Improve the Myrna Larson Function - Combinations and Permutations Code for MS Access

[Luthius]

There is a very good and impressive code for Permutation and Combinations developed by Myrna Larson for excel.

I would like to improve it to be a general code that can be used for MS Access too.
Can someone help me.

'Option Explicit
'*******************************************************************************
' Myrna Larson, July 25, 2000, Microsoft.Public.Excel.Misc
' http://www.mydatabasesupport.com/forums/spreadsheets/250560-combinations.html
'*******************************************************************************
Dim vAllItems As Variant
Dim Buffer() As String
Dim BufferPtr As Long
Dim Results As Worksheet
Private Sub AddPermutation(Optional PopSize As Integer = 0, _
                          Optional SetSize As Integer = 0, _
                          Optional NextMember As Integer = 0)


Static iPopSize As Integer
Static iSetSize As Integer
Static SetMembers() As Integer
Static Used() As Integer
Dim i As Integer


   If PopSize <> 0 Then
       iPopSize = PopSize
       iSetSize = SetSize
       ReDim SetMembers(1 To iSetSize) As Integer
       ReDim Used(1 To iPopSize) As Integer
       NextMember = 1
   End If
   
   For i = 1 To iPopSize
       If Used(i) = 0 Then
           SetMembers(NextMember) = i
           If NextMember <> iSetSize Then
               Used(i) = True
               AddPermutation , , NextMember + 1
               Used(i) = False
           Else
               SavePermutation SetMembers()
           End If
       End If
   Next i
   
   If NextMember = 1 Then
       SavePermutation SetMembers(), True
       Erase SetMembers
       Erase Used
   End If


End Sub


Private Sub AddCombination(Optional PopSize As Integer = 0, _
                          Optional SetSize As Integer = 0, _
                          Optional NextMember As Integer = 0, _
                          Optional NextItem As Integer = 0)


Static iPopSize As Integer
Static iSetSize As Integer
Static SetMembers() As Integer
Dim i As Integer
   
   If PopSize <> 0 Then
       iPopSize = PopSize
       iSetSize = SetSize
       ReDim SetMembers(1 To iSetSize) As Integer
       NextMember = 1
       NextItem = 1
   End If
   
   For i = NextItem To iPopSize
       SetMembers(NextMember) = i
       If NextMember <> iSetSize Then
           AddCombination , , NextMember + 1, i + 1
           Debug.Print NextMember
       Else


           SavePermutation SetMembers()
       End If
   Next i
   
   If NextMember = 1 Then
       SavePermutation SetMembers(), True
       Erase SetMembers
   End If


End Sub


Private Sub SavePermutation(ItemsChosen() As Integer, _
                           Optional FlushBuffer As Boolean = False)
Dim i As Long, sValue As String
Static RowNum As Long, ColNum As Long
   
   If RowNum = 0 Then RowNum = 1
   If ColNum = 0 Then ColNum = 1
   
   If FlushBuffer = True Or BufferPtr = UBound(Buffer()) Then
       If BufferPtr > 0 Then
           If (RowNum + BufferPtr - 1) > Rows.Count Then
               RowNum = 1
               ColNum = ColNum + 1
               If ColNum > 256 Then Exit Sub
           End If
       
       Results.Cells(RowNum, ColNum).Resize(BufferPtr, 1).Value _
           = Application.WorksheetFunction.Transpose(Buffer())
       RowNum = RowNum + BufferPtr
       'End If
       
       BufferPtr = 0
       If FlushBuffer = True Then
           Erase Buffer
           RowNum = 0
           ColNum = 0
           Exit Sub
       Else
           ReDim Buffer(1 To UBound(Buffer))
       End If
   


   'construct the next set
   For i = 1 To UBound(ItemsChosen)
       '************************************************************
'       Debug.Print vAllItems(ItemsChosen(i)) ', 1)
       'With comma space
       sValue = sValue & ", " & vAllItems(ItemsChosen(i), 1)
 '      Debug.Print sValue
       'Without comma space
       'sValue = sValue & vAllItems(ItemsChosen(i), 1)
       '************************************************************
       
   Next i
   'and save it in the buffer
   BufferPtr = BufferPtr + 1
   Buffer(BufferPtr) = Mid$(sValue, 3)
End Sub 'SavePermutation

 

[Luthius]

In fact my biggest concern is in here.

vAllItems = Rng.Offset(2, 0).Resize(PopSize).Value

It seems so unusual and I cannot change it for a blank/null array or whatever it means.
Does someone have some idea???

 

[Micron]

I see the variable declared in code, but not used at all. Perhaps you missed part of it? Regardless, I for one don't understand the question. In your question, the range is being offset 2 rows and 0 columns and its size is changed to the value of PopSize. Don't see why you'd want to make it Null.