VBA version of Python's Zip function (FOR EACH loop)

Question

In Python, I can iterate through multiple lists at once, by using the zip function. How would I do this in a macro in VBA in Excel?

Pseudo Code

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

score 5 · Answer 1 · answered Feb 01 '17 at 13:27

There is no direct equivalent of zip in VBA.
Note1 its much more efficient to get the data into arrays and loop on the arrays rather than process cell by cell
Note2 Its very unusual to get .Text from cells because it does not get the underlying value, may give ####, and is slow : better to use .Value2

If the ranges are contiguous it would be best to use a 2D array, otherwise 4 individual arrays

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

Related to usage of .Value2: https://stackoverflow.com/a/17363466/3015186 — Niko Föhr, Nov 20 '19 at 08:59

score 0 · Answer 2 · answered Feb 01 '17 at 13:46

If your actual code have the "lists" on the same sheet where the corresponding ones-twos-threes-four are on the same row this could work for you.

Dim ws As Worksheet
Set ws = ActiveSheet
Set rng = ws.Range("A2:D673")

For Each rw In rng.Rows
    ' where 4 is the column number of the D-column
    ones = ws.Cells(rw.Row, 4).Value 
    twos = ws.Cells(rw.Row, 1).Value
    threes = ws.Cells(rw.Row, 2).Value
    fours = ws.Cells(rw.Row, 3).Value
Next rw

VBA version of Python's Zip function (FOR EACH loop)

2 Answers2