Naming Of Sub Procedures

[John]

I am writing an Excel Visual Basic Applications (VBA) Program involving
calling one of a number of Sub procedures from within a Sub procedure. The
name of the Sub procedure being called varies depending upon the data
provided, so that the name of the Sub procedure could be:
Sub Program_a
Sub Program_b
Etc

I have tried defining a string called Type_Of_Function which may be equated
to Program_a, Program_b etc, depending upon the data provided. A parameter is
passed to the calling Sub procedure which in turn invokes one of the Sub
procedures to be called so that, in this case a statement in the calling Sub
procedure would appear as:
Call Sub Type_Of_Function()

to enable either Program_a or Program_b to be called as appropriate.
Visual Basic throws up an error when attempting to execute Type_Of_Function
and execution halts.

Can you please tell me how I can circumvent this particular problem to
enable me to invoke either procedure Proram_a or Program_b from the calling
Sub procedure?

Regards,

John

 

[JLGWhiz]

Post your problem code.

 

[JLGWhiz]

If you have a procedure called MainProcess then

Sub MainProcess()
'Declarations here
'Define variables her
'Some preliminary code maybe
Program_a 'Calls procedure of that name
Program_b 'Calls procedure of that name
'Cleanup code goes here
End Sub

If Program_a or Program_b have variables to be passed then you will need to
use the keyword Call and this sample syntax

Call Program_a(myVar As Variant)

 

[JLGWhiz]

This should be:

Call Program_a(ByRef myVar As Variant)

 

[Tim Williams]

Dim NameOfSub
NameOfSub = "Proc_A"

Application.Run NameOfSub

Tim

 

[John]

Tim,

Thanks for your help.

Can you tell me how to pass arguments - Do I need to declare them as global
variables as the procedures I will be invoking will be returning a number of
argument values?

Regards,

John.

 

[Tim Williams]

See Help on "Run" in the Excel VBE

Tim

Tim,

Thanks for your help.

Can you tell me how to pass arguments - Do I need to declare them as global
variables as the procedures I will be invoking will be returning a numberof
argument values?

Regards,

John.






- Show quoted text -