How to Auto Generate Combinations in Excel VBA Macro

This article will show you how to auto generate a combination of different elements with VBA macro in Excel in detail. This macro is used to list all combinations by selecting a specified number of elements from a specific group of elements. For example, you might want to list all the different combinations of characteristics of a product, or you might want to output all the different role combinations of team members.

If you want to generate a combination from a browser instead of Excel, please see the reference page below.

CHECK THE NUMBER OF COMBINATIONS IN ADVANCE USING THE COMBIN FUNCTION

In Excel, there is a function to pre-calculate the total number of combinations to pick a certain number of items from a range of items. THAT’S THE “COMBIN” FUNCTION. The function takes two arguments: the total number of objects to be combined (n) and the number of items to select at once (r).

The usage of the COMBIN function is as follows:

1. First, select an empty cell. This is the destination of the results.
2. Then type =COMBIN(n, r) in that cell. where n is the overall number and r is the number of items to select at once.
3. Finally, press Enter to see the result.

For instance

=COMBIN(7, 4)

to calculate the number of all combinations of 4 out of 7 items. This allows you to check in advance whether the number of combinations generated will be too large (35 pieces).

This is especially useful when generating combinations that pick many items from a large range. You can use the COMBIN function to avoid performance problems caused by an unexpectedly large number of combinations.

Macro code and its description

Once you’ve verified that the number of combinations isn’t unexpectedly large, it’s time to actually create a combination list.

Here is the code for the macro: {Alt}Start VBE with +{F11} etc., and paste it into “Standard Module” → “Insert”. After setting, close VBE and execute macro “”Combinations with +{F8},{Alt} etc.

Option Explicit

Sub Combinations()
    Dim rng As Range
    Dim outCell As Range
    Dim arr() As Variant
    Dim r As Long
    Dim n As Long
    Dim i As Long
    Dim cmb As Variant
    
    ' アイテム範囲を指定
    Set rng = Application.InputBox("アイテム範囲を選択してください", Type:=8)
    arr = rng.Value
    n = rng.Cells.Count
    
    ' 取り出す数を指定
    r = Application.InputBox("取り出す数を入力してください", Type:=1)
    
    ' 出力先の開始セルを指定
    Set outCell = Application.InputBox("出力先の開始セルを指定してください", Type:=8)
    
    ' 組み合わせを計算し、出力
    For Each cmb In CombinationsArray(arr, r)
        outCell.Resize(, r).Value = cmb
        Set outCell = outCell.Offset(1, 0)
    Next cmb
End Sub

Function CombinationsArray(arr() As Variant, r As Long) As Collection
    Dim cmb As New Collection
    CombinationsArrayRecur cmb, arr, "", r, LBound(arr)
    Set CombinationsArray = cmb
End Function

Sub CombinationsArrayRecur(cmb As Collection, arr() As Variant, prefix As String, r As Long, start As Long)
    Dim i As Long
    For i = start To UBound(arr)
        If r = 1 Then
            cmb.Add Split(IIf(prefix <> "", prefix & " ", "") & arr(i, 1))
        Else
            CombinationsArrayRecur cmb, arr, IIf(prefix <> "", prefix & " ", "") & arr(i, 1), r - 1, i + 1
        End If
    Next i
End Sub

This macro works in the following steps: Make an item list somewhere in advance. In the example below, a list is created in cells I2:I11. However, I intend to use only 7 types of I2:I8.

1. First, let the user select a range of items.

   

2. Next, let them specify the number of items to retrieve.

   

3. After that, let them choose the position of the cells where they want to output the result.

   

4. The CPU calculates all combinations that retrieve the specified number of items from the selected range and outputs them to the specified cell.

   

Summary

The above is how to automatically generate combinations using VBA macro in Excel. Macros make it easy for users to specify a range of items, the number of items to retrieve, and the location of the cells that output the result. This macro is very useful when you want to list all combinations of different elements, or when you want to find all possible combinations by selecting a certain number of elements from a range.

Reference: A tool that performs equivalent processing from a browser

On the following page, you can generate a combination list from your browser.

組合せ(重複取出しなし)の全パターンを生成するツール

組み合わせ(重複取り出しなし)の全パターンを生成するツール n個の異なる要素からr個を選ぶ際の、全組み合わせパターンを生成します。選んだ要素の順番は考慮しません。 サンプルのように、テキストエリアへ要素を1行ごとに入力してください。 組合せ...

www.helpaso.net

2023.08.25