excelvbaloopsvariablesdeclaration

How to declare variable by using for-next loop


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

Solution

  • 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