vba 在excel vba中查找单元格引用的简单方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/978611/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Easy way to find cell reference in excel vba
提问by a_m0d
I am working on a spreadsheet with lots of VBA code in it. Most cell references use numbers, e.g.
我正在处理一个包含大量 VBA 代码的电子表格。大多数单元格引用使用数字，例如
Worksheets("Sheet1").Cells(someRow, someColumn)
I am wondering if there is any easy way to find out exactly which cell is being referenced - is it possible to put a breakpoint on this line of code, and then see which cell it will refer to (using the traditional "BA10" style) or will I have to calculate it each and every time using divsand mods?
我想知道是否有任何简单的方法可以准确找出被引用的单元格 - 是否可以在这行代码上放置一个断点，然后查看它将引用哪个单元格（使用传统的“BA10”样式）还是我每次都必须使用div和mod来计算它？
Not all the references are hard coded, and I would really like to be able to work out where data is being pulled from, as the cell is accessed (not necessarily changed).
并非所有引用都是硬编码的，我真的希望能够在访问单元格（不一定更改）时确定数据从何处提取。
(edit) Is it possible to do this without changing the original source line, e.g. in an event module or something?
（编辑）是否可以在不更改原始源代码行的情况下执行此操作，例如在事件模块或其他内容中？
回答by mechanical_meat
Debug.Print Worksheets(1).Cells(10, 53).Address(False, False)
returns BA10
返回 BA10
回答by JustPlainBill
You can use Address.
您可以使用地址。
Debug.print Worksheets("Sheet1").Cells(10, 53).Address
Debug.print Worksheets("Sheet1").Cells(10, 53).Address
Will print the ranges address for you in the Immediate Window.
将在立即窗口中为您打印范围地址。
Is that what you are looking for?
这就是你要找的吗？
回答by Joel Goodwin
There is another option. If you are making changes to a sheet, you can catch the Change event on the Worksheet, and pump out the changed range like so:
还有另一种选择。如果您正在对工作表进行更改，您可以在工作表上捕获 Change 事件，并像这样抽出更改的范围：
Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print "CHANGED -> " & Target.Address(False, False)
End Sub
Each change to the sheet will be output to your Immediate window like thus:
对工作表的每次更改都将输出到您的立即窗口，如下所示：
CHANGED -> G10
CHANGED -> G11:G28
There is also the SelectionChange event as well, but that's unlikely to be too useful. There is no event for just "reading" cells.
还有 SelectionChange 事件，但这不太可能太有用。没有仅“读取”单元格的事件。
回答by Rimskys
Use the following to know which cell is calling the function:
使用以下内容来了解哪个单元格正在调用该函数：
Application.Caller.Address
Example:
例子：
Function Addition(va As Double, vb As Double) As Double
Addition = va + vb
MsgBox "Cell calling function Addition is " & Application.Caller.Address
End Function
Whenever this function is calculated a message box indicates the cell address calling it. Be aware that calling this function from another VBA function will send an error. Check http://www.cpearson.com/Excel/WritingFunctionsInVBA.aspx
每当计算此函数时，消息框都会指示调用它的单元格地址。请注意，从另一个 VBA 函数调用此函数将发送错误。检查http://www.cpearson.com/Excel/WritingFunctionsInVBA.aspx
回答by Rimskys
You can also change your reference style to R1C1 in: tools\options\general\R1C1 reference style this way you will be able to know what the code is refereeing to.
您还可以在以下位置将您的参考样式更改为 R1C1：tools\options\general\R1C1 参考样式，这样您就可以知道代码正在引用什么。
回答by Oorang
Another way to go would be to add a watch in the VBE (Visual Basic Editor). You can do this by going to View>Watch Window to make sure the watch window is visible. Then from Debug menu click Add Watch. If you do this in break mode the context will already be set for you so all you have to do is paste in the expression you want watched and it will be visible. This of course works best on range objects (ex: rngFoo.Address as the expression) but you can paste in things like Worksheets(1).Cells(10, 53).Address.
另一种方法是在 VBE（Visual Basic 编辑器）中添加一个监视。您可以通过转到“查看”>“监视窗口”来确保监视窗口可见。然后从调试菜单中单击添加监视。如果您在中断模式下执行此操作，则上下文已经为您设置好，因此您所要做的就是粘贴您想要观看的表达式，它就会可见。这当然最适用于范围对象（例如：rngFoo.Address 作为表达式），但您可以粘贴 Worksheets(1).Cells(10, 53).Address 之类的内容。
