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.
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.
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