从阵列创建的所有可能的独特组合的列表（使用VBA） [英] Creating a list of all possible unique combinations from an array (using VBA)

问题描述

背景：我拉着所有的字段名称从数据库到一个数组 - 我有这部分没有问题做的，所以我已经有包含所有字段（allfields（））数组，我有多少字段的计数有（numfields）

Background: I'm pulling all of the field names from a database into an array - I've got this part done without a problem, so I already have an array containing all the fields (allfields()) and I have a count of how many fields there are (numfields).

我现在尝试编译所有可从这些不同的字段名称进行独特的组合。例如，如果我的三个字段名称，DESCR，日期，我会想返回如下：

I am now trying to compile all of the unique combinations that can be made from those various field names. For example, if my three fields are NAME, DESCR, DATE, I would want to return the following:

• NAME，DESCR，DATE


• NAME，DESCR


• 名称，日期


• DESCR，日期


• 名称


• DESCR


• 日期

我试过了这几个不同的东西，包括多个嵌套循环，并在这里修改了答案：<一href=\"http://stackoverflow.com/questions/2509436/how-to-make-all-possible-sum-combinations-from-array-elements-in-vb\">How使从VB 数组元素的所有可能的组合总和要适合我的需要，但好像我没有我的工作电脑上获得必要的libaries（系统或System.Collections.Generic）它的出现，因为它只有VBA。

I've tried a few different things for this, including multiple nested loops, and modifying the answer here: How to make all possible sum combinations from array elements in VB to fit my needs, but it appears as though I do not have access to the necessary libaries (System or System.Collections.Generic) on my work PC, as it only has VBA.

有没有人有一点VB code的周围踢将实现这一目的？

Does anyone have a bit of VB code kicking around that would fulfill this purpose?

非常感谢！

推荐答案

我在几年前也有类似的要求。我不记得为什么，我不再有code，但我记得的算法。对我来说，这是一个一次性的活动，所以我想一个简单的code。我不关心效率。

I had a similar requirement some years ago. I do not remember why and I no longer have the code but I do remember the algorithm. For me this was a one-off exercise so I wanted an easy code. I did not care about efficiency.

我会假定基于阵列，因为它使一个稍微更容易解释。由于VBA支持一个基于阵列，这应该是OK，虽然它是一个容易调整到零基于阵列如果这是你想要的。

I will assume one-based arrays because it makes for a marginally easier explanation. Since VBA supports one-based arrays, this should be OK although it is an easy adjustment to zero-based arrays if that is what you want.

AllFields（1至NumFields）持有的名字。

AllFields(1 To NumFields) holds the names.

有一个循环：对于量输入x = 1到2 ^ NumFields - 1

Have a Loop: For Inx = 1 To 2^NumFields - 1

在循环考虑量输入x与编号为1至NumFields位的二进制数。对于1和NumFields之间的每一个N，如果N位一个包括AllFields（N），在这个组合。

Within the loop consider Inx as a binary number with bits numbered 1 to NumFields. For each N between 1 and NumFields, if bit N is one include AllFields(N) in this combination.

这个循环产生2 ^ NumFields - 1组合：

This loop generates the 2^NumFields - 1 combinations:

Names: A B C

Inx:          001 010 011 100 101 110 111

CombinationS:   C  B   BC A   A C AB  ABC

使用VBA唯一的困难越来越的位n的值。

The only difficulty with VBA is getting the value of Bit N.

附加栏目

使用具有去是实施我的算法中位大家好，我想我有更好的展现我会怎么做它。

With everyone having at go at implementing bits of my algorithm, I thought I had better show how I would have done it.

我已经填写测试数据的数组与讨厌的一组字段名，因为我们没有被告知哪些字符可能是一个名字。

I have filled an array of test data with an nasty set of field names since we have not been told what characters might be in a name.

子程序GenerateCombinations做业务。我是递归的粉丝，但我不认为我的算法够复杂了在这种情况下使用的理由。我回到我在其中preFER以串联交错数组的结果。 GenerateCombinations的输出被输出到即时窗口来证明它的输出。

The subroutine GenerateCombinations does the business. I am a fan of recursion but I do not think my algorithm is complicated enough to justify its use in this case. I return the result in a jagged array which I prefer to concatenation. The output of GenerateCombinations is output to the immediate window to demonstrate its output.

Option Explicit

这个例程演示GenerateCombinations

This routine demonstrates GenerateCombinations

Sub Test()

  Dim InxComb As Integer
  Dim InxResult As Integer
  Dim TestData() As Variant
  Dim Result() As Variant

  TestData = Array("A A", "B,B", "C|C", "D;D", "E:E", "F.F", "G/G")

  Call GenerateCombinations(TestData, Result)

  For InxResult = 0 To UBound(Result)
    Debug.Print Right("  " & InxResult + 1, 3) & " ";
    For InxComb = 0 To UBound(Result(InxResult))
      Debug.Print "[" & Result(InxResult)(InxComb) & "] ";
    Next
    Debug.Print
  Next

End Sub

GenerateCombinations做业务。

GenerateCombinations does the business.

Sub GenerateCombinations(ByRef AllFields() As Variant, _
                                             ByRef Result() As Variant)

  Dim InxResultCrnt As Integer
  Dim InxField As Integer
  Dim InxResult As Integer
  Dim I As Integer
  Dim NumFields As Integer
  Dim Powers() As Integer
  Dim ResultCrnt() As String

  NumFields = UBound(AllFields) - LBound(AllFields) + 1

  ReDim Result(0 To 2 ^ NumFields - 2)  ' one entry per combination 
  ReDim Powers(0 To NumFields - 1)          ' one entry per field name

  ' Generate powers used for extracting bits from InxResult
  For InxField = 0 To NumFields - 1
    Powers(InxField) = 2 ^ InxField
  Next

 For InxResult = 0 To 2 ^ NumFields - 2
    ' Size ResultCrnt to the max number of fields per combination
    ' Build this loop's combination in ResultCrnt
    ReDim ResultCrnt(0 To NumFields - 1)
    InxResultCrnt = -1
    For InxField = 0 To NumFields - 1
      If ((InxResult + 1) And Powers(InxField)) <> 0 Then
        ' This field required in this combination
        InxResultCrnt = InxResultCrnt + 1
        ResultCrnt(InxResultCrnt) = AllFields(InxField)
      End If
    Next
    ' Discard unused trailing entries
    ReDim Preserve ResultCrnt(0 To InxResultCrnt)
    ' Store this loop's combination in return array
    Result(InxResult) = ResultCrnt
  Next

End Sub

这篇关于从阵列创建的所有可能的独特组合的列表（使用VBA）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！