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