Using arrays with loops-CodePudding

Good day to everyone, I have multiple pages and I want to loop them all into individual arrays, but I couldn't. Thanks in advance to those who will help.


Option Explicit
Dim d1() As Variant, d2() As Variant, d3() As Variant, d4() As Variant, d5() As Variant, d6() As Variant
Dim d7() As Variant, d8() As Variant, d9() As Variant, d10() As Variant, d11() As Variant, d12() As Variant
Dim personel As Collection, sonuc() As Variant

Sub examples()
Dim i As Integer
d1 = Sheet1.Range("A4:AX34")
d2 = Sheet2.Range("A4:AX34")
d3 = Sheet3.Range("A4:AX34")
d4 = Sheet4.Range("A4:AX34")
d5 = Sheet5.Range("A4:AX34")
d6 = Sheet6.Range("A4:AX34")
d7 = Sheet7.Range("A4:AX34")
d8 = Sheet8.Range("A4:AX34")
d9 = Sheet9.Range("A4:AX34")
d10 = Sheet10.Range("A4:AX34")
d11 = Sheet11.Range("A4:AX34")
d12 = Sheet12.Range("A4:AX34")
End Sub

CodePudding user response：

I'm not sure I understand what you are asking, but based on your code, it looks as though you could use help converting what you have written using 12 variables into code that uses one array. Here is the simplest conversion of your existing code to use a single array instead of many variables:

Option Explicit
Dim d(12) As Variant

Sub example1()
    d(1) = Sheet1.Range("A4:AX34")
    d(2) = Sheet2.Range("A4:AX34")
    d(3) = Sheet3.Range("A4:AX34")
    d(4) = Sheet4.Range("A4:AX34")
    d(5) = Sheet5.Range("A4:AX34")
    d(6) = Sheet6.Range("A4:AX34")
    d(7) = Sheet7.Range("A4:AX34")
    d(8) = Sheet8.Range("A4:AX34")
    d(9) = Sheet9.Range("A4:AX34")
    d(10) = Sheet10.Range("A4:AX34")
    d(11) = Sheet11.Range("A4:AX34")
    d(12) = Sheet12.Range("A4:AX34")
End Sub

However, it is unclear what you are trying to accomplish. I'm not sure what it means to just assign a variant to a range. If you are trying to have each variable (or element of the array) refer to the range on each sheet, then you'll need to use the "set" keyword to bind a variable (or element of an array) to an object. In this case, it's slightly more efficient to declare the array of the "range" type instead of variant as follows. I've added a loop to print the value from the first row, third column in the range on each sheet (which is cell C4 on the sheet) for demonstration purposes.

Option Explicit
Dim d(12) As Range

Sub example1()
    Dim i As Integer
    Set d(1) = Sheet1.Range("A4:AX34")
    Set d(2) = Sheet2.Range("A4:AX34")
    Set d(3) = Sheet3.Range("A4:AX34")
    Set d(4) = Sheet4.Range("A4:AX34")
    Set d(5) = Sheet5.Range("A4:AX34")
    Set d(6) = Sheet6.Range("A4:AX34")
    Set d(7) = Sheet7.Range("A4:AX34")
    Set d(8) = Sheet8.Range("A4:AX34")
    Set d(9) = Sheet9.Range("A4:AX34")
    Set d(10) = Sheet10.Range("A4:AX34")
    Set d(11) = Sheet11.Range("A4:AX34")
    Set d(12) = Sheet12.Range("A4:AX34")

    For i = 1 To 12
        Debug.Print d(i).Cells(1, 3).Address, d(i).Cells(1, 3).Value
    Next
End Sub

This code is a bit longer than I would like it to be but to refer to each sheet by its object name, that what we have to do. If sheet names have a pattern similar to the code names, we can shorten the code significantly:

Option Explicit
Dim d(12) As Range

Sub example1()
    Dim i As Integer
    
    For i = 1 To 12
       Set d(i) = ThisWorkbook.Worksheets("Sheet" & i).Range("A4:AX34")
       Debug.Print d(i).Cells(1, 3).Address, d(i).Cells(1, 3).Value
    Next
    
End Sub

An important thing about Arrays in VBA is that they start counting at 0 instead of 1. So in all of the examples above, we have an unused element of the array at the very beginngin. We can issue a compiler directive (Option Base 1) atop the code to tell the VBA interpreter to start arrays counting at 1 as follows:

Option Base 1
Option Explicit
Dim d(12) As Range

Sub example1()
    Dim i As Integer
    
    For i = 1 To 12
       Set d(i) = ThisWorkbook.Worksheets("Sheet" & i).Range("A4:AX34")
       Debug.Print d(i).Cells(1, 3).Address, d(i).Cells(1, 3).Value
    Next
    
End Sub

However, I think it is more commonly done to allow the arrays to count from zero and just offset the array index as needed as follows:

Option Explicit
Dim d(11) As Range

Sub example1()
    Dim i As Integer
    
    For i = 0 To 11
       Set d(i) = ThisWorkbook.Worksheets("Sheet" & i   1).Range("A4:AX34")
       Debug.Print d(i).Cells(1, 3).Address, d(i).Cells(1, 3).Value
    Next
    
End Sub

This is all fine, but we often do not know the number of elements we need in the array before the code runs. To make the size of the array dynamic, we declare the array without specifying a size and use the ReDim statement to set its size at runtime (notice the empty parentheses when the array is delclared, and the use of the "Ubound" statement in the For Loop to find the upper boundry of the array):

Option Explicit
Dim d() As Range

Sub example1()
    Dim i As Integer
    ReDim d(ThisWorkbook.Worksheets.Count - 1)
    For i = 0 To UBound(d)
       Set d(i) = ThisWorkbook.Worksheets("Sheet" & i   1).Range("A4:AX34")
       Debug.Print d(i).Cells(1, 3).Address, d(i).Cells(1, 3).Value
    Next
    
End Sub

Finally, I see from the original code posting that you are familiar with collections. Because a collection is an object with properties and methods, it may be simpler to use a collection instead of an array, which is just a memory allocation scheme.

Option Explicit
Dim d As New Collection

Sub example1()
    Dim i As Integer
    
    For i = 1 To ThisWorkbook.Worksheets.Count
       d.Add ThisWorkbook.Worksheets("Sheet" & i).Range("A4:AX34")
       Debug.Print d(i).Cells(1, 3).Address, d(i).Cells(1, 3).Value
    Next
    
End Sub

Notice here that collections start counting at 1.