Setting variable values dynamically using VBA

Question

I dont work with VBA much, I have a number of global integer variables, such as DFromRow1, DFromRow2, DFromRow3 up until DFromRow20. I want to set each of these in sequential order in this manner(This is just a very simple example to help you understand what I am trying to do) :

Dim Count as integer
Count = 0
Begin Loop(loop here for 20 times)

    Count = Count + 1

    DFromRow & Count = Count

End Loop

The idea here is that the global variables will be populated as such:

DFromRow1 = 1
DFromRow2 = 2
DFromRow3 = 3
... up until 20

However I get an error on the & :

Expected: expression

Is there a way to do this or am I being too ambitious with what I want ?

Thanks appreciate any help.

Sphinx · Accepted Answer · 2019-07-29 08:57:37Z

2

Instead of declaring 20 variables DFromRow you can use array
Something like this

Option Explicit

Public ArrDFromRow(20)

Sub Test()

    Dim i As Long

    For i = 1 To 20
        ArrDFromRow(i) = i
    Next i

    For i = 1 To 20
        Debug.Print ArrDFromRow(i)
    Next i

End Sub

answered Jul 29, 2019 at 8:57

Sphinx

6601 gold badge7 silver badges15 bronze badges

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

1 Comment

AxleWack Over a year ago

Thanks for this! I didnt think about making use of an array!

Nacorid · Accepted Answer · 2019-07-29 09:01:43Z

0

You can use an Array to store your values like this:

Dim DRowArray(1 to 20) as Long

Sub PopulateDFromRowArray()
    Dim i as Integer
    For i = LBound(DRowArray) to UBound(DRowArray)
        DRowArray(i) = i
    Next
End Sub

Or a Dictionary, albeit you need to set a VBA reference to Microsoft Scripting Runtime:

Dim DRowDict as Scripting.Dictionary

Sub PopulateDFromRowDictionary()
    Dim i as Integer
    Set DRowDict = New Scripting.Dictionary
    For i = 1 To 20
        DRowDict.Add "DFromRow" & i, i
    Next
End Sub

answered Jul 29, 2019 at 9:01

Nacorid

7939 silver badges22 bronze badges

1 Comment

AxleWack Over a year ago

Thanks for this! The answer from Sphinx suited my situation a bit better. Appreciate it!

Collectives™ on Stack Overflow

Setting variable values dynamically using VBA

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related