13

I'm trying to call a function with a variable name that is generated at run time based upon a combo box value. This is straightforward in most languages but I can't seem to figure it out in Excel VBA, I suspect this is because I don't really understand how the compiler works. I've found several posts that are close but don't quite seem to do the trick. The code below is wrong but should give an idea of what I want.

Thanks

Sub main()
    'run formatting macros for each institution on format button click

     Dim fn As String
     Dim x As Boolean

     'create format function name from CB value        
     fn = "format_" & CBinst.Value

     'run function that returns bool
     x = Eval(fn)

     ...

End Sub
Improve this question

4 Answers 4

Reset to default
21

CallByName is what you'll need to accomplish the task.

example: Code in Sheet1

Option Explicit
Public Function Sum(ByVal x As Integer, ByVal y As Integer) As Long
    Sum = x + y
End Function

Code is Module1 (bas module)

Option Explicit

Sub testSum()
Dim methodToCall As String
methodToCall = "Sum"

MsgBox CallByName(Sheet1, methodToCall, VbMethod, 1, 2)
End Sub

Running the method testSum calls the method Sum using the name of the method given in a string variable, passing 2 parameters (1 and 2). The return value of the call to function is returned as output of CallByName.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Nice. I would have tried Evaluate, since I did not know that one.
Hi.. what if I don't want the 'sum' function in Sheet1.. but say in Module1. How can I call Module1 'sum' function using CallByName. This doesn't seem to work. Please advise.
@ihightower: That is not possible. You may use Application.Run and pass the method name, parameters to it.
@shahkalpesh Thanks.. that works great!. Does Application.Run has any disadvantages as compared to CallByName.. like it is slow, less efficient or something like that.
@ihightower: Regarding performance, I would say both are slow but it depends on measuring it. i.e. if you call Application.Run a 100,000 times and compare it with CallByName to see how long does it take or memory consumption etc
1

You should write a function that accepts the CB value as a parameter and then uses a select case to call the appropriate formatting function.

Something similar to this

Function SelectFormatting(Name as String) As Boolean
Select Case CBinst.Value
Case "Text1":
   SelectFormatting = Text1FormattingFunction()
Case "Text2":
   .
   .
   .
End Select
End Function
Improve this answer

1 Comment

Thanks for the answer. This is indeed the straightforward way to solve this problem. I was hoping to abstract my way around having to add cases for each CB value for two reasons: 1) the code will be regularly updated by folks of varying coding ability and it would be nice to allow them to add a new CB option and formatting function without touching the guts of the program and 2) the CB list may become fairly long. Maybe this is just ridiculous over engineering.
1

The above will work but not with a large number of names.

Use Application.Run(MacroName, Parameters)

You have to make sure that there is a macro but it is better than the above as there is no select statement.

Improve this answer

Comments

0

With respect to my answer above you might also find this useful to check whether the macro exists

'=================================================================================
'- CHECK IF A MODULE & SUBROUTINE EXISTS
'- VBA constant : vbext_pk_Proc = All procedures other than property procedures.
'- An error is generated if the Module or Sub() does not exist - so we trap them.
'---------------------------------------------------------------------------------
'- VB Editor : Tools/References - add reference TO ......
'-    .... "Microsoft Visual Basic For Applications Extensibility"
'----------------------------------------------------------------------------------
'- Brian Baulsom October 2007
'==================================================================================
Sub MacroExists()
    Dim MyModule As Object
    Dim MyModuleName As String
    Dim MySub As String
    Dim MyLine As Long
    '---------------------------------------------------------------------------
    '- test data
    MyModuleName = "TestModule"
    MySub = "Number2"
    '----------------------------------------------------------------------------
    On Error Resume Next
    '- MODULE
    Set MyModule = ActiveWorkbook.VBProject.vbComponents(MyModuleName).CodeModule
    If Err.Number <> 0 Then
    MsgBox ("Module : " & MyModuleName & vbCr & "does not exist.")
    Exit Sub
    End If
    '-----------------------------------------------------------------------------
    '- SUBROUTINE
    '- find first line of subroutine (or error)
    MyLine = MyModule.ProcStartLine(MySub, vbext_pk_Proc)
    If Err.Number <> 0 Then
    MsgBox ("Module exists      : " & MyModuleName & vbCr _
           & "Sub " & MySub & "( )  : does not exist.")
    Else
    MsgBox ("Module : " & MyModuleName & vbCr _
        & "Subroutine   : " & MySub & vbCr _
        & "Line Number : " & MyLine)
    End If
End Sub
'-----------------------------------------------------------------------------------
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.