如何在Excel VBA中获得包含工作表名称而不是工作簿名称的范围地址? [英] How do I get a range's address including the worksheet name, but not the workbook name, in Excel VBA?

问题描述

如果我有一个Range对象，例如，假设它引用了名为Book1的工作表上的单元格A1.因此，我知道调用Address()将为我提供一个简单的本地引用:$A$1.我知道也可以将其称为Address(External:=True)以获得包含工作簿名称和工作表名称的参考:[Book1]Sheet1!$A$1.

If I have a Range object--for example, let's say it refers to cell A1 on 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.

我想要的是获取一个包含工作表名称而不是书名的地址.我真的不想调用Address(External:=True)并尝试使用字符串函数自己删除工作簿名称.我可以在范围内拨打电话以获得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?

推荐答案

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

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)

将最后一行修改为:

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.

这篇关于如何在Excel VBA中获得包含工作表名称而不是工作簿名称的范围地址?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！