Help Tom Ogilvy

[terryspencer2003]

I want to limit the amount of decision logic I put in my looping
routine. I am doing up to 11,000 iterations. This is why I want all
the decision logic to be stated up front BEFORE the loop. I can use
the IF statments within the loop as you recommended, but they are
likely going to have to be Case Statements due to the complexity of my
procedure.

Lets simplify my original question. Lets say I want to choose between
three sub routines. If I simply want to pass the name of these
subrouitnes (all in same module with loop logic) to a variable, how do
I go about doing it? How do I dimension the variable? Say variable
is called PriceOption. Does it use quotes when being called? I tried
using Application.Run but could not get it to work.

Dim PriceOPtion As String
Dim X as Integer

Select Case
Case = 1
PriceOption = "Sub1"
Case = 2
PriceOption = "Sub2"
Case = 3
PriceOption = "Sub3"


For X =1 to 11,00

Call PriceOption ( or is it Application.Run "PriceOption")

Next X

 

[Chip Pearson]

You would use code like

Application.Run PriceOption


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Ken Wright]

Why??? Tom won't need any help

Seriously though, take a look at the following examples - There are 3 different
ways listed of doing it. The 5 example subs are all listed as Private so that
they don't show up in Tools / Macros. You can use Call to initiate another
routine, or you can leave it out, but the advantage of using it is that you are
effectively commenting your code to ay that you are running another routine at
the point. The first two example show you it with and without, but the 3rd is
probably a better way of doing it in this case. You can put the whole of the
'Case is' and the 'Do this when it is' on the same line by using a colon
separator, and for one liners like this it works well. Obviously in the example
below the fact that there is only a single line in each of the private subs
means that you could have just used that one line after the Case Is: statement,
but it was only for example.

Sub WhichOne()

Dim Cel As Range
Set Cel = ActiveSheet.Cells(2, 1)

Select Case Cel.Value
Case Is = 1
Call Pro1
Case Is = 2
Call Pro2
Case Is = 3
Call Pro3
Case Is = 4
Call Pro4
Case Is = 5
Call Pro5
Case Else
Cells(1, 1).Value = 999
End Select

End Sub

Private Sub Pro1()
Cells(1, 1).Value = 10
End Sub

Private Sub Pro2()
Cells(1, 1).Value = 15
End Sub

Private Sub Pro3()
Cells(1, 1).Value = 20
End Sub

Private Sub Pro4()
Cells(1, 1).Value = 25
End Sub

Private Sub Pro5()
Cells(1, 1).Value = 30

----------------------------------------------------
Or without the 'Call' bit

Sub WhichOne()

Dim Cel As Range
Set Cel = ActiveSheet.Cells(2, 1)

Select Case Cel.Value
Case Is = 1
Pro1
Case Is = 2
Pro2
Case Is = 3
Pro3
Case Is = 4
Pro4
Case Is = 5
Pro5
Case Else
Cells(1, 1).Value = 999
End Select

End Sub

---------------------------------------------------
Mixed to show it can work in this example with either method.

Sub WhichOne()

Dim Cel As Range
Set Cel = ActiveSheet.Cells(2, 1)

Select Case Cel.Value
Case Is = 1: Call Pro1
Case Is = 2: Pro2
Case Is = 3: Call Pro3
Case Is = 4: Pro4
Case Is = 5: Call Pro5
Case Else
Cells(1, 1).Value = 999
End Select

End Sub

 

[Tom Ogilvy]

The problem is you never show a variable to be used in making the case
decision, so you apparently didn't/don't understand the answer I provided.

assume you have a varible inum which will be used in your case statement to
decide which sub to run. Then:

Dim PriceOPtion As String
Dim X as Integer
Dim Inum as Integer

Inum = int(rnd*3+1)

Select Case Inum
Case = 1
PriceOption = "Sub1"
Case = 2
PriceOption = "Sub2"
Case = 3
PriceOption = "Sub3"
End Select

For X =1 to 11,00

Application.Run PriceOption

Next X

but as I originally suggest, you can reduce your code with

Dim X as Integer
Dim Inum as Integer

Inum = int(rnd*3+1)


For X =1 to 11,00

Application.Run "Sub" & inum

Next X

if you use the sequential numbering sequence of subs you present in you
example.

 

[Tom Ogilvy]

Ken,

he wants to make a onetime selection of the sub to be called, then use it in
his loop.

Thus the recommendation to use application.Run

although I suspect the overhead of including the case construct in the loop
with the advantage of using Call (as you suggest) would significantly
overcome any advantage gained by the reduction to one case statement but
incurring the overhead of using application.run 11000 times.

 

[Ken Wright]

Apologies for not reading it more carefully Tom, I hadn't picked up on that.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Ken,

he wants to make a onetime selection of the sub to be called, then use it in
his loop.

Thus the recommendation to use application.Run

although I suspect the overhead of including the case construct in the loop
with the advantage of using Call (as you suggest) would significantly
overcome any advantage gained by the reduction to one case statement but
incurring the overhead of using application.run 11000 times.

<snip>

 

[Tom Ogilvy]

Perhaps not, but your answer had/has great merit.

I was just elaborating.

 

[terryspencer2003]

Thanks Tom and others. It worked for me. I unfortunatley built a
simuation model that was fine for 100 runs but was not fine for
11,000. As such, I had to band-aid it at the last minute in order to
have the work done in time. Knowing how to code it one thing, knowing
how to structure it so that it does not take 40 hours to run is
another. Still learning!

Thanks again. This forum has proved to be worth its weight in gold.
Don't know what I would do without it.

TS

 

[Ken Wright]

Just another thought, but if you have lots of other sheets full of formulas that
are all linked to the sheets that are changing (ie perhaps a Monte Carlo
simulation), and you don't necessarily need those links to be active until all
the iterations have run (or charts that are linked), you might want to consider
turning off calculation for those sheets at the start of the sub, and then
turning it back on again at the end. Avoid selecting wherever possible, ensure
screenupdating is turned off and use the statusbar to give a guide as to how far
through the routine has got. 11,000 is not really that many, although anything
you can do to limit that is will help in terms of speed. Try and identify the
bottlenecks, ie what part of the routine is taking the time, or how long does it
take to recalc every time between iterations etc, and then attack that.

 

[terryspencer2003]

Yes Ken I did all the above. Problem was I was calling subs within my
looping code. Depending on my Case (11 by 1000 iterations) not all
the subs needed to be called. But I was calling each sub 1000 times

So what I eventually did was create a control module which had a large
Case Statment which called the appropiate subs when needed. Then my
loop called the control sub. Reduced time from 40 hours to 8!

TS