R vs. Excel: Cartesian Products

A while back I spent a decent amount of time creating some VBA code that would give me the Cartesian product of an arbitrary number of vectors of arbitrary size. Recursion has always been a stumbling block for me so I spent most of my time testing and tweaking the code against arrays I had built in a spreadsheet.

So it should come as no surprise that R does this with a simple, built-in function: expand.grid(). The lesson remains: if you need it, 99 percent of the time someone has already coded it. Granted I was one of the few dolts who just HAD to have this in Excel, so I’m not too upset about my oversight. Code below:

-- First, in R
-- Use the Cartesian product to make sure
-- all the dogs get all the grooming services
a <- c("Saffy","Pickles","Zoe");
b <- c("nails","bath","haircut");
x <- expand.grid(a,b);

-- Now, in VBA
Function crossP(ByVal x As Collection, str As String)
 Dim outList As New Collection
 Dim words As New Collection
 Dim tempx As New Collection
 words.Add (x.item(1))

 If (x.Count = 1) Then
  '.. we have reached the last one so no need to recurse
  For i = 1 To words.item(1).Count
   outList.Add (str & words.item(1).item(i))
  Next i
 Else
  For i = 1 To words.item(1).Count
   For Each cItem In x
    tempx.Add cItem
   Next cItem
   tempx.Remove (1)
   For Each cItem In crossP(tempx, str & words.item(1).item(i))
    outList.Add cItem
   Next cItem
   Set tempx = Nothing
  Next i
 End If
 Set crossP = outList

End Function