ExcelBanter - Excel VBA code executing a variable as a Sub.

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Excel VBA code executing a variable as a Sub. (https://www.excelbanter.com/excel-programming/278586-excel-vba-code-executing-variable-sub.html)

Excel VBA code executing a variable as a Sub.

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

Excel VBA code executing a variable as a Sub.

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.
-----Original Message-----
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
.

Excel VBA code executing a variable as a Sub.

Dim szReport As String
szReport = CLng(szSelectedReport)
szReport = "RunReport" & szReport
Application.Run szReport

--
Regards,
Tom Ogilvy

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

Excel VBA code executing a variable as a Sub.

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

"Tom Ogilvy" wrote in message ...
Dim szReport As String
szReport = CLng(szSelectedReport)
szReport = "RunReport" & szReport
Application.Run szReport

--
Regards,
Tom Ogilvy

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