Change variables value in array

There are variables assigned to array. Will variables value change, if array values change?

There was the question on StackOverflow https://stackoverflow.com/questions/57686894/vba-change-value-of-variables-in-an-array/57687005#57687005. Actually, my answer is marked as correct one with 6 votes. What’s more, the question itself has 7 votes (for now). And this is why I want to describe and sum this up in one post.

In the first place, let’s sketch up the situation – 3 logic variables and 1 array.

Option Explicit

Dim var1 As Boolean
Dim var2 As Boolean
Dim var3 As Boolean

Sub Test()
Dim arr As Variant
Dim i As Long

After all declaration var1, var2 and var3 they take the default value – false.

arr = Array(var1, var2, var3)

For Each i In arr
    Debug.Print i
Next

To check their values we can use For Each loop and read them in immediate window.

False
False
False

After that we want to assigned True value to every element of array. You can achieve that by looping from first element LBound(arr) to the last UBound(arr).

For i = LBound(arr) To UBound(arr)
    arr(i) = True
Next

Now every array element has True value assigned. We can check that with the same method as above.

For Each i In arr
    Debug.Print i
Next

End Sub

Question & Answer

Now, after all the code, what is the value of 3 variables – var1, var2 and var3?

The answer for this question is very simple. They have the same value as it was in the beginning, in other words all False.

Why?

Firstly, variables were declared. Then, only their values were assigned to array, not whole variables. So whatever happened with values in array elements it does not affect variables value! Only if You want, You can put array results into variables. To do this You need to assign array elements value to the variables.

var1 = arr(0)
var2 = arr(1)
var3 = arr(2)

All in all, it is a good thing. To work on variables value You can choose array. They are quite convenient and the most important, really fast. Afterward You paste your results into chosen place. You can replace your source of data or paste it somewhere else.

Besides, please take a look at my previous post https://simpleexcelvba.com/arrays-as-the-best-table-alternative-in-excel-vba/. This is a good example of above issue. Also I presented there arrays speed and proved that they are faster than tables.

Whole code

Sub Test()
Dim arr As Variant
Dim i As Variant

arr = Array(var1, var2, var3)

For Each i In arr
    Debug.Print i
Next

For i = LBound(arr) To UBound(arr)
    arr(i) = True
Next

For Each i In arr
    Debug.Print i
Next

var1 = arr(0)
var2 = arr(1)
var3 = arr(2)

End Sub