CODEWITHSUNDEEP
excelvba
notIntoXS
notIntoXS

Reputation: 129

How to determine what VBA code is changing the value of an Excel cell

I have a workbook written by someone else and a particular cell is being changed by some code. I cannot figure out the code that is changing the cell value.

I cannot even figure out a strategy to narrow down the code possibilities apart from setting a breakpoint in hundreds of procedures. So I would be happy with strategy ideas.

I am very experienced using VBA in Microsoft Access and a little bit experienced using VBA in Excel.

EDIT Gotta larf. I cannot even F8 throught the code by starting at the start. What I mean is.. The cell in question changes after I change a value (a year actually) in the worksheet. So I set up a subroutine and breakpoint as below.

Private Sub Worksheet_Change(ByVal Target As Range)
End Sub   '<== set breakpoint here

Then I press F8 and no other code is entered by the debugger and yet the cell changes.

The problem may be because there is an awful lot of dynamic formula creation going on in the code. Not sure. Don't know enough about Excel quirks.

EDIT 2 There is a missing formula in the cell in question. Sorry to have wasted everyone's time.

So how can a missing formula change a cell's value. It can't! The question should have been "How to determine why a cell is NOT changing." So why didn't I ask that?

I did not notice that other cells in the same column as this cell contain a formula. Because the other cells were changing values correctly and this cell was not, I presumed it was some VBA code not working so I tried to track down the rogue code. I guess not being an experienced Excel person I did not rule out the bleeding obvious and went straight to VBA. Phew!

Upvotes: 0

Views: 655

Answers (1)

chris neilsen
chris neilsen

Reputation: 53136

Add a change event handler on the sheet containing the cell you want to track.

In that Event Handler, add code to break when a change in the tracked cell takes place

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Cells(1, 1)) Is Nothing Then
        Stop
    End If

End Sub

Chnage Me.Cells(1, 1) to cell of your choosing.

When the code breaks, open the Call Stack, to see where the change came from

Here's a proof of concept. I ran ZX. Call stack shows the Change event at the top. The next function is what changed to cell.

enter image description here

Upvotes: 1

Related Questions