Excel VBA code executing a variable as a Sub.

  • Thread starter Thread starter John Dempsey
  • Start date Start date
J

John Dempsey

I have a If ....Else ....Then with code:

ElseIf szSelectedReport = "204" Then
RunReport204
ElseIf szSelectedReport = "205" Then
RunReport205
ElseIf szSelectedReport = "220" Then
RunReport220

This goes on for sometime and what I want to produce is a shortened
version i.e.

Dim szReport As String
szReport = CDbl(szSelectedReport)
szReport = "RunReport" & szReport
szReport

So the Sub is called RunReport222 and I have created that but I dont
know how to tell VB to now run that Sub.

Can anyone help me please.

Regards

John
 
John,

If you haven't tried this, it might be worth a shot.
After you've set the value of szSelectedReport, pass it to
RunReport222, but allow RunReport222 to have one
argument. (Check out the Excel VB help screen on "Sub
Statement" and the example of using arguments with subs.)
Also check out the "Call Statement" help screen. You
could pass the report number to RunReport222 with:

Call RunReport222(204)

Control would pass to RunReport222, then when it's
finished, control would come back to your original macro.
Hope this helps.
 
Dim szReport As String
szReport = CLng(szSelectedReport)
szReport = "RunReport" & szReport
Application.Run szReport
 
Thanks for your response guys. The application.Run philosophy works
but then says it cant find the macro, it exists as a sub under the
code on a form that exists. Is there any way I can tell it to look
there. Or do I have to create the macro RunReport222 and then call
that.

Thanks in advance

John
 
Back
Top