Easy way to find cell reference in excel vba

Question

Hi, 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?

Answer 1

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

returns BA10

Answer 2

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?

Answer 3

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.

Answer 4

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.

Answer 5

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.