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
Interesting topic. Nice 🙂
Thank You 🙂