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
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.