VBA-Lesson 6.2. Data types (Variables)

In the previous lesson, we looked at variables in which certain data can be written, but they cannot contain more than one value. To get around this limitation, there are arrays in VBA that can store multiple values.

Here are some examples of different arrays:

Sub variables()
'Example of declaring a variable
Dim var1 As String
   
An example of declaring a 1-dimensional array
Dim array1(4) As String
   
An example of declaring a 2-dimensional array
Dim array2(4, 3) As String
   
An example of declaring a 3-dimensional array
Dim array3(4, 3, 2) As String
End Sub

One-dimensional Array

Dim array1(4) As String

A one-dimensional array can be thought of as a table consisting of one column. The array array1(4) is a one-dimensional array that can hold 5 records. Why 5? Because the numbers of entries in the array start from zero (0, 1, 2, 3, 4).

Two-dimensional Array

Now let's consider a 2-dimensional array:

Dim array2(4, 3) As String

It will already look like a table with 5 rows and 4 columns:

'Assigning values for colored cells
array2(0, 0) = "Value in red cell"
array2(4, 1) = "Value in green cell"
array2(2, 3) = "Value in blue cell"

Constants

Like variables, constants can be used to store values, but the difference is that values cannot change. We can add a constant to avoid repeating the value, for example13.14:

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

This makes the code easier to understand and edit. It also allows you to change the value of the constant quite simply:

Sub const_example()
    'Constant declaration + value assignment
     Const ANNUAL_RATE As Double = 13.14
   
     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

Visibility area of variables

If a variable is declared at the beginning of a procedure (Sub), then it can be used only in this procedure. The value of the variable will no longer be available after the procedure is executed.

Sub procedure1()
    Dim var1 As Integer
    ' => The variable is only valid in this procedure
End Sub

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

In order to use a variable in any module procedure, we only need to declare it at the very beginning of the module. And if you declare a variable like this, it will be available 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 use the same variable in all modules of the book, you should only replace Dim with Global in the previous example:

Global var1 As Integer

To use a variable after executing the procedure in which it appears, replace Dim with Static:

Sub procedure1()
     Static var1 As Integer
End Sub

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

Static Sub procedure1()
     Dim var1 As Integer
End Sub

Creating custom type variables

Here is a quick example of how you can create your own type:

'Creating a variable type
Type customers
     last_name As String
     first_name As String
End Type
   
Sub variables()
     'Variable declaration
     Dim cust1 As customers
   
     'Assigning a value to cust1
     cust1.last_name = "Smith"
     cust1.first_name = "John"
   
     'Usage example
     MsgBox cust1.last_name & " " & cust1.first_name
End Sub

Articles on the topic: