564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Variables and Constants in Excel Visual Basic
Part five of an eight-part series of blogs
This blog explains the nuts and bolts of Excel VBA variables, including how to declare them, how to set their scope and all sorts of other tips and tricks. The blog also covers constants in Excel macros.
The example on the previous page would run just as well if you didn't declare your variables:
If you comment out the variable declarations like this, your macro will still work.
In the above case, VBA will reach the first two lines creating variables:
HeroName = Range("C4")
HeroRating = Range("C6")
Because you haven't explicitly declared HeroName and HeroRating, VBA will create them on the fly for you, giving each the type Variant. So far, so good?
Well, yes - until you mistype a variable name. Here's what would happen for a simple mistyping:
|There's a misprint here - how quickly can you see it?||The superhero you get as a result|
The problem above could be avoided so easily, if you could just tell VBA that wherever you use a variable, you have to declare it. Read on!
The solution to the above problem is to include two magic words at the top of each module - Option Explicit:
'the name of each superhero and their rating
'Dim HeroName As String
'the rating assigned to them
'Dim HeroRating As Long
'go to the votes sheet and get the value of the superhero, and their rating
HeroNane = Range("C4").Value
HeroRating = Range("C6").Value
Here's what you get if you run the above macro now:
The result of running the macro. VBA highlights the variable you've misspelt, and tells you that it's not defined.
Given that forcing yourself to declare all variables like this is such a good idea, how can you avoid having to type in Option Explicit at the top of every new module you create? Here's how!
Tick the box shown!
From now on, every time you create a new module it will have the words Option Explicit at the top, and you'll have to declare any variables that you use.
|Parts of this blog|
25 Aytoun Street