How to run a VBA script

[edebe]

Hi All, I would like to ask your help with VBA.

As I've to calculate all the possible combinations, without repetitions, between 11 decimal numbers entries based on respectively 1,2,3,4,5,6,7,8,9,10,11 elements-combination based. For example, one 1 element based element combination is 5.47; one 2 based elements combination is 12.85 (3.53+9.32); one 6 based elements combination is 125.32 (3.53+94.61+9.32+6.88+5.51+5.47), and so on.

As possible solution I found the following VBA algorithm, but honestly I don't know how to run it. Somebody could help me in this?

// VBA
Function Factorial(n As Integer) As Floating
Factorial = 1
For i = 1 To n
Factorial = Factorial * i
Next
End Function
Function NbCombinations (k As Integer, n As Integer) As Floating
Dim z As Integer
z = n - k
NbCombinations = Factorial / (Factorial(k) * Factorial(z))
End Function

 

[Cyberchipz]

Hi All, I would like to ask your help with VBA.

As I've to calculate all the possible combinations, without repetitions, between 11 decimal numbers entries based on respectively 1,2,3,4,5,6,7,8,9,10,11 elements-combination based. For example, one 1 element based element combination is 5.47; one 2 based elements combination is 12.85 (3.53+9.32); one 6 based elements combination is 125.32 (3.53+94.61+9.32+6.88+5.51+5.47), and so on.

As possible solution I found the following VBA algorithm, but honestly I don't know how to run it. Somebody could help me in this?

// VBA
Function Factorial(n As Integer) As Floating
Factorial = 1
For i = 1 To n
Factorial = Factorial * i
Next
End Function
Function NbCombinations (k As Integer, n As Integer) As Floating
Dim z As Integer
z = n - k
NbCombinations = Factorial / (Factorial(k) * Factorial(z))
End Function

View attachment 45132

First, look at your Tabs at the top of the screen, while you are on a sheet. Do you have a tab called "Developer" ... example: File / Home / Insert / Page Layout / Formulas / Data / Review / View / Developer / Help ? if yes, then Click the Developer Tab, and on the left Click "Visual Basic". This will take you into developer mode. This is the first step.
If you do not have this tab available... This link you help you get started: How to Enable the Developer Tab in Excel for Windows - Excel Campus

 

[edebe]

First, look at your Tabs at the top of the screen, while you are on a sheet. Do you have a tab called "Developer" ... example: File / Home / Insert / Page Layout / Formulas / Data / Review / View / Developer / Help ? if yes, then Click the Developer Tab, and on the left Click "Visual Basic". This will take you into developer mode. This is the first step.
If you do not have this tab available... This link you help you get started: How to Enable the Developer Tab in Excel for Windows - Excel Campus

Yes, I already set it on.

 

[Cyberchipz]

Create a module and paste your code in the module... Or alternately, and this is what I would do... Under Developer, Record a macro, but don't do anything after filling in the Box, adding the name, let's call it "AMacro" , and start recording, then without doing anything else, Stop the macro. This will create a blank macro with the name "AMacro" no code in it.

Under the developer Tab, click Macros which will list existing macros... There should only be one, if you are just beginning. This should be your macro you just created.... Click on it. Then, select EDIT. ... this is the next step... It will have only the macro you just created. And look like the first part below. Paste the script you want after "End Sub" If you called the macro you created "AMacro". It should look like this....

Sub AMacro()
'
' AMacro Macro
'

'
End Sub

'And the stuff you pasted will be below it...

Function Factorial(n As Integer) As Floating
Factorial = 1
For i = 1 To n
Factorial = Factorial * i
Next
End Function
Function NbCombinations(k As Integer, n As Integer) As Floating
Dim z As Integer
z = n - k
NbCombinations = Factorial / (Factorial(k) * Factorial(z))
End Function
'This is the next step...

I am doing this to answer your question... at some point you will need to watch tutorials, and learn how to code..

 

[Cyberchipz]

I will check back with you later... the only real thing... to make it running code... is to add a line inside "AMacro" calling the NbCombinations... put the answer in a Variable... and then Debug.print Answer to see the results. In the immediate window...

The other way you can use this in a spreadsheet... is to use these functions... in a cell! NbCombinations(k As Integer, n As Integer) and replace the k with a cell that will have a value, and the n that will be another cell with a value... So, something like

 

[Cyberchipz]

But, there are problems with the code... I've modified it a bit... to get you something coherent.... but, you can tweak it now... to get what you are looking for... Since I don't know the values of (k, n) that you are looking for.... I just put in some numbers...

But this is what I ended up with... if it doesn't do what you want... the problem is in the math... Or the values for K & N

Sub AMacro()
Dim answer As Double, n As Integer
'
' AMacro Macro

'
For n = 1 To 11
answer = NbCombinations(11, n)
Next n
Debug.Print "Answer: "; answer
'
End Sub
Function Factorial(n As Integer) As Double
Dim i As Integer
Factorial = 1
For i = 1 To n
Factorial = Factorial * i
Next
End Function

Function NbCombinations(k As Integer, n As Integer) As Double
Dim z As Integer
z = n - k
NbCombinations = Factorial / (Factorial(k) * Factorial(z))

Debug.Print " NbCombinations ="; NbCombinations; "("; k; ","; n; ")"

End Function

After this, you will have to be more specific to get what you want..
In other words... what combinations were you looking for the answer to? This sounds like homework... so, I'll stop at giving you the code solution that "should" work based on what you gave me.
Note: VBA has no type "Floating" so I used "Double" which should have enough resolution for you...
Plus, I always use Option Explicit, which just means I have to declare my variables before I use them.

Good Luck!

 

[Cyberchipz]

You may have only been looking for something like this.... in a cell =+NbCombinations(J13,K14) But, the code I wrote should work for you now... in a spreadsheet.. if you supply the values in those cells J13, K13... Which of course could be whatever cells you choose to use...

 

[Cyberchipz]

Of course without knowing which combinations gave you the results you have.... my results are different... I took k and n times... so it looked like you were asking for k=11 things n at a time. But, that didn't give me what you got! Instead I got... what you see below... using the math in the formula you gave me...

Amacro
NbCombinations = 2.50521083854417E-08 ( 11 , 1 )
NbCombinations = 5.01042167708834E-08 ( 11 , 2 )
NbCombinations = 1.5031265031265E-07 ( 11 , 3 )
NbCombinations = 6.01250601250601E-07 ( 11 , 4 )
NbCombinations = 3.00625300625301E-06 ( 11 , 5 )
NbCombinations = 1.8037518037518E-05 ( 11 , 6 )
NbCombinations = 1.26262626262626E-04 ( 11 , 7 )
NbCombinations = 1.01010101010101E-03 ( 11 , 8 )
NbCombinations = 9.09090909090909E-03 ( 11 , 9 )
NbCombinations = 9.09090909090909E-02 ( 11 , 10 )
NbCombinations = 8.33333333333333E-03 ( 5 , 0 )


j13 =5 k13 = 2
Formula Cell = 0.008333333

 

[Cyberchipz]

I just read that I need to wrap my VBA code; but, I am unable to edit previous posts.... so... sorry about this... not sure what to do...

 

[Fluff]

Duplicate to: How To Generate All Possible Combinations Of Items From One List

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.