python - Python Zip 函数的 VBA 版本(FOR EACH 循环)

Question

在 Python 中，我可以使用 zip 函数一次迭代多个列表。我如何在 Excel 中的 VBA 宏中执行此操作？

伪代码

Set ones = Worksheets("Insertion").Range("D2:D673")
Set twos = Worksheets("Insertion").Range("A2:A673")
Set threes = Worksheets("Insertion").Range("B2:B673")
Set fours = Worksheets("Insertion").Range("C2:C673")

For Each one, two, three, four in zip(ones.Cells, twos.Cells, threes.Cells, fours.Cells)
    Debug.Print(one.Text & two.Text & three.Text & four.Text)
Next one

Answer 1

VBA 中没有 zip 的直接等效项。
注意1 将数据放入数组并在数组上循环比逐个单元处理要高效得多
注意2 从单元格获取.Text 是非常不寻常的，因为它没有获取底层值，可能会给出####，并且速度很慢:最好使用.Value2

如果范围是连续的，最好使用 2D 数组，否则使用 4 个单独的数组

Sub testing1()
Dim var As Variant
Dim j As Long
Dim k As Long
Dim str As String
var = Worksheets("Sheet1").Range("A2:D673").Value2

For j = LBound(var) To UBound(var)
For k = LBound(var, 2) To UBound(var, 2)
str = str & var(j, k) & " "
Next k
Debug.Print str
str = ""
Next j

End Sub

Sub testing2()
Dim varA As Variant
Dim varB As Variant
Dim varC As Variant
Dim varD As Variant
Dim j As Long

varA = Worksheets("Sheet1").Range("A2:A673").Value2
varB = Worksheets("Sheet1").Range("B2:B673").Value2
varC = Worksheets("Sheet1").Range("C2:C673").Value2
varD = Worksheets("Sheet1").Range("D2:D673").Value2

For j = LBound(varA) To UBound(varA)
Debug.Print varA(j, 1) & " " & varB(j, 1) & " " & varC(j, 1) & " " & varD(j, 1)
Next j

End Sub