Excel VBA Loops – How to Declare Variable Using For-Next Loop

declarationexcelloopsvariablesvba

The following code is very good.

Sub Macro1()

'Convert plain excel data to Excel Table. (Excel workbook consist of 3 sheets)
For i = 1 To 3
    Worksheets(i).Select
    Worksheets(i).ListObjects.Add SourceType:=xlSrcRange, Source:=Range("A1:C50"), XlListObjectHasHeaders:=xlYes
Next i

'Load data from Excel Table to Variant
Dim Variant1 As Variant
Variant1 = Worksheets(1).ListObjects(1).DataBodyRange

Dim Variant2 As Variant
Variant2 = Worksheets(2).ListObjects(1).DataBodyRange

Dim Variant3 As Variant
Variant3 = Worksheets(3).ListObjects(1).DataBodyRange

'Loop through each item in Third Column of Table
Dim x1 As Long
For x1 = LBound(Variant1) To UBound(Variant1)
    Debug.Print Variant1(x1, 3)
Next x1

Dim x2 As Long
For x2 = LBound(Variant2) To UBound(Variant2)
    Debug.Print Variant2(x2, 3)
Next x2

Dim x3 As Long
For x3 = LBound(Variant3) To UBound(Variant3)
    Debug.Print Variant3(x3, 3)
Next x3

End Sub

I want to shorten the above code.

The following codes need to be repaired.

Sub Macro2()

'Load data from Excel Table to Variant

For i = 1 To 3
    'The following codes need to be repaired.
    'Dim "Variant" & i As Variant
    '"Variant" & i = Worksheets(i).ListObjects(1).DataBodyRange
Next i

End Sub

I have tried an alternate approach using Scripting.Dictionary as BigBen recommended. But the following code needs to be repaired.

Sub Macro3()

    Dim dict As Object
    Set dict = Nothing
    Set dict = CreateObject("Scripting.Dictionary")
    dict.RemoveAll
    
    For i = 1 To 3
        dict.Add Key:=i, Item:=Worksheets(i).ListObjects(1).DataBodyRange
    Next i
     
End Sub

Best Answer

You can use an array:

Sub Macro3()
    Dim myInteger As Integer
    Dim data() As Variant, i As Long, r As Long, arr
    
    myInteger = 3
    ReDim data(1 To myInteger) 'can't `Dim` using a variable...
    
    For i = 1 To myInteger
        data(i) = ThisWorkbook.Worksheets(i).ListObjects(1).DataBodyRange.Value
    Next i
    
    For i = 1 To myInteger
        arr = data(i)
        For r = 1 To UBound(arr, 1)
            Debug.Print arr(r, 3)
        Next r
    Next i
End Sub