VBA Variables

A variable is a name given to a particular area in memory that stores a value, which can later be reused. When creating a variable in VBA it is necessary to specify the type of data to be stored within the variable, for example, whether it is a string, integer or floating-point number. The value of a variable can be changed as much as is necessary once it has been created.

When naming variables in VBA it should be noted that the names are not case sensitive. Variable names must start with a letter, but can then be followed by either letters, numbers or underscores and cannot exceed 255 characters in length. One further restriction is that a variable name cannot be the same as a reserved word. Reserved words are those that have a specific purpose in VBA and therefore cannot be used for anything else.

When declaring a variable, it takes the following format.

Dim variable_name As variable_type

The words in blue above are examples of reserved words in VBA. Variable name is the name given to the variable in memory, which can be used to reference it later. Variable type refers to the type of data that can be stored in a variable. The most commonly used data types in VBA are ‘String’, ‘Boolean’, ‘Integer’ and ‘Double’, where ‘String’ contains a string of characters, ‘Boolean’ is either ‘True’ or ‘False’, ‘Integer’ is a whole number and ‘Double’ refers to  a number that can contain decimal places, such as 1.5.

Below are examples of variable declarations.

Dim firstName As String
Dim age As Integer
Dim price As Double
Dim result As Boolean

Once a variable has been declared, a value can be assigned to it.

firstName = "Fred"
age = 30
price = 12.99
result = True

It is possible, if required to add these values to cells in a spreadsheet. Here the values of the variables are added to cells ‘A1’ through to ‘A4’.

Range("A1").Value = firstName
Range("A2").Value = age
Range("A3").Value = price
Range("A4").Value = result