Excel vba public variable not storing in do loop after update

Hello @Domenic Moscato ,

Thanks for your question.

Imagine Mrow as a sticky note holding the number 9. The code uses it to find drawer 9 on your screen and read the SecID label. However, due to a recent Excel update, the code accidentally erases this sticky note right after reading it, which breaks the rest of the process.

I recommend passing the code a "photocopy" of the sticky note instead. This keeps the original Mrow value safe. You can refer to these two options:

1. Putting extra parentheses around the word Mrow forces Excel to make a quick photocopy. Change your SecID line to exactly this:

SecID = Trim(scr.Area((Mrow), 4, (Mrow), 12))

2. You can create an actual temporary copy just before the step that fails. Update your code to look exactly like this:

Mrow = Mrow
scr.moveTo Mrow, 2, 1
Dim TempRow As Integer
TempRow = Mrow
SecID = Trim(scr.Area(TempRow, 4, TempRow, 12))
If SecID = "" Then

Please try these options and let me know the results. I would be happy to investigate further if needed.

I hope this addresses your question. If this response was helpful, please consider following the guidance to provide feedback.

SecID = Trim(scr.Area(Mrow, 4, Mrow, 12))

becomes Empty after SendKeysWaitX(scr,"<Enter>") executes and returns to the loop.

A stronger answer for the post would be:

Answer:

This probably is not a VBA Public variable problem or a recent Office update changing variable persistence. From the screenshots, Mrow remains 9, but the expression:

SecID = Trim(scr.Area(Mrow, 4, Mrow, 12))

starts returning Empty.

The more likely issue is that:

SendKeysWaitX(scr,"<Enter>") changes the active screen/state

scr gets refreshed/recreated

scr.Area(...) no longer points to the same object/data after returning

Try storing the value before the screen navigation:

Dim TempSecID As String

TempSecID = CStr(Trim(scr.Area(Mrow, 4, Mrow, 12)))
SecID = TempSecID

Call SendKeysWaitX(scr, "<Enter>")

Also add debug output:

Debug.Print "Mrow = " & Mrow
Debug.Print "SecID before Enter = " & SecID

Call SendKeysWaitX(scr,"<Enter>")

Debug.Print "SecID after Enter = " & SecID

If SecID disappears only after SendKeysWaitX, then the problem is likely object state refresh or screen focus changes rather than a Microsoft update changing Public variables.

I would investigate scr and SendKeysWaitX() behavior first.