Calling a function using variable name in VBA

[venkat46]

<tbody>
[TD="class: votecell"][/TD]
[TD="class: postcell"] Thanks in advance.

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

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

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

<code>sFuncName = "testfunc"

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

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

ExtractData = CallByName("Sheet.xls", sFuncName, VbMethod, s1, s2) </code>

Note that the <code>sFuncName</code> 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.

[/TD]

</tbody>

 

[Ken Cowen]

I think all you need is an ! between your workbook name and your procedure name.

sFuncName = "!testfunc"


I hope this helps.

Ken

 

[venkat46]

Sorry I've missed to mention that... I used ! as well...still its showing same error

 

[Ken Cowen]

Try closing the workbook that contains the procedure. If the workbook is closed, calling the procedure should cause the workbook to open. If it doesn't open, then you have a problem with the workbook identification. If it opens and can't find the procedure, then there is a problem with finding the procedure. Is the procedure in a regular module, and not set to private? I was a little surprised that your workbook name was *.xls rather then *.xlsm, but .xls could be correct.

Good luck.

Ken

 

[venkat46]

the code is working if I call the function name directly. Everything is as expected. Moment I replace the function name with a variable its throwing the error.

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

 

[Ken Cowen]

This works fine with either of the run lines.


Sub test()


Dim str As Strin
str = "'!test"
'Application.Run ("'" & "delete.xlsm" & "'!test"), CStr("xxx")
Application.Run ("'" & "delete.xlsm" & str), CStr("xxx")
End Sub


Can you try your first option with a ! in the variable name? I think my syntax is a little different, the variable is not in the same parenthesis as the function.

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