VBA Course: Variables (Part 2)
Arrays
Variables allow storing a single value per variable, arrays allow storing a multitude of values per array (their use is close to that of variables).
Here are some examples of declarations:
'Example of variable declaration
Dim var1 As String
'Example of 1-dimensional array declaration
Dim array1(4) As String
'Example of 2-dimensional array declaration
Dim array2(4, 3) As String
The 1-dimensional array
'Example of 1-dimensional array declaration
Dim array1(4) As String
In this declaration, there is only one number in parentheses, so it is a one-dimensional array.
This number also indicates the number of cells in the array. In this case, array1(4) is an array whose cells go from 0 to 4, so it is an array that has 5 cells:
And here's how to assign values to the 5 cells of this array:
array1(0) = "Value of cell 0"
array1(1) = "Value of cell 1"
array1(2) = "Value of cell 2"
array1(3) = "Value of cell 3"
array1(4) = "Value of cell 4"
The 2-dimensional array
'Example of 2-dimensional array declaration
Dim array2(4, 3) As String
And here's how to assign values to cells of a 2-dimensional array:
array2(0, 0) = "Value of red cell"
array2(4, 1) = "Value of green cell"
array2(2, 3) = "Value of blue cell"
We will return to arrays later in this course.
Constants
Constants allow storing values like variables, but they cannot be modified (hence their name) after being declared.
For example, these few lines calculate the amount of VAT based on a VAT rate of 12.34%:
Sub example()
Cells(1, 1) = Cells(1, 2) * 0.1234
Cells(2, 1) = Cells(2, 2) * 0.1234
Cells(3, 1) = Cells(3, 2) * 0.1234
Cells(4, 1) = Cells(4, 2) * 0.1234
Cells(5, 1) = Cells(5, 2) * 0.1234
End Sub
To avoid repetitions and facilitate the reading of this code, it is possible to declare the VAT rate as a constant:
Sub example()
'Declaration of the constant + assignment of its value
Const VAT_RATE As Double = 0.1234
Cells(1, 1) = Cells(1, 2) * VAT_RATE
Cells(2, 1) = Cells(2, 2) * VAT_RATE
Cells(3, 1) = Cells(3, 2) * VAT_RATE
Cells(4, 1) = Cells(4, 2) * VAT_RATE
Cells(5, 1) = Cells(5, 2) * VAT_RATE
End Sub
By using a constant, the day when the VAT rate will change, you will just need to modify the value of the constant in the code once (instead of searching and replacing all the 0.1234 values in the code).
The scope of variables
If the variable is declared at the beginning of a procedure (Sub), it can only be used in this same procedure. The value of the variable is not preserved after the execution of the procedure.
Sub procedure1()
Dim var1 As Integer
'=> Use of the variable in the procedure only
End Sub
Sub procedure2()
'=> Unable to use var1 here
End Sub
To be able to use a variable in all procedures of a module, simply declare it at the beginning of the module. Moreover, this allows preserving the value of the variable until the workbook is closed.
Dim var1 As Integer
Sub procedure1()
'=> Use of var1 possible
End Sub
Sub procedure2()
'=> Use of var1 possible
End Sub
Same principle to use a variable in all modules, except that Dim is replaced by Public:
Public var1 As Integer
To preserve the value of a variable at the end of a procedure, replace Dim by Static:
Sub procedure1()
Static var1 As Integer
End Sub
To preserve the values of all variables of a procedure, add Static in front of Sub:
Static Sub procedure1()
Dim var1 As Integer
End Sub
Create your own variable type
We won't dwell on this point, here's just an example:
'Creating a variable type
Type User
Name As String
FirstName As String
End Type
Sub example()
'Declaration
Dim user1 As User
'Assigning values to user1
user1.Name = "Smith"
user1.FirstName = "John"
'Example of use
MsgBox user1.Name & " " & user1.FirstName
End Sub