假設我有一個帶有 X 值的 VBA 一維陣列（或字典或集合）。我需要以 Y 的批次執行這些值的操作。
因此，如果 X = 55 且 Y = 25，我將需要回圈 3 次：
- 選擇值 1 到 25 并執行操作
- 選擇值 26 到 50 并執行操作
- 選擇最后 5 個值并執行操作
任何具有良好性能的想法將不勝感激:)
編輯：
我想出了下面的代碼。雖然看起來不是很簡潔，但它有效
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
uj5u.com熱心網友回復：
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
uj5u.com熱心網友回復：
2d 陣列，因為懶得編碼 1d 但與 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
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/334639.html