Use a Variable to Refer to a Sub Procedure

[Bonediggler1]

Hello-

Is it possible to use a variable to refer to a sub procedure? VBA only seems to recognize the literal name when compiling the code. E.G:

Dim strSubName as String
Dim strCustName as String 'Customer name

strSubName = strCustName & "_Process" 'Literal sub name

Select Case strCustName
...
Case "A"
Call strSubName
...
End Select

In this case strSubName would translate into "A_Process", which is the literal name of another sub procedure. However when compiling, the error "Expected sub, function, or property" pops up.

Thank you!

 

[dhookom]

This sounds a bit unorthodox but you could try use the Eval() function like:

Sub CallAnother()
    Dim strSubName As String
    Dim strCustName As String 'Customer name
    strCustName = "A"
    strSubName = strCustName & "_Process()" 'Literal sub name
   
    Select Case strCustName
    Case "A"
       Call Eval(strSubName)
    Case Else
        '
    End Select

End Sub
Public Function A_Process()
    MsgBox "Test"
End Function

Duane
Hook'D on Access
MS Access MVP

 

[Remou]

The Run command may suit:

http://msdn.microsoft.com/en-us/library/aa199108(office.10).aspx

Run (strSubName)

Or

Run (strSubName, arg1, arg2, ..., argn)

http://lessthandot.com

 

[MajP]

Why would you want to do this? It seems like a work around for some poor programming construct. I am sure there is a better way to get the same results.

 

[Bonediggler1]

Given my programming skills, I am pretty sure it is a poor programming construct. Perhaps you could offer a solution?

 

[dhookom]

Perhaps you can tell us what you are attempting to accomplish ;-) I think you have most of us confused regarding your perceived need for this type of code.

Duane
Hook'D on Access
MS Access MVP

 

[Bonediggler1]

Ok...perhaps I should have started with this.

Anyway, to make a long story short I have a variable "strCustName" and 5 customer-specific sub procedures used to process their files after import. The 5 sub procedures are all named like "Sub Customer Name_Process".

The sub procedures are called from a case statement based on strCustName. So instead of writing,

Select Case strCustName
Case A
Call A_Process
Case B
Call B_Process

...and so on, I would prefer:

Select Case strCustName
Case A, B
Call strCustName & "_Process"
....
End Select

It just saves extra code.

Thanks again for the help!

 

[JoeAtWork]

You should never hard code against information in your database - it's a maintenance nightmare!

A better solution is to add some new field to the Customer record, such as Category, and go to the correct subroutine based on that.

(And please, please don't tell me you create a new subroutine for each customer.)

 

[dhookom]

I agree with JoeAtWork. You might want to review Data belongs in your tables -- not in your code

Duane
Hook'D on Access
MS Access MVP

 

[Bonediggler1]

dhookum/JoeAtWork-

Thanks for the valuable advice. The problem I am facing is that the DB is used only for importing & processing files that come from various customers in various formats, and then using VBA to transform their data into a standardized output format that is in turn loaded into our A/R system - I am not storing data.

Given this scenario, how would I avoid storing these customer-specific processing routines in different sub procedures?

 

[Remou]

If you are the only user, you may have a case for doing this the wrong way.

http://lessthandot.com

 

[dhookom]

I agree with Remou that you may have a case. However, I generally try to build tables and process that all might identify the structure of the imported files. I always assume the structures will change and I would much rather change some data than dig through code.

Duane
Hook'D on Access
MS Access MVP