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