VBA Course: Variables (continued)
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"
Another example, a two dimensional array:
'Sample declaration of a 2 dimensional array Dim array2(4, 3) As String
'Assigning values to three colored cells array2(0, 0) = "Red cell value" array2(4, 1) = "Green cell value" array2(2, 3) = "Blue cell value"
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