Auto data tips are a very handy feature when you’re debugging VBA code. You may have noticed that when you’re stepping through code, if you hover your mouse over, for example, a property, then you get a pop-up window showing its value.
If you’re not getting auto data tips then check under Tools > Options > Editor tab and make sure that the ‘Auto Data Tips’ checkbox is ticked.
Now, the fact that auto data tips can do this for properties raises an interesting question which I never even considered for many years: how? Well, the only possible answer is that hovering your mouse over the code actually causes the property to be called. Let’s prove it.
Let’s start a new workbook and add a class module (Insert > Class Module) called Class1
. Inside that class put the following code:
Public Property Get Counter() As Long Static i As Long i = i + 1 Counter = i End Property
Now it’s time for my disclaimer: this is a poorly designed property – I’m only writing it like this to prove the concept. Okay, now add a standard code module (Insert > Module) called Module1
to the project and paste in the following code:
Sub Test() Dim cls As Class1 Set cls = New Class1 Debug.Print cls.Counter End Sub
Step into the code by pressing F8 and press F8 twice again so that the Debug.Print cls.Counter
line is highlighted in yellow. At this point in the code we have created a Class1
object so we can hover our mouse over cls.Counter
to see what happens. If you repeatedly move your mouse over it you will see that the value increments by 1 each time. When you press F8 again you will see in the Immediate Window (CTRL+G) that the value is incremented once more.
If we revisit our custom class we can add a method which calculates in the same way but which will not be called by the auto data tip. For example:
Public Function GetCounter() As Long Static i As Long i = i + 1 GetCounter = i End Function
Then, try hovering your mouse over cls.GetCounter
in this code:
Sub Test2() Dim cls As Class1 Set cls = New Class1 Debug.Print cls.GetCounter; cls.GetCounter; cls.GetCounter End Sub
It doesn’t increment on mouse hover and no auto data tip is provided.
Incidentally, not many people know that you can use the Static
keyword in Sub
, Function
and Property
signatures and it will mean that all local variables will be Static
. So this method will behave in exactly the same way as the previous one:
Public Static Function GetCounter2() As Long Dim i As Long i = i + 1 GetCounter2 = i End Function
I’m not a fan of doing it this way: I think it’s clearer to use the Static
keyword with the variable declarations.
Sure you haven’t got those variable types the wrong way aound?
LikeLike
Hi Bob, good to hear from you. It all looks okay to me – which variable types do you think are the wrong way round?
LikeLike
Pingback: A Guided Tour Through The VBA IDE’s Options | RAD Excel
Bit late to the party.
For the first sets of code here, when I repeatedly hover over cls.Counter it stays continually at 1, it does not increment – I copy pasted your code exactly, and I am using Excel 2013?
Any idea why I do not see the behaviour you document?
LikeLike
Hi Duncan,
It’s never too late to party!
You’re right, there was an error in my post. The i variable should have been declared using the Static keyword rather than the Dim keyword. I’ve edited the post now to reflect this.
Thanks for bringing it to my attention,
Colin
LikeLike