CODEWITHSUNDEEP
vbaexcel
a_m0d
a_m0d

Reputation: 12195

Easy way to find cell reference in excel vba

I am working on a spreadsheet with lots of VBA code in it. Most cell references use numbers, e.g.

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 divs and mods?

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?

Upvotes: 2

Views: 41635

Answers (6)

Rimskys
Rimskys

Reputation: 11

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

Upvotes: 1

Joel Goodwin
Joel Goodwin

Reputation: 5086

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:

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.

Upvotes: 2

Oorang
Oorang

Reputation: 6770

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.

Upvotes: 1

user121014
user121014

Reputation:

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.

Upvotes: 1

JustPlainBill
JustPlainBill

Reputation: 276

You can use 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?

Upvotes: 2

mechanical_meat
mechanical_meat

Reputation: 169264

Debug.Print Worksheets(1).Cells(10, 53).Address(False, False)

returns BA10

Upvotes: 5

Related Questions