VBA: Change value of variables in an array

Assigning "True" to every element of array does not make your variables same value. Nothing weird your var1 is still False, because You didn't change the value of var1, but the value of your array elements. :)

Add something like this

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

Test2

When you do arrTest = Array(var1, var2, var3) you copy the values of your variables into the arrTest. You do NOT create a reference to those variables.

Since arrTest is declared in Test() its scope is limited to Test().

If you declare arrTest at the module level, you will be able to check arrTest values from Test2. But anyway they will be completely independent from varx.

The behaviour is different if we speak about Objects. When we write Set x = obj1 we DO create a reference, and in that case a change in obj1 will be visible in x.


arrTest is a local variant variable for the Test sub. And this variable takes some public boolean values in it, which are passed ByVal. If the array is declared outside the Subs itself, then it would be accessible from everywhere, exactly the way it is intended:

Option Explicit
Option Base 0

Dim arrTest() As Variant

Sub Test()

    Dim i
    arrTest = Array(False, False, False)

    For Each i In arrTest
        Debug.Print i
    Next

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

    For Each i In arrTest
        Debug.Print i
    Next

    Test2

End Sub

Sub Test2()
    Debug.Print arrTest(LBound(arrTest)) 'or arrTest(0)
End Sub

If you need to use variables inside array and pass them as a reference, and not as a copy, the OOP in VBA may help. Create a class "Container", holding a property SomeValue, encapsulated in valueKept. Creating Class is like creating a Module in VBA:

enter image description here

Private valueKept As String

Public Property Get SomeValue() As String    
    SomeValue = valueKept    
End Property

Public Property Let SomeValue(ByVal myValue As String)    
    valueKept = myValue    
End Property

Then the SomeValue property of this class, could be changed ByRef, when the Container object is put in array:

Sub Test()

    Dim myArr As Variant
    Dim c1 As New Container
    Dim c2 As New Container

    c1.SomeValue = 5
    c2.SomeValue = 10

    myArr = Array(c1, c2)

    Test2 myArr
    Debug.Print c1.SomeValue '55

End Sub

Sub Test2(myArr As Variant)
    myArr(0).SomeValue = 55
End Sub

Tags:

Arrays

Excel

Vba