VBA Blogs: Declaring Variables

To see the start of our VBA blog series, click here.

One of the most useful things you can do in VBA is to store values in the program’s memory, so that you can use it in the future.  In Excel, we would normally do this in cells – put a value in a cell, then refer back to that cell when you want to use it.  In VBA, we do this with variables.

Variables can be used to store any type of object or value.  For example, you could create a variable to store a particular sheet or workbook.  You can create a variable to hold a number, or even an array of numbers.  You can use variables to store text.  However, you generally want to define what each variable will store in advance.  We can do this by dimensioning (which is really a fancy way for telling the computer to allocate space for) variables:

For example, we can use the following to create a variable for our message box:

A String is the variable type – this tells Excel that the value being stored in this variable is text, rather than numeric or some other form of variable.

We can then add a value to this variable, using the code:

So again, in the case of our message box, we can use:

This effectively tells Excel that we want to find the variable called “MessageBoxText” and set it to represent the value “Hello World!”.  We need to note that the type of variable is important in defining how the variable will take values.  If we set the variable to be equal to “3”, then it will take on the value, but it will treat it as a String type, meaning that it can’t be used in some types of mathematical operations that don’t automatically convert text to numbers.

In some cases (in particular, the Workbook example below), we might need to use:

Let’s take a look at some other examples of variables:

(set up a variable that contains integers, i.e. whole numbers)

(set up a variable that contains decimal numbers)

(set up a variable that contains True and False values)

(set up a variable that contains a reference to the workbook, so that you can use that in other code elements, e.g. StartingWorkbook.Sheets(“Sheet1”))

Hope this all makes sense, next week we will start using our declared variables in some interesting ways!