Declaring a variable is usually done using the keyword Dim, for example
Dim MyVar As Integer
or
Dim MyVar
In the first example, we tell Visual Basic that we are going to store numbers of type Integer in the variable. Integers are whole numbers between -32,768 and +32,767. If we try to store a number outside this range, or another kind of data in the variable, we will get an error message. For example,
MyVar = 37
is OK, but
MyVar = "Lounge"
will cause an error message.
In the second example, we merely tell Visual Basic that we are going to use a variable named MyVar, but we don't specify what kind of data we are going to store in it. Since VBA is very forgiving, it allows us to store any kind of data in it - text, numbers, dates, and more complicated data. Both
MyVar = 37
and
MyVar = "Lounge"
are OK in this situation. Technically speaking, omitting the data type is the same as declaring a variable as a Variant.
If we can store anything in a Variant, why bother to declare a data type explicitly at all? Isn't it more work?
It is more work, but it does have advantages, the same way declaring variables explicitly has advantages. It enables Visual Basic to warn us if we try to assign the wrong type of value to a variable. Specifying the data type also makes code execution more efficient. With a Variant, Visual Basic has to inspect the data type each time you do something with the variable, and try to determine the best way to handle it. With an explicitly declared data type, Visual Basic knows in advance what to expect.
Still, there is a place for Variants too.
Some functions can return more than one type of result. For example, the Excel VBA function GetOpenFilename displays the Open dialog. It returns the Boolean value False if the user cancels the dialog, otherwise it returns a string or an array of strings depending on whether the MultiSelect argument is False or True.
In a construction such as
Code: Select all
MyArray = Array(2, 3, 5, 8, 13)
For Each MyItem In MyArray
...
Next MyItem
If you want to declare multiple variables, you can do so on multiple lines:
Dim FirstName As String
Dim LastName As String
or on a single line:
Dim FirstName As String, LastName As String
Note that both variables are explicitly declared as strings. If I had written
Dim FirstName, LastName As String
LastName would have been declared as a string, but FirstName would have been a Variant because I didn't specify a data type explicitly. This is different from many other programming languages, so keep it in mind when writing VBA code.