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.

Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel, Microsoft Office and tagged , , . Bookmark the permalink.

5 Responses to VBA Auto Data Tips

  1. Bob Phillips says:

    Sure you haven’t got those variable types the wrong way aound?

    Like

  2. Pingback: A Guided Tour Through The VBA IDE’s Options | RAD Excel

  3. Duncan says:

    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?

    Like

  4. Colin Legg says:

    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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s