VBA: Change value of variables in an array

Question

How do I permanently change the value of a variable in an array? I wrote some code that illustrates my problem:

Option Explicit
    Dim var1 As Boolean, var2 As Boolean, var3 As Boolean

Sub Test()
    Dim arrTest As Variant
    Dim i

    arrTest = Array(var1, var2, var3)

    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 "var1 in Sub Test2 : " & var1
    If var1 Then
        Debug.Print "Hello"
    End If
End Sub

The output in the direct window is:

False
False
False
True
True
True
var1 in Sub Test2 : False

The first six lines make sense to me because I changed the value of the variables in Test. However, it obviously wasn't permanent or it was confined to that one sub and so "Hello" in Test2 didn't get printed.

How do I change this? I've read that for each loops are read-only, but why does the for-loop not work?

Teamothy · Accepted Answer · 2019-08-28 07:25:26Z

6

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

answered Aug 28, 2019 at 7:25

Teamothy

2,0163 gold badges18 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Alex Over a year ago

Thank you for the help. Is there a reason for this, why does this make sense? And if I use the variables var1, var2, var3 for nothing more than what I've shown in my code, is there a reason I should use an array? Simply writing var1 = True, var2 = True, var3 = True would be fewer lines.

Teamothy Over a year ago

Like @Patrick Honorez said below, it is a copy of you variables, not a reference. In this case there is no need to do array or loop. It is simple case in which You can deal just like You wrote above.

Alex Over a year ago

And it's impossible to put them as a reference in the array?

Teamothy Over a year ago

I'm not 100% sure, but rather not. Instead of array try scripting.dictionary object. Maybe it will help You to improve / move further with this topic :) stackoverflow.com/questions/17453272/…

Teamothy Over a year ago

I described this problem also here simpleexcelvba.com/change-variables-value-in-array

Patrick Honorez · Accepted Answer · 2019-08-28 07:28:57Z

4

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.

answered Aug 28, 2019 at 7:28

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

Comments

Vityata · Accepted Answer · 2019-08-28 16:30:23Z

2

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:

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

edited Aug 28, 2019 at 16:30

answered Aug 28, 2019 at 8:02

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

4 Comments

Alex Over a year ago

But using variables inside the array (as a reference and not as a copy) is not possible?

Vityata Over a year ago

@Alex - anything is possible with VBA, just the work around could be a bit more.

Alex Over a year ago

I haven't done anything with Container objects, so I gotta familiarize myself with it, but in some way that's even better because I'm gonna learn something new. Thank you!

Vityata Over a year ago

@Alex - Container is just a name of the class, it is not a real Container. It can be changed to anything else.

Collectives™ on Stack Overflow

VBA: Change value of variables in an array

3 Answers 3

5 Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related