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
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
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.
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
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:
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
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
That’s all for now. I hope this saves you a few headaches and, until next time, happy coding.