VBA Course: Variables (continued)

Arrays

While variables only allow us to store one value each, arrays make it possible to store many values and they work almost exactly the same way.

Here are some examples of declarations:

'Sample variable declaration
Dim var1 As String
   
'Sample 1 dimensional array declaration
Dim array1(4) As String
   
'Sample 2 dimensional array declaration
Dim array2(4, 3) As String
   
'Sample 3 dimensional array declaration
Dim array3(4, 3, 2) As String

The 1 dimensional array :

'Sample 1 dimensional array declaration
Dim array1(4) As String

There is only one number in parentheses in this declaration, which means that it is a one dimensional array. This number sets the size of the array. array1(4) is an array in which the cells are numbered from 0 to 4, which means that it is an array with 5 cells:

'Assigning values to the 5 cells
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"
1dim - variables continued
Important : the first cell in an array is numbered 0.

Another example, a two dimensional array :

'Sample declaration of a 2 dimensional array
Dim array2(4, 3) As String
2dim - variables continued
'Assigning values to three colored cells
array2(0, 0) = "Red cell value"
array2(4, 1) = "Green cell value"
array2(2, 3) = "Blue cell value"

Constants

Like variables, constants can be used to store values, but the difference is that they can't be modified (thus their name).

For example, we could add a constant to avoid having to repeat a number like 6.87236476641 :

Sub const_example()
    Cells(1, 1) = Cells(1, 2) * 6.87236476641
    Cells(2, 1) = Cells(2, 2) * 6.87236476641
    Cells(3, 1) = Cells(3, 2) * 6.87236476641
    Cells(4, 1) = Cells(4, 2) * 6.87236476641
    Cells(5, 1) = Cells(5, 2) * 6.87236476641
End Sub

This makes the code much easier to read (important parts in particular) and makes it much easier to change the value of the constant, should you need to :

Sub const_example()
   'Declaration of a constant + assignment of value
    Const ANNUAL_RATE As Double = 6.87236476641
   
    Cells(1, 1) = Cells(1, 2) * ANNUAL_RATE
    Cells(2, 1) = Cells(2, 2) * ANNUAL_RATE
    Cells(3, 1) = Cells(3, 2) * ANNUAL_RATE
    Cells(4, 1) = Cells(4, 2) * ANNUAL_RATE
    Cells(5, 1) = Cells(5, 2) * ANNUAL_RATE
End Sub

The scope of variables

If a variable is declared at the beginning of a procedure (Sub), it can only be used within this same procedure. The value of the variable will not be maintained after the execution of the procedure.

Sub procedure1()
   Dim var1 As Integer
   ' => Use of a variable only within a procedure
End Sub

Sub procedure2()
   ' => var1 cannot be used here
End Sub

In order to use a variable in any of the procedures within a module, all you have to do is declare it at the beginning of the module. And if you declare a variable this way, its value will be maintained until the workbook is closed.

Dim var1 As Integer

Sub procedure1()
   ' => var1 can be used here
End Sub

Sub procedure2()
   ' => var1 can also be used here
End Sub

If you want to be able to use a variable in any module, on the same principle as the previous example, all you have to do is replace Dim with Global :

Global var1 As Integer

To maintain the value of a variable after the execution of the procedure in which it appears, replace Dim with Static :

Sub procedure1()
    Static var1 As Integer
End Sub

To maintain the values of all the variables in a procedure, add Static before Sub :

Static Sub procedure1()
    Dim var1 As Integer
End Sub

Create your own type of variable

We won't spend very much time on this point. Here is an example :

'Creation of a variable type
Type guests
    last_name As String
    first_name As String
End Type
   
Sub variables()
    'Declaration
    Dim p1 As guests
   
    'Assigning values to p1
    p1.last_name = "Smith"
    p1.first_name = "John"
   
    'Example of use
    MsgBox p1.last_name & " " & p1.first_name
End Sub
 
Download VBA course archive (PDF)