CODEWITHSUNDEEP
excelvba
AxleWack
AxleWack

Reputation: 1911

Setting variable values dynamically using VBA

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.

Upvotes: 0

Views: 41

Answers (2)

Nacorid
Nacorid

Reputation: 793

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

Upvotes: 0

Sphinx
Sphinx

Reputation: 660

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

Upvotes: 2

Related Questions