## VBA permutation of arrays

## VBA permutation of arrays

(OP)

Hello,

I have the following Table

Name Min Max Nvals

A -5 6.7 6

B -10 23 4

C 2 15 3

I am looking for a code that generates all the sets of [A B C] that include all the possible variatiosn as defined in the Table

The cath is that the code has to be able to cope with an apriori unknown number of variables , so nested loopes option seems to me unusable

Thank you

I have the following Table

Name Min Max Nvals

A -5 6.7 6

B -10 23 4

C 2 15 3

I am looking for a code that generates all the sets of [A B C] that include all the possible variatiosn as defined in the Table

The cath is that the code has to be able to cope with an apriori unknown number of variables , so nested loopes option seems to me unusable

Thank you

## RE: VBA permutation of arrays

## RE: VBA permutation of arrays

Is it:

or

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson## RE: VBA permutation of arrays

Something like this or something else ?

## CODE

## RE: VBA permutation of arrays

## RE: VBA permutation of arrays

I have 3 variables A,B,C which can have repectively 6,4 and 3 possible values.

I need to create SETS of [A B C] that cover all the possible combinations of A, B and C values.

All this in VBA where the input is the NUMBER of VARIABLES (here 3) and the VARIABLES VALUES.

The INPUT data ---------------------------------------------------

Name Min Max Nvals

A -5 6.7 6 i.e. A=[5 11.7 18.4 25.1 31.8 38.5]

B -10 23 4 i.e. B=[10 33 56 79]

C 2 15 3 i.e. C=[2 17 32]

The total number of combinations(permutations) is 6x4x3=72

The output data (Comnination Sets) ------------------------------

no. A B C

1 5 10 2

2 5 33 2

3 5 56 2

4 5 79 2

5 11.7 10 2

6 11.7 33 2

.

.

.

72 38.5 79 32

## RE: VBA permutation of arrays

A can have the values of 5 11.7 18.4 25.1 31.8 38.5

B can have the values of 10 33 56 79

C can have the values of 2 17 32

and the outcome you want is:

then you can simply do:

## CODE

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson## RE: VBA permutation of arrays

In your code, there are 3 nested loops.

If another case requires 10 variable, I need to rewrite the code to accomodate 10 nested loops.

No big deal but, it limites the generality of the code.

I think, I need some kind of a recursive method. Not in expert in this

## RE: VBA permutation of arrays

businessrequirement for this?Or it is strictly 'academic' (homework) exercise?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson## RE: VBA permutation of arrays

## RE: VBA permutation of arrays

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...unless you multiply yourself by the speed of light squared, then...

You Energy!## RE: VBA permutation of arrays

It's not quite what I was looking for

## RE: VBA permutation of arrays

guessthat Min is the minimum value of each set, Max is the maximum value of each set and Nvals the number of members in each set then every one of your examples fails your criteria.Name Min Max Nvals

A -5 6.7 6 i.e. A=[5 11.7 18.4 25.1 31.8 38.5]

Min is not a member. Max is not a member. Five out of six values are larger than Max

B -10 23 4 i.e. B=[10 33 56 79]

Min is not a member. Max is not a member. Three out of four values are larger than Max

C 2 15 3 i.e. C=[2 17 32]

Max is not a member. Two out of three values are larger than Max

You do understand that coding is the systematic solution of a fully defined problem, right?

## RE: VBA permutation of arrays

To make the implementation universal - i.e. without need to have 3 loops for 3 arrays, 4 loops for 4 arrays,..etc - we can create 2 procedures:

1) procedure product_of_two:

which will have on input two 1D arrays and returns an array of arrays

This procedure computes only product of two arrays

2) procedure product_of_n

which will have on input array of arrays and will return other array of arrays

This procedure will use the previous procedure to process compound array given on input.

Unfortunatelly, i don't have now Windows machine by my hands, so i have done proof of concept on Linux using python, which uses lists instead of arrays.

katto.py## CODE

With the data given above it delivers the same result as posted by Andrzejek

## CODE

if you uncomment

## CODE

## CODE

## RE: VBA permutation of arrays

But Katto is constrained to VBA.

## RE: VBA permutation of arrays

That's why I tried to do it without itertools so that it could then be written in VBA in the similar way.

## RE: VBA permutation of arrays

Have you looked inside iteertools, to see how they do it?

Edit:

Ok, I just peaked. Iteertools is implemented in c.

## RE: VBA permutation of arrays

https://chandoo.org/wp/generate-all-combinations-f...

## RE: VBA permutation of arrays

## CODE

Running the test makro prints in the immediate window the same result as before

## CODE

## RE: VBA permutation of arrays

Thank you all for your responses

I'll try the VBA code suggested

## RE: VBA permutation of arrays

It works great

## RE: VBA permutation of arrays

I'm glad to be able to help you a little bit.

It works, but now when I look at the code again, I see some places where it could be improved. I haven't done anything in VBA for a long time and so it wasn't that easy for me to rewrite the Python code in VBA at first. It was an opportunity for me to write something in VBA again and afterwards I had a good feeling that I had succeeded in the end.

## RE: VBA permutation of arrays

thanks again