VBA Course: Variables (Part 2)
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 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