A Common Mistake When Declaring Variables In VBA

For today’s post I’d like you to imagine that you’re an up and coming VBA programmer who is starting to feel at home with the IDE and language. You’ve read about some VBA best practices which has made you aware of the benefits of Option Explicit, so you’ve started to dutifully declare your variables and you try to carefully choose what types they should be. Good on you, but be warned: you’re a prime candidate for falling foul of one of VBA’s syntactical nuances!

Let’s have a look at some code.

Option Explicit

Sub HowNotToDeclareYourVariables()

    Dim rngStartDate, rngEndDate As Range

    'do some stuff with rngStartDate and rngEndDate

End Sub

So here I’ve declared two variables called rngStartDate and rngEndDate. The variable names are meaningful and I’ve prefixed them with rng which is the prefix I always use to indicate that they’re a Range type.

Unfortunately I’ve made a mistake. Let’s step into the code by pressing F8 and have a look in the Locals Window to see what type they are.

VariableMistake1

rngEndDate is a Range type as you’d expect, but have a closer look at rngStartDate. It’s a Variant.  In VBA, if you declare multiple variables using a single Dim (or similar, eg. Private) keyword, then you should explicitly state the type of each variable. If you don’t then they’ll all be Variant types apart from the last one which’ll be whatever type you’ve given it.

Let’s write the code so both variables are explicitly declared as Range types.

Sub HowNotToDeclareYourVariables()

    Dim rngStartDate As Range, rngEndDate As Range

    'do some stuff with rngStartDate and rngEndDate

End Sub

And now check it in the Locals window:

VariableMistake2

Of course, a perfectly fine and safe option would be to declare the variables on separate lines:

Sub HowToDeclareYourVariables()

    Dim rngStartDate As Range
    Dim rngEndDate As Range

    'do some stuff with rngStartDate and rngEndDate

End Sub

VariableMistake3

So there you have it, but that’s not quite the end of the story. What makes things slightly more confusing is that, whilst this rule also holds up in VB6, things were changed in VB.Net. You need to be aware of this for when you’re reading VB.Net code snippets online. In VB.Net, the following code is quite correct in its intention to declare both variables as Range types:

VariableMistake4

That’s all for now. I hope this saves you a few headaches and, until next time, happy coding.

Advertisements

About Colin Legg

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

10 Responses to A Common Mistake When Declaring Variables In VBA

  1. Vasim says:

    Oh my God…was this especially for me…..the description exactly suits me…

    I ‘ve now started using Option explicit and all varaible declaring in wrong farmat (variant, integer)….will change

    Thanks…you saved me…

    Like

  2. Bob Watson says:

    I see this so often. It seems that the syntax of declaring variables in a single statement should work – and actually it does in some languages – but not in VBA. Worse still, Excel provides no warning that the variables have different types. This can lead to performance issues and perhaps even incorrect results (because the variable type is not what you expect it to be).

    Thanks for the post.

    Cheers,

    Bob
    http://www.i-nth.com

    Like

    • Patrick says:

      Bob, it *does* work in VBA; the trick is that you must declare a type for each variable or the type defaults to Variant.

      For exactly this reason, I try to be in the habit of declaring just one variable per statement.

      Of course, in VBScript I often use just a single Dim statement because in VBScript all variables are Variant :)

      Like

  3. Nice one Col. I think this is a common mistake many many budding VBA programmers fall prey to, I know I did.

    Like

  4. Pingback: VBA: What Type Are Your Constants? | RAD Excel

  5. zbarresse says:

    Reblogged this on Microsoft Excel and Access Experts Blog and commented:
    This has been asked many times, and Colin did an awesome job of covering it. If you use VBA this is a must read and will help you quite a bit. Knowledge is power my friends. :)

    Like

  6. Brad S. says:

    I’ve always used the separate line approach to declare my variables, but now I know to be aware of the VB.NET format. Thanks!

    Like

  7. Ejaz Ahmed says:

    Thanks for letting “us” know! Now I have to revisit all my code!

    Like

  8. Ejaz Ahmed says:

    Reblogged this on Struggling To Excel and commented:
    Potential Pitfalls while declaring multiple variables in one line.

    Like

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

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