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

You would use code like

Application.Run PriceOption


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



wrote in message
om...
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

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


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

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



wrote in message
om...
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


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004

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.

--
Regards,
Tom Ogilvy

"Ken Wright" wrote in message
...
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


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

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



wrote in message
om...
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


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004

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 :-)
----------------------------------------------------------------------------



"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

<snip


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004

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

I was just elaborating.

--
Regards,
Tom Ogilvy

"Ken Wright" wrote in message
...
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 :-)
--------------------------------------------------------------------------
--



"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

<snip


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004

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.

--
Regards,
Tom Ogilvy


wrote in message
om...
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

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

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.

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

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



wrote in message
om...
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


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.562 / Virus Database: 354 - Release Date: 16/01/2004

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