Solved: Call Function by name contined in String Variable and Return Value

[Lagos]

I want to select one function out of many (3)functions, by assigning the name of the selected function <Fun2> into the string variable <FunctionName>.

I then want to call the selected function and return the value from the function to the variable <Result>. How can I do that?

The code below illustrates what I want to do, but obviously it is not working.

[VBA]Public Sub Main()
Dim FunctionName As String, Result As Double
FunctionName = "Fun2" ' Selected function
Result = FunctionName ' I know this does not work...
MsgBox Result
End Sub

Public Function Fun1() As Double
Fun1 = 1
End Function

Public Function Fun2() As Double
Fun2 = 2
End Function

Public Function Fun3() As Double
Fun3 = 3
End Function[/VBA]

[Kenneth Hobs]

Welcome to the forum!

You may have noticed that xld input code tags for you. If you click the Go Advanced button, there is a Code # button that inserts the tag for you to paste between them.

cheers

[vba]Public Sub Main()
Dim FunctionName As String, Result As Double
FunctionName = "Fun2" ' Selected function
Result = Application.Run("'" & ThisWorkbook.FullName & "'!Module1." & FunctionName)
MsgBox Result
End Sub[/vba]

[Lagos]

Thanks Kenneth! It works....!

- Lagos

[Lagos]

Hi Kenneth,

I now need to pass an argument <Arg>, in the Function call. Can you please help me?

Thx,

Lagos

[vba]
Public Sub Main()
Dim FunctionName As String, Result As Double, Arg as Double
FunctionName = "Fun2" ' Selected function
Result = Application.Run("'" & ThisWorkbook.FullName & "'!Module1." & FunctionName) ' How to add <Arg> to this line?
End Sub [/vba]

[Kenneth Hobs]

Tip: Press F1 when your cursor is in or next to a keyword like Run to see the help in the VBE. From help:

Syntax

expression.Run(Macro, Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13, Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30)

[Lagos]

Thanks!