Reputation: 853
I was just curious if there might be a way to call functions dynamically. For example.
Sub foo1()
Debug.Print "in foo1"
End Sub
Sub foo2()
Debug.Print "in foo2"
End Sub
Is there a way that I can do something like:
Sub callSomeFoo(i as Integer)
Call foo&i
End Sub
Or is something like this necessary:
Sub callSomeFoo(i as Integer)
Select Case i
Case 1
Call foo1
Case Else
Call foo2
End Select
End Sub
Not a pressing matter... just curious. Any other creative things to do function call wise are welcome as well.
Thanks!
edit1: Here's the code I have and the error listed below:
Sub foo1()
Debug.Print "in foo1"
End Sub
Sub foo2()
Debug.Print "in foo2"
End Sub
Sub callSomeFoo()
Dim i%
'using a cell on the worksheet to determine the function. Happens to be "1"
i = Sheets("Sheet1").Range("A1").Value
'Line below works
Call foo1
'Line below gives me an error
Application.Run "foo"&i
End Sub
Error is:
Run-time error '1004' Cannot run the macro 'foo1'. The macro may not be available in this workbook or all macros may be disabled.
Upvotes: 14
Views: 12833
Reputation: 21
This is still a valid thread as I'm trying to do the same thing here in MS Excel 365 VBA.
When I use the Application.Run [name of macro here]
... I get the following msg ...
Run-time error '1004':
Cannot run the macro '[correct macro name]'. The macro may not be available in this workbook or all macros may be disabled.
The current syntax appears to be Application.Run([Macro], [Arg1], ... [Arg#])
Excel is ignoring the fact that the macro is declared as Public & contained in the same module (form's code) that I'm calling it from. The macro that I'm wanting to call, successfully runs & starts off this whole process. I can Call [hardcoded name of macro]
successfully ... but ... Application.Run [hardcoded name of macro]
... nor ... Application.Run [dynamically created string of the name of macro]
does not ... both giving the same error mentioned above.
I'm using a set of tabs to select data from a certain worksheet. Then after the data is saved, I want to refresh the listView as names may have changed or new items may have been added. So I need to refresh the list based on which "tab" (I'm using an image here) triggered the original click event. When the image is originally clicked, I'm saving an identifier to the label's tag above the listBox, indicating the source of the list.
The original list/click-event could be 1 of 7 different "tabs", so after the data is saved, I need to construct something based on the saved image.name that will trigger the correct click event to refresh the list.
I have been able to dynamically pass objects w/ ...
Name_Of_Frame.Object("img_" & image identifier here & "_Tab_pic)
So I tried something similar w/ ...
Name_Of_Frame.Object("img_" & image identifier here & "_Tab_pic).Click
... which returns an 'invalid method', despite it automatically capitalizing the 'C' in 'click', like it's an actual recognized method. I'm just guessing & grasping at straws here since there is no documentation on how to do this.
I'm getting the correct concatenated string for the name of the macro, as verified in a MsgBox.
I've even tried to proceed the macro name w/ the name of the form in-which it exists ... no joy there either.
... unknown amount of time has passed ...
What I have FINALLY come up with, was to create a separate macro & pass the image object ... as in the following ...
Dim refresh_tab_list As Object
Set refresh_tab_list = Controls("img_" & [stored tab identifier] & "_Tab_pic")
Call sel_Production(refresh_tab_list)
Set refresh_tab_list = Nothing
Private Sub sel_Production(obj_prod_cat As Object)
[do all the stuff here]
End Sub
Props to anyone that made it thru all my rambling & got down this far. I don't know if this specifically answers the OP's question, but our issues were similar. Maybe I didn't have the correct References selected or something, but given Application.Run
failed to work for me, this was the best work-around that I could come up with.
Upvotes: 0
Reputation: 10318
You want the run method!
Sub callSomeFoo(i as Integer)
Application.Run "foo" & i
End Sub
But that wont work, VBA doesn't like the name foo1
and so it won't work.
It's because FOO1 could also be a cell reference. The first arg of Application.Run can be a Range object, so it evaluates FOO1, thinks it's a cell, and since that cell is empty, doesn't know what to do. – Dick Kusleika
This can easily be solved by choosing a longer nicer method name.
Option Explicit
Public Sub TestDynamic1()
Debug.Print "TestDynamic 1"
End Sub
Sub TestDynamic2()
Debug.Print "TestDynamic 2"
End Sub
Private Sub TestDynamic3()
Debug.Print "TestDynamic 3"
End Sub
Sub callTestDynamic(i As Integer)
On Error GoTo DynamicCallError
Application.Run "TestDynamic" & i
Exit Sub
DynamicCallError:
Debug.Print "Failed dynamic call: " & Err.Description
End Sub
Public Sub TestMe()
callTestDynamic 1
callTestDynamic 2
callTestDynamic 3
callTestDynamic 4
End Sub
Upvotes: 19