Assign values to variable using loop VBA

Question

I have variables declared in VBA function like A1,A2,A3.....An

and assigned values to A1,A2,A3...An

now how can I update values in the variables using loop

Dim A1, A2, A3 As Integer, Tmp As String
A1 = 1
A2 = 2
A3 = 3


For i = 1 To 3

    Debug.Print A & i
    A & i  = i+1 --- This line is not working
Next

How can I assign variables without using any arrays

You cannot construct a variable out of two parts like that, you need an Array, see cpearson.com/excel/vbaarrays.htm — Alex K.
– Alex K., Commented Dec 8, 2016 at 15:25
If you need named identifiers, use a Scripting.Dictionary instead. — Comintern
– Comintern, Commented Dec 8, 2016 at 15:26
Note: Your Dim statement is declaring A1 and A2 as Variant. If you expected to declare them as Integer you need to use Dim A1 As Integer, A2 As Integer, A3 As Integer, Tmp As String — YowE3K
– YowE3K, Commented Dec 8, 2016 at 15:27
Why "without using arrays"? Because you don't understand them? That's exactly what they exist for. Learn to use them, learn to love them. They'll love you in return and together you'll write awesome code. Don't give up! — Mathieu Guindon
– Mathieu Guindon, Commented Dec 8, 2016 at 16:02

Gary's Student · Accepted Answer · 2016-12-08 15:28:49Z

2

Re-consider using arrays:

Sub marine()
    Dim A(1 To 3) As Long
    A(1) = 1
    A(2) = 2
    A(3) = 3
End Sub

answered Dec 8, 2016 at 15:28

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

Comments

Jiminy Cricket · Accepted Answer · 2016-12-08 17:06:16Z

You could create a collection to do this, and later loop the collection or get the value by passing in the key (variable name)

Sub TestCollection()
Dim i As Long
Dim objCollection As New Collection
Dim varVariable As Variant

    'Loop From 1 To 3. The Upper Bound Can Be Changed To Suit Your Needs
    For i = 1 To 3
        'Add The Key And Item To The Collection
        'The First Parameter Is The Item (Value You Want To Store)
        'The Second Parameter Is The Key (How You Access The Value, Like A Variable Name)
        objCollection.Add i, "A" & i
    Next i

    'The Value Passed Into The Collection Is The Key - Which Is Like The Variable Name
    Debug.Print objCollection("A1")
    Debug.Print objCollection("A2")
    Debug.Print objCollection("A3")

    'Loop All Values
    For Each varVariable In objCollection
        Debug.Print varVariable
    Next varVariable

End Sub

Collectives™ on Stack Overflow

Assign values to variable using loop VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related