呵呵,我自己来回答。
1. 这样的代码作用,是最基本的,通过循环产生不重复组合的【组合代码】
结果是: =combin(m,n) 的所有组合解。
2. 用VBA代码生成组合结果,方法有很多。
a. 本例所示,是最基本的,或称最标准的数组循环方法。
优点是:
计算速度最快。(其他任何方法,都不可能比它更快!)
第2个优点是,非常直观,便于新手理解和模仿。
缺点是:
通用性不强。即,如果m,n参数需要改变,那么代码就需要重新编写……
但是,这个问题,我有自己的独门暗器解决。
即,我另做了一个生成组合代码的VBA
- Sub 自动生成组合代码()
-
- m = Val(InputBox("组合对象个数 m =", "生成组合代码", 10))
- If m = 0 Then Exit Sub
- n = Val(InputBox("抽取个数 n =", "生成组合代码", 3))
- If n = 0 Then Exit Sub
- AC = WorksheetFunction.Combin(m, n)
- MsgBox "组合结果总数 = " & AC
-
- tn = "AutoCombin_" & Format(Date, "yymmdd_") & Format(Time, "hhmm_") & "Macro"
- '本组合代码名称为: 【AutoCombin_yymmdd_hhmm_Macro】,确保每次名称不同
- s = s & "Sub " & tn & "()" & Chr(10) & Chr(10)
- s = s & "tms = Timer" & Chr(10)
- s = s & "m = " & m & " : n = " & n & Chr(10)
- s = s & "ReDim jg(1 To " & AC & ", 0 To n)" & Chr(10)
-
- T1 = "Dim i1%"
- For i = 2 To n
- T1 = T1 & ", i" & i & "%"
- Next
- s = s & T1 & Chr(10)
-
- s = s & "For i1 = 1 To " & m - n + 1 & Chr(10)
-
- T2 = " jg(i, 0) = i1"
- T3 = " jg(i, 1) = i1"
- T4 = "next i" & n
- For i = 2 To n
- s = s & "For i" & i & " = i" & i - 1 & " + 1 To " & m - n + i & Chr(10)
- T2 = T2 & " & "","" & i" & i
- T3 = T3 & " : jg(i, " & i & ") = i" & i
- T4 = T4 & ", i" & n - i + 1
- Next
- s = s & " i = i + 1" & Chr(10)
- s = s & T2 & Chr(10)
- s = s & T3 & Chr(10)
- s = s & T4 & Chr(10)
-
- s = s & "[a1].CurrentRegion.Clear: [a1].Resize(i, n + 1) = jg" & Chr(10)
- s = s & "[a1].Resize(, n + 1).EntireColumn.AutoFit" & Chr(10)
- s = s & "Msgbox i & vbcr & Format(Timer - tms ,""0.000s"")" & Chr(10) & Chr(10)
- s = s & "End Sub" & Chr(10)
-
- '以上为止,自动生成了能够计算combin(m,n)组合的标准循环代码 s
- [a1] = s '输出代码到A1单元格中(代码行之间有换行符)
- ' 如果只需要添加模块到当前工作簿,但不需要自动执行新生成的代码,则:
- Set t = ActiveWorkbook.VBProject.VBComponents.Add(1) '在当前工作簿文件中自动添加模块
- t.CodeModule.AddFromString s '在此新添加的模块中写入此代码
- Exit Sub
- '如果要立即自动执行此组合代码,那么:
- Set t = ThisWorkbook.VBProject.VBComponents.Add(1) '在本代码所属工作簿文件中自动添加模块,
- t.CodeModule.AddFromString s '在此新添加的模块中写入此代码
- Application.Run tn '自动执行此代码
- ThisWorkbook.VBProject.VBComponents.Remove t '执行完之后立即删掉此代码模块→不需要时注释掉
-
- End Sub
复制代码执行上述代码,就能自动生成标准的,生成combin(m,n)解的代码。
例如,执行代码,输入m=5,n=3参数后,自动在当前工作薄内生成一个模块,里面有代码:
Sub AutoCombin_121111_1419_Macro()
tms = Timer
m = 5: n = 3
ReDim jg(1 To 10, 0 To n)
Dim i1%, i2%, i3%
For i1 = 1 To 3
For i2 = i1 + 1 To 4
For i3 = i2 + 1 To 5
i = i + 1
jg(i, 0) = i1 & "," & i2 & "," & i3
jg(i, 1) = i1: jg(i, 2) = i2: jg(i, 3) = i3
Next i3, i2, i1
[a1].CurrentRegion.Clear: [a1].Resize(i, n + 1) = jg
[a1].Resize(, n + 1).EntireColumn.AutoFit
MsgBox i & vbCr & Format(Timer - tms, "0.000s")
End Sub