如何在 Excel VBA 中获取范围地址，包括工作表名称，但不包括工作簿名称？

How do I get a range's address including the worksheet name, but not the workbook name, in Excel VBA?

提问by Micah

If I have a Range object--for example, let's say it refers to cell A1on a worksheet called Book1. So I know that calling Address()will get me a simple local reference: $A$1. I know it can also be called as Address(External:=True)to get a reference including the workbook name and worksheet name: [Book1]Sheet1!$A$1.

如果我有一个 Range 对象——例如，假设它指的是A1名为Book1. 所以我知道调用Address()会给我一个简单的本地引用：$A$1. 我知道也可以调用它Address(External:=True)来获取参考，包括工作簿名称和工作表名称：[Book1]Sheet1!$A$1。

What I want is to get an address including the sheet name, but not the book name. I really don't want to call Address(External:=True)and try to strip out the workbook name myself with string functions. Is there any call I can make on the range to get Sheet1!$A$1?

我想要的是获得一个地址，包括工作表名称，但不包括书名。我真的不想调用Address(External:=True)并尝试使用字符串函数自己去除工作簿名称。有什么我可以在范围内拨打的电话Sheet1!$A$1吗？

回答by Ben Hoffstein

Only way I can think of is to concatenate the worksheet name with the cell reference, as follows:

我能想到的唯一方法是将工作表名称与单元格引用连接，如下所示：

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:

编辑：

Modify last line to :

将最后一行修改为：

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

if you want it to work even if there are spaces or other funny characters in the sheet name.

如果您希望它工作，即使工作表名称中有空格或其他有趣的字符。

回答by Luciano Evaristo Guerche

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

回答by theo

Ben is right. I also can't think of any way to do this. I'd suggest either the method Ben recommends, or the following to strip the Workbook name off.

本是对的。我也想不出任何方法来做到这一点。我建议使用 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, "]"))

回答by raph82

The Address()worksheet function does exactly that. As it's not available through Application.WorksheetFunction, I came up with a solution using the Evaluate()method.

该Address()工作表函数正是这么做的。由于它无法通过Application.WorksheetFunction，我想出了一个使用该Evaluate()方法的解决方案。

This solution let Excel deals with spaces and other funny characters in the sheet name, which is a nice advantage over the previous answers.

这个解决方案让 Excel 处理工作表名称中的空格和其他有趣的字符，这比以前的答案有很大的优势。

Example:

例子：

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

returns exactly "Sheet1!$A$1", with a Rangeobject named rngreferring the A1 cell in the Sheet1 worksheet.

准确返回“Sheet1！$A$1”，其中一个Range对象命名为rng引用 Sheet1 工作表中的 A1 单元格。

This solution returns only the address of the first cell of a range, not the address of the whole range ("Sheet1!$A$1" vs "Sheet1!$A$1:$B$2"). So I use it in a custom function:

此解决方案仅返回范围的第一个单元格的地址，而不是整个范围的地址（“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

The full documentation of the Address() worksheet function is available on the Office website: https://support.office.com/en-us/article/ADDRESS-function-D0C26C0D-3991-446B-8DE4-AB46431D4F89

可在 Office 网站上获取 Address() 工作表函数的完整文档：https: //support.office.com/en-us/article/ADDRESS-function-D0C26C0D-3991-446B-8DE4-AB46431D4F89

回答by Jeff

I found the following worked for me in a user defined function I created. I concatenated the cell range reference and worksheet name as a string and then used in an Evaluate statement (I was using Evaluate on Sumproduct).

我发现以下内容在我创建的用户定义函数中对我有用。我将单元格范围引用和工作表名称连接为一个字符串，然后在 Evaluate 语句中使用（我在 Sumproduct 上使用 Evaluate）。

For example:

例如：

Function SumRange(RangeName as range)   

Dim strCellRef, strSheetName, strRngName As String

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

Then refer to strRngName in the rest of your code.

然后在其余代码中引用 strRngName。

回答by HarveyFrench

You may need to write code that handles a range with multiple areas, which this does:

您可能需要编写处理具有多个区域的范围的代码，这样做的目的是：

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:$H,'Sheet 1'!$C:$J
        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

回答by ArnonK

rngYourRange.Address(,,,TRUE)

Shows External Address, Full Address

显示外部地址、完整地址

回答by Harry S

For confused old me a range

对于困惑的老我一个范围

.Address(False, False, , True)

.Address(False, False, , True)

seems to give in format TheSheet!B4:K9

似乎给出格式 TheSheet!B4:K9

If it does not why the criteria .. avoid Str functons

如果它不是为什么标准..避免 Str 函数

will probably only take less a millisecond and use 153 already used electrons

可能只需要不到一毫秒的时间并使用 153 个已经使用过的电子

about 0.3 Microsec

约 0.3 微秒

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

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

or

或者

'about 1.7 microsec

'大约 1.7 微秒

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

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

回答by Harry S

Why not just return the worksheet name with address = cell.Worksheet.Namethen you can concatenate the address back on like this address = cell.Worksheet.Name & "!" & cell.Address

为什么不使用address = cell.Worksheet.Name返回工作表名称， 然后您可以像这样address = cell.Worksheet.Name & "!"将地址连接回去 &单元格地址

回答by rinku

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