Как получить адрес диапазона, включая имя рабочего листа, но не имя книги, в Excel VBA?

Если у меня есть объект Range, например, допустим, что он ссылается на ячейку A1 на листе, который называется Book1. Поэтому я знаю, что вызов Address() даст мне простую локальную ссылку: $A$1. Я знаю, что его также можно назвать Address(External:=True), чтобы получить ссылку, включая имя и название рабочей книги: [Book1]Sheet1!$A$1.

Я хочу получить адрес, включая имя листа, но не имя книги. Я действительно не хочу называть Address(External:=True) и пытаться лишить имя книги самостоятельно со строковыми функциями. Есть ли какой-либо вызов, который я могу сделать на диапазоне, чтобы получить Sheet1!$A$1?

Ответы

Ответ 1

Единственный способ, о котором я могу думать, - связать имя листа с ссылкой на ячейку следующим образом:

Dim cell As Range
Dim cellAddress As String
Set cell = ThisWorkbook.Worksheets(1).Cells(1, 1)
cellAddress = cell.Parent.Name & "!" & cell.Address(External:=False)

EDIT:

Изменить последнюю строку:

cellAddress = "'" & cell.Parent.Name & "'!" & cell.Address(External:=False)

если вы хотите, чтобы он работал, даже если в имени листа есть пробелы или другие забавные символы.

Ответ 2

Split(cell.address(External:=True), "]")(1)

Ответ 3

Бен прав. Я также не могу придумать, как это сделать. Я предлагаю либо метод Ben рекомендует, либо следующее, чтобы удалить имя рабочей книги.

Dim cell As Range
Dim address As String
Set cell = Worksheets(1).Cells.Range("A1")
address = cell.address(External:=True)
address = Right(address, Len(address) - InStr(1, address, "]"))

Ответ 4

Функция рабочего листа Address() выполняет именно это. Поскольку он не доступен через Application.WorksheetFunction, я придумал решение, используя метод Evaluate().

Это решение позволяет Excel обрабатывать пробелы и другие забавные символы в имени листа, что является хорошим преимуществом по сравнению с предыдущими ответами.

Пример:

Evaluate("ADDRESS(" & rng.Row & "," & rng.Column & ",1,1,""" & _
    rng.Worksheet.Name & """)")

возвращает точно "Sheet1! $A $1" с объектом Range с именем rng, ссылающимся на ячейку A1 в листе Sheet1.

Это решение возвращает только адрес первой ячейки диапазона, а не адрес всего диапазона ( "Sheet1! $A $1" против "Sheet1! $A $1: $B $2" ). Поэтому я использую его в пользовательской функции:

Public Function AddressEx(rng As Range) As String

    Dim strTmp As String

    strTmp = Evaluate("ADDRESS(" & rng.Row & "," & _
        rng.Column & ",1,1,""" & rng.Worksheet.Name & """)")

    If (rng.Count > 1) Then

        strTmp = strTmp & ":" & rng.Cells(rng.Count) _
            .Address(RowAbsolute:=True, ColumnAbsolute:=True)

    End If

    AddressEx = strTmp

End Function

Полная документация по функции рабочего листа Address() доступна на веб-сайте Office: https://support.office.com/en-us/article/ADDRESS-function-D0C26C0D-3991-446B-8DE4-AB46431D4F89

Ответ 5

Я обнаружил, что для меня была создана работа в пользовательской функции, которую я создал. Я связал ссылку диапазона ячеек и имя листа как строку, а затем использовал ее в выражении Evaluate (я использовал Evaluate on Sumproduct).

Например:

Function SumRange(RangeName as range)   

Dim strCellRef, strSheetName, strRngName As String

strCellRef = RangeName.Address                 
strSheetName = RangeName.Worksheet.Name & "!" 
strRngName = strSheetName & strCellRef

Затем обратитесь к strRngName в остальной части вашего кода.

Ответ 6

Вам может потребоваться написать код, который обрабатывает диапазон с несколькими областями, что это делает:

Public Function GetAddressWithSheetname(Range As Range, Optional blnBuildAddressForNamedRangeValue As Boolean = False) As String

    Const Seperator As String = ","

    Dim WorksheetName As String
    Dim TheAddress As String
    Dim Areas As Areas
    Dim Area As Range

    WorksheetName = "'" & Range.Worksheet.Name & "'"

    For Each Area In Range.Areas
'           ='Sheet 1'!$H$8:$H$15,'Sheet 1'!$C$12:$J$12
        TheAddress = TheAddress & WorksheetName & "!" & Area.Address(External:=False) & Seperator

    Next Area

    GetAddressWithSheetname = Left(TheAddress, Len(TheAddress) - Len(Seperator))

    If blnBuildAddressForNamedRangeValue Then
        GetAddressWithSheetname = "=" & GetAddressWithSheetname
    End If

End Function

Ответ 7

rngYourRange.Address(,,,TRUE)

показывает внешний адрес, полный адрес

Ответ 8

Почему бы просто не вернуть имя рабочего листа address = cell.Worksheet.Name то вы можете конкатенировать адрес назад, как это address = cell.Worksheet.Name и "!" и cell.Address

Ответ 9

Dim rg As Range
Set rg = Range("A1:E10")
Dim i As Integer
For i = 1 To rg.Rows.Count

    For j = 1 To rg.Columns.Count
    rg.Cells(i, j).Value = rg.Cells(i, j).Address(False, False)

    Next
Next

Ответ 10

Для путаных старых мне диапазон

.Address(False, False, True)

кажется, дает в формате TheSheet! B4: K9

Если это не так, почему критерии.. избегайте str functons

вероятно, займет меньше миллисекунды и использует 153 уже использованных электрона

около 0,3 микросекунда

RaAdd = mid (RaAdd, instr (raadd, "]" ) +1)

или

'около 1,7 микрос

RaAdd = split (radd, "]" ) (1)

Ответ 11

[edit on 2009-04-21]

Как указывал Мика, это работает только тогда, когда вы назвали, что конкретный диапазон (следовательно. Кто-нибудь?) Да, oops!

[/править]

Немного поздно вечеринке, я знаю, но если кто-то поймает это в поиске Google (как я и сделал), вы также можете попробовать следующее:

Dim cell as Range
Dim address as String
Set cell = Sheet1.Range("A1")
address = cell.Name

Это должно вернуть полный адрес, что-то вроде "= Sheet1! $A $1".

Предполагая, что вам не нужен знак равенства, вы можете отключить его с помощью функции Replace:

address = Replace(address, "=", "")