Is there a more efficient loop for combinations?

[Wook]

Hi,
Hoping someone might be able to improve my code! I've got a bit of code that works, but is clearly very inefficient:

For UserTypeA = 0 To Total_Users
For UserTypeB = 0 To Total_Users
For UserTypeC = 0 To Total_Users
If UserTypeA + UserTypeB + UserTypeC = Total_Users Then
'do something
End If
Next UserTypeC
Next UserTypeB
Next UserTypeA

Effectively it's creating all the different combinations for a given number of user based on 3 different types.
This works but is very slow at high number of users as most of the looping is unnecessary as doesn't add up to total number of users.

Any suggestions?

Thanks, Wook

 

[jmacleary]

You could improve it with some exits - i will think on it.

 

[jmacleary]

Try this:

For UserTypeA = 0 To Total_Users
If UserTypeA + UserTypeB + UserTypeC > Total_Users Then Exit For
For UserTypeB = 0 To Total_Users
If UserTypeA + UserTypeB + UserTypeC > Total_Users Then Exit For
For UserTypeC = 0 To Total_Users
If UserTypeA + UserTypeB + UserTypeC > Total_Users Then Exit For
If UserTypeA + UserTypeB + UserTypeC = Total_Users Then
' do something here
End If
Next UserTypeC
UserTypeC = 0
Next UserTypeB
UserTypeB = 0
Next UserTypeA

 

[Wook]

Thanks jmacleary!
Agreed, I think this would be more efficient, basically you are removing any loops once they exceed the total.. Neat..
So now the only inefficient loops are the ones that are less than the total. Ill give it a go!
Cheers

 

[mikerickson]

I think that this will catch all combinations but without any If statements

For UserTypeA = 0 To Total_Users
    For UserTypeB = 0 To Total_Users - UserTypeA
        For UserTypeC = 0 To Total_Users - (UserTypeA + UserTypeB)
 
            'do something

        Next UserTypeC
    Next UserTypeB
Next UserTypeA

 

[offthelip]

What have you got in the

'do something

because this is often the cause of slow vba. If you are accessing the worksheet as part of the "do something" this will make the routine very slow. if you load the worksheet into a variant array instead of accessing the worksheet it would speed up your routine by a factor of about 1000 without bothering about what happens about the triple loop

 

[Wook]

mikerickson, thanks that works too, and agreed more tidy code without the ifs.

Offthehip, I had already moved all the calculation work into VBA for this very reason, but realise whilst not writing, I was still reading from the spreadsheet in the loop, sure enough moving it all into variants worked a dream!

All in all dropped the macro run time from 2 hours+ to less than 1 minute!
I love this forum, thanks all!

Cheers, Wook

 

[mikerickson]

mikerickson, thanks that works too, and agreed more tidy code without the ifs....

Not only tidy code, but If statements take time to execute (my feeling is that If branching (either true or false) is one of the more time consuming VBA things). Getting them outside of loops is one good way to speed things up.

 

[jmacleary]

Hi both - just for fun I did a very quick benchmark of the if-less v iffy solutions - there is approximately a factor of 3 difference in favour of the if-less solution - kudos to mikerickson.