So What are Variables?
In VBA we use variables as a place to temporarily store some data/information we want to use later. Think of a variable as a carrier bag – you can temporarily put things into a carrier bag, and remove them later (when you want to use them). Each time you use the carrier bag, its contents can change, but the carrier bag remains the medium for holding something. Now in VBA, we don’t physically store anything; instead variables are used solely to hold data in computer memory.
Formal definition: A uniquely named data item where a value or values can be stored and modified during program execution.
A variable can store data of many different types, this can include integers, decimals, booleans algebraic values, dates etc (we’ll get into more of these over the course of this lesson). One important point to note, each variable can only contain data of one type (at any one time). Before a variable is used, it’s name has to be defined and it’s type has to be declared (chosen). Any subsequent attempt to assign data of the incorrect type to a variable results in an error, and the sound of the ever irritating windows error-ding!
In the real world we can cut corners. We don’t actually even have to declare variables before we use them in code. Who wants to spend all that time picking data types right? Well… the short answer is, you do! The use of undeclared variables is actively discouraged as you can very easily introduce code errors harder to catch than a ninja at night, while blindfolded.
But what if I forget to assign a variable type? Luckily, VBA includes as statement that forces all variables to be declared. The command Option Explicit should be entered in the first line in every module.
'Remember these lines are called 'comments'!
Now I know what you’re thinking… what if I forget to put the Option Explicit command at the start of the module? Fortunately, Bill Gates… I mean Microsoft has you covered! Open the Visual Basic Editor and hit Tools > Options > Editor Tab > Require Variable Declaration.
This will auto-insert the Option Explicit command at the start of every new module you create. Hence, you will be forced to declare all variables you wish to use. This is very good coding practice, so if you complete the step above, well done! You deserve a cookie.
Common Variable Types
As mentioned previously, there are many data types we can use in VBA. The once you’ll most likely to see are detailed in the table below:
|Boolean||False||True or False|
|Integer||0||-32,768 to 32,767|
|Long||0||-2,147,483,648 to 2,147,483,648|
|Single||0.0||-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values|
|Double||0.0||-1.79769313486231E308 to-4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values|
|Date||00/00/0000||January 1, 100 to December 31, 9999|
|String||Empty String||0 to approximately 2 billion|
|Variant||Empty||Any numeric value up to the range of a Double|
If you want some more information on Data Types, please visit the Microsoft MSDN:
How do I Name Variables?
There are some rules and regulations you need to be aware of. When creating a variable name, it:
- Must not begin with a number.
- Must not contain any spaces.
- Must not exceed 40 characters in length.
- May contain only letters, numbers of the underscore symbol ( _ ).
- Must not share a name with another variable or parent procedure.
Putting it all Together
So we’ve learnt that VBA can hold data temporarily and call upon it at will. We’ve also learnt that Variables can be assigned different Data Types. Let’s put it all together with an example.
Let’s say you’re a Crazy Physicist, hell-bent on world domination. You might want to do some calculations to check your ‘3-Phase Moon-Laser of Ultimate Destruction’ is going to work properly.
In your calculations you might want some variable to hold important bits of information such as laser power, weight of the earth, distance to the moon etc. Each of these could be stored like this.
'Here we are assigning the types of variable we plan to use
Dim LaserPower As Double
Dim EarthWeight As Single
Dim Distance2Moon As Long
Dim DestructionLevel As Variant
'Here we are storing different bit of data in each variable
LaserPower = 6.246 'PetaWatts
EarthWeight = 5.972E+24 'kg
Distance2Moon = 384400 'km
'We can then use the variables to do calculations
DestructionLevel = (2.4 * LaserPower) / (EarthWeight + Distance2Moon)
MsgBox "The Destruction level will be: " & DestructionLevel
So we did a few things in the code above:
- We chose some variable names (LaserPower, EarthWeight, Distance2Moon) known as Declaring variables.
- We then proceeded to store some data in each one of these variables using the statement: Variable Name = ‘Some Data’.
- We then used these variables in a calculation
- So you can see in the Line 14, instead of typing out all those numbers again, Dr.Evil can just use the variable name we assigned to each number to calculate the ‘DestructionLevel’ of his moon laser.
Of course I talk to myself... sometimes I need an expert opinion