VBA Auto Data Tips

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.