Declaring variables is the way you let VBA know what the names are to the variables you are going to use. You would do this by adding in Dim statements (Dim is short for Dimension) at the beging of a Sub or function procedure. You can insert variable declaration anywhere within a procedure as long as it doesn’t precede the first use of that variable. It is both traditional and clearer to include all Dim statements together at the top of a procedure.
The simplest way of writing a Dim statement would be:
When declaring a variable it also specifies what data type the variable is and this determines what kind of data the variable can hold. If you don’t do this then VBA will automatically assign the Variant data type which means you can store any type of data in the variable.
To include the data type within a Dim statement you use the As keyword and write it like this:
Dim variableName As DataType
- Before you decide on a name for your variable you should consider the following:
- The name must start with a letter
- It must be shorter than 256 characters
- It cannot be a VBA keyword such as Sub or Dim
- It must not contain a space or the following characters: . ! # $ % & @
- The name should use the Reddick VBA Naming Convention e.g. intUpper
It would also be good practice to begin the name with a lowercase letter so that VBA does not get confused and think it’s a keyword. If the variable name contains more than one word then each subsequent word should begin with a capital letter, for example strCatchPhrase.
VBA will automatically preserves the case of the variable names which you declare, so it would also be good practice to write them all in lower case, VBA will then change them where needed when doing its syntax checks. It is also quicker to write in lower case and makes it easier for you to see misspelt variables.
Reddick VBA Naming Convention
This will provide guidelines for naming your objects in VBA. Using the standardised naming conventions, the name of the object will tell us particular information about the meaning of that object. You should use the following prefixes at the start of your variable names to indicated the type of data they will hold.
byte = byte
bool = Boolean (true/False)
int = integer
lng = long
sng = single
adbl = double
cur = currency
dec = decimal
date = date
obj = object
str = string
stf = fixed length string
var = variant
Storing Values in Variables
When you have declared your variables at the beginning of a procedure, you will need to assign values to them. To this by using this syntax:
variableName = value