A really, really strange Excel problem

[MartinLiss]

Create this sub, put a breakpoint on the End Sub line and execute it

Sub test()
Dim strCol As String
strCol = "AJ"
'MsgBox strCol
End Sub

In my Excel if I check at the breakpoint what strCol is, it shows up as blank! However if I uncomment the MsgBox line the Msgbox shows AJ.

Note that if I change strCol to "XX" or any other value (as far as I know), strCol becomes XX with or without the MsgBox.

[OptionBase1]

I don't have convenient access to anything newer than Office 2003 at the moment, but I'm unable to reproduce the issue in Excel 2003. Breakpoint set at End Sub, value of strCol when ran = "AJ" when code paused at the breakpoint.

[MartinLiss]

Someone else has the same problem.

https://stackoverflow.com/questions/...-to-a-variable

[OptionBase1]

Amazing. I can't imagine how some strangely specific bug like that makes its way into production code.

[OptionBase1]

Out of curiosity, I tried it in Excel 97 SR2-B and Excel XP SP3 and the issue doesn't happen on them either.

[MartinLiss]

I'm using Office 16.

[jdc2000]

Works OK on my Office 2016.

[Zvoni]

Is the same issue on O365.
Tooltip doesn't show Value.
Watch-Window works, though.

Begs the Question, what's so special about "AJ" to confuse the Tooltip

[Zvoni]

Bugger it!
Just ran afoul of this in my own code.
I have a lookup-worksheet "Config" where i keep column-describers ("SalesPrice is in Column AJ") Column A="SalesPrice", ColumnB="AJ"
where at runtime my code fishes the column-letters from that sheet.

And i got permanently Error 1004 until i remembered this thread.

Blast it!

[jdc2000]

I have not had this error occur on my systems.

However, I wonder what would happen if you changed the code to:

Sub test()
Dim strCol As String
strCol = "A" & "J"
'MsgBox strCol
End Sub

[Zvoni]

Jdc, the bug is with the tooltip, not the value and the variable itself.
i thought i got that error because my lookup function was faulty. The error was produced somewhere else, but since i didn’t see the value in the tooltip i thought it was empty, and i was chasing the wrong cat-tail

[jdc2000]

Zvoni,

Thanks. Now I understand what is happening. I retested it on my computers, and it does not matter what you set strCol to, "AJ" or "XX", it shows as "" in the ToolTip if you put the break point on that line. If you put the break point on the End Sub, it works OK. You cannot put a break point on the commented out MsgBox, but you can if it is not commented. I usually avoid this issue by inserting Stop statements into the code where I want to check something.

[Zvoni]

Zvoni,

Thanks. Now I understand what is happening. I retested it on my computers, and it does not matter what you set strCol to, "AJ" or "XX", it shows as "" in the ToolTip if you put the break point on that line. If you put the break point on the End Sub, it works OK. You cannot put a break point on the commented out MsgBox, but you can if it is not commented. I usually avoid this issue by inserting Stop statements into the code where I want to check something.

Funny enough, in VBA i use a lot of „old school“ variable checking issuing Debug.Print‘s
i take it out of production code with a global const.
something along the lines

Public Const DO_DEBUG As Boolean = True


‘Somewhere else
If DO_DEBUG Then Debug.Print SomeVariable

When the code goes live i just set the constant to False