Calling a function using variable name in VBA

Question

Thanks in advance.

I'm trying to call a function using a variable name. It's throwing the error

The macro 'testfunc' cannot be found. Resume without error

My Code: I tried below 3 statements but all are throwing the same error.

sFuncName = "testfunc"

ExtractData = Application.Run("Sheet.xls" & sFuncName, s1, s2)

ExtractData = Application.Run(sFuncName, s1, s2)

ExtractData = CallByName("Sheet.xls", sFuncName, VbMethod, s1, s2)

Note that the sFuncName returns a value & I have 2 Excels opened at that moment, I'm trying to copy data from one Excel to other.

Please help me in achieving this.

Private Function ABC
  sFuncName = oMapWksht.Cells(sMapRowItr, 3).Value
  'sFuncName = "testfunc"
  ExtractData = Application.Run("Sheet.xls" & sFuncName, s1, s2)
  'ExtractData = Application.Run(sFuncName, s1, s2)
  'ExtractData = CallByName("Sheet.xls", sFuncName, VbMethod, s1, s2)
End Function
Public Function testfunc(ByVal s1 As String, ByVal s2 As String) As    Boolean
--
---
----
End Function

Where exactly is testfunc located: is it in a different workbook from the code you posted? — Tim Williams
– Tim Williams, Commented Mar 2, 2016 at 21:37
I have 2 workbooks open. I have all the code in Book1 and trying to copy data from Book2. "testfunc" is another function in same workbook. — Venkat
– Venkat, Commented Mar 2, 2016 at 21:40
In your VBAProject, make sure you put your function (such as testfunc) into a module (under the folder Modules in the Project Explorer) and not as VBA code inside a worksheet or workbook (under the folder Microsoft Excel Objects). — rskar
– rskar, Commented Mar 2, 2016 at 21:43
Scott -- I need to dynamically call diff functions. Private Function ABC sFuncName = oMapWksht.Cells(sMapRowItr, 3).Value 'sFuncName = "testfunc" ExtractData = Application.Run("Sheet.xls" & sFuncName, s1, s2) 'ExtractData = Application.Run(sFuncName, s1, s2) 'ExtractData = CallByName("Sheet.xls", sFuncName, VbMethod, s1, s2) End Function Public Function testfunc(ByVal s1 As String, ByVal s2 As String) As Boolean -- --- ---- End Function — Venkat
– Venkat, Commented Mar 2, 2016 at 21:47

Mathieu Guindon · Accepted Answer · 2016-03-02 21:55:46Z

2

If the function you're calling is in the same workbook as the caller, it doesn't matter how many workbooks are opened.

If the invoked procedure doesn't have a unique name across the entire project, you need to qualify the function name with at least the name of the module though. This should get you going:

Module1 (caller)

Sub DoSomething()
    Debug.Print Application.Run("Module2.Test", "Test")
End Sub

Module2 (callee)

Public Function Test(ByVal title As String) As VbMsgBoxResult
    Test = MsgBox("Works?", vbQuestion + vbYesNo, title)
End Function

answered Mar 2, 2016 at 21:55

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

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

11 Comments

Venkat Over a year ago

Invoked function is unique across the project. sFuncName returns diff values as i read the value dynamically from sheet. sFuncName returns diff values testfunc1, testfunc2 ....

Mathieu Guindon Over a year ago

Then the problem is elsewhere, possibly in the value you're reading in the cell - are you completely totally absolutely sure what you're reading matches the name of an existing procedure? No leading/trailing whitespace?

Venkat Over a year ago

I have checked that it clearly returns the function name in error without any leading/trailing spaces. I'm using excel 2003 wonder if that wont cause any issue.

Mathieu Guindon Over a year ago

Try the example code I gave you right here, does it work? (works here on Excel 2010)

Venkat Over a year ago

I replaced ("Module2.Test", "Test") to ("sheet.xls!testfunc", "Test") and it returned same error...macro cannot be found

|

Gary's Student · Accepted Answer · 2016-03-02 22:08:09Z

1

You can use a String variable to hold the Sub's name. Here is an example of a Sub in the same module as the caller:

Sub MAIN()
    Dim st As String
    st = "whatever"
    Application.Run st
End Sub

Public Sub whatever()
    MsgBox "whatever"
End Sub

If the caller and called subs are in different modules, the syntax may be slightly different.

EDIT#1:

If we want to call a Sub in a different module within the same workbook, the syntax would be the same.

If we want to call a Sub within another workbook, and both workbooks are open then the caller would look like:

Sub MAIN()
    Dim st As String
    st = "Book2.xlsm!whatever"
    Application.Run st
End Sub

I usually declare the called Sub Public, but I don't know if this is necessary.

EDIT#2

To use another workbook's UDF() in a worksheet cell, then:

where qwerty() is a Public UDF()

edited Mar 2, 2016 at 22:08

answered Mar 2, 2016 at 21:49

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

1 Comment

Venkat Over a year ago

I've just added some part of my script...where I'm getting function name (sFuncName )from a cell in workbook.

Collectives™ on Stack Overflow

Calling a function using variable name in VBA

2 Answers 2

11 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related