VBA Excel - How to know if a specific cell is affected by any macro?

Question

I have an excel file with hundreds of macros, where which macro has extensive code.

I need to change the layout of the cells and that includes moving some cells to another location.

My only problem is that after doing that i need to change the macros that interacts with that cell. Interacts as his value/formula was changed or his value is used in another operation

I can't search in the code for range("C2") for example because the cell can be affected in diferent forms like:

Range("C2")
Cells(2, 3)
Range("B1:E5")
Range(Cells(1, 1), Cells(10, 10))
Offset

Is there any whey that i can discover which macros are changing a specific cell?

Well, I guess you will need to review your macro codes if they are affected by moving some cells to other locations. — Pᴇʜ
You can lock Your worksheet "Protect worksheet" function. Then if You have the VBA window open Macro which tries to modify this cell should throw an exception. Cons are that each time Your VBA code will hang on the same macro, so e. g. You must comment it or if it is possible run each macro separately. Furthermore, I assume that those macros will try to write something to cell. — Mikisz
@Paul .Dirty Designates a range to be recalculated when the next recalculation occurs. How does this tell you which cells are affected by a macro? — Pᴇʜ
Don't forget Offset, Resize and Intersect among others that are not immediately apparent. — user4039065
@Pᴇʜ - You're absolutely right. Never assume makes an ass ... mainly of me in this case! You'd usually expect Dirty to mean that the cell has changed value :o) — Paul

Mathieu Guindon Mathieu Guindon · Accepted Answer · 2018-07-26T13:51:51

Are you looking for a way to track a specific cell, or for a way to track all cells affected by VBA code? Also, "affected" as in "value was changed" or "formula was changed", or "cell was merged with another", "range was unmerged", or "borders were changed", or "backcolor was changed"? Is adding conditional formatting "affecting" a cell? Data validation?

And then, there are more ways a cell can be "affected", too. Without tracking the code as it's running, it could be hard to tell whether this myRange variable is affecting the cell you're looking for.. especially if methods like Range.Offset and Range.Resize are used.

Then ranges can be named, so Range("Foo") might be referring to a cell you're interested in, but you can't know that without verifying whether Names("Foo").RefersToRange includes that cell.

Short of carefully reviewing the code, I'm afraid you can't.

If no macro is highlighting any cell in bright yellow, you could always make a copy of the file and then handle Workbook_SheetChange in ThisWorkbook:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Target.Interior.Color = vbYellow
End Sub

You can easily add conditional logic here to only recolor modified cells from a specific sheet, or from a specific column or row.

Now remove or comment-out any Application.EnableEvents toggling everywhere in the code, and run your macros - the cells it affected (assuming a change in value is what we're after) should all be bright yellow.

If you need to know what code caused this, you can place a breakpoint there, and inspect the call stack:

call stack showing what code caused a cell change

VBA Excel - How to know if a specific cell is affected by any macro?

1 Answers