1800-222-WTF

Excel & VBA

20 Feb 2018

What are Variables?

/
Posted By
/
Comments2
/

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!

Option Explicit

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.

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.

Option Explicit

Figure 1 – Option Explicit

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:

 Type Default Value Range
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:

https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/data-type-summary

How do I Name Variables?

There are some rules and regulations you need to be aware of. When creating a variable name, it:

  1. Must not begin with a number.
  2. Must not contain any spaces.
  3. Must not exceed 40 characters in length.
  4. May contain only letters, numbers of the underscore symbol ( _ ).
  5. 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.

DrEvil

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.

So we did a few things in the code above:

  1. We chose some variable names (LaserPower, EarthWeight, Distance2Moon) known as Declaring variables.
  2. We then proceeded to store some data in each one of these variables using the statement: Variable Name = ‘Some Data’.
  3. We then used these variables in a calculation
  4. 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