Suppose I have a VBA one dimension array (or dict or collection) with X values. I need to perform an action with these values in batches of Y.

So if X = 55 and Y = 25, I would need to loop 3 times:

1. Pick values 1 to 25 and perform action
2. Pick values 26 to 50 and perform action
3. Pick last 5 values and perform action

Any ideas with good performance will be greatly appreciated :)

Edit:

I came up with the code below. It works although doesn't look very concise

Sub test()

Dim arr As Variant
Dim temparr As Variant
Dim sippno As Integer
Dim loopend As Integer
Dim loopstart As Integer
Dim batchsize As Integer
Dim i As Integer

'Storing main array with all values

arr = Sheet1.Range("A1:A" & Sheet1.Range("A" & Rows.Count).End(xlUp).Row).Value

'Setting count of values, batch size and starting step for loop

sippno = WorksheetFunction.CountA(arr)
loopstart = 1
batchsize = 10

Do Until sippno = 0

If sippno < batchsize Then
loopend = loopstart   sippno - 1
Else
loopend = loopstart   batchsize - 1
End If

ReDim temparr(loopstart To loopend)

For i = loopstart To loopend

temparr(i) = WorksheetFunction.Index(arr, i, 0)

sippno = sippno - 1

Next

loopstart = loopend   1

'Action to be performed with batch of values stored in second array

Debug.Print WorksheetFunction.TextJoin(", ", True, temparr)

Loop

End Sub

CodePudding user response：

2d array because to lazy to encode 1d but same idea with 1d:

Sub test()
    arr = Sheet3.Range("A1").CurrentRegion.Value2
    x = UBound(arr)
    y = 5
    jj = y
    
    For j = 1 To UBound(arr)
        sumaction = sumaction   arr(j, 1)
        If (UBound(arr) - jj) < 0 Then
            jj = UBound(arr)
            sumaction = 0
        End If
        If j = jj Then
            dosomething = sumaction * 2
            sumaction = 0
            jj = jj   y
        End If
    Next j
End Sub

CodePudding user response：

Option Explicit
Sub splice()

    Const batch = 10
   
    Dim data, ar()
    Dim lastrow As Long, n As Long, i As Long
    Dim j As Long, r As Long
   
    With Sheet1
        lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
        data = .Range("A1:A" & lastrow).Value2
    End With
    i = Int(lastrow / batch)
   
    For n = 0 To i
        r = batch
        If n = i Then
            r = lastrow Mod batch
        End If
        If r > 0 Then
            ReDim ar(r - 1)
            For j = 1 To r
                ar(j - 1) = data(j   n * batch, 1)
            Next
            ' do something
            Debug.Print Join(ar, ",")
        End If
    Next
End Sub