Excel VBA and Variables

 

In computer programming, you need to store things in memory, things like numbers and strings of text. You store them so that you can retrieve them later and manipulate the numbers or strings of text in some way. To store things in memory you use a variable. Variables have a name (something that you yourself come up with) and a type (such as Integer or String). In the Visual Basic programming language you also need to declare that you are setting up a variable. This is done (mostly) with the word Dim. As an example, study the following:

Dim MyNumber As Integer

The above code sets up a variable with the name MyNumber. The type of variable is a whole number (As Integer). The Dim keyword goes at the start, and tells the programme to set up a variable of this name and type.

However, there's nothing stored inside of the MyNumber variable - VBA has just allocated the memory at this point. To store something inside of a variable, you use the equal sign (=), also known as the assignment operator. To store a value of 10, say, inside of the variable called MyNumber, you do it like this:

MyNumber = 10

The above line reads, "Assign a value of 10 to the variable called MyNumber". So the name of your variable comes first, then the equal sign. After the equal sign you type the value that you want to store inside of your variable.

Sadly, you can't set up a variable and assign a value all on the same line. (You can in Visual Basic .NET but not in Visual Basic for Applications.) So you can't do this:

Dim MyNumber As Integer = 10

 

Variable Names

You can call your variable just about anything you like. But there are a few things you're not allowed to do. They are:

  • You can't start a variable name with a number
  • You can't have spaces in your variable names, or full stops (periods)
  • You can't use any of the following characters: !, %, ?, #, $

So these variable names are OK

MyVariable
My_Variable
myvariable2

But these variable names will get you an error:

2MyVariable
My Variable
$myvariable

The first one above starts with a number, the second one has a space, and the third starts with a dollar sign.

With variable names, it's best to come up with something relevant. So if you're storing a discount rate, then call it DiscountRate and not something odd like var1.

 

In the next lesson, you'll get some practice using variables in Excel VBA.