Dynamic Function Calls in Excel VBA

Question

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.

Solution

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.

Tested working example

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

Comments

[sbanders]

Thanks! I went to try it out and got an error. Please see edit1:

[AndASM]

I know, I also encountered that. I was just doing some research on why foo1 doesn't work. See my edit for a working example.

[sbanders]

Wwweeeeiiiiirrrrddddd... Excel literally just didn't like "foo" lol. Thanks again!

[AndASM]

Foo is okay, Fooo1 is okay, but Foo1 is not. Capitalization does not matter. I have no clue why.

[Dick Kusleika]

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.

[AndASM]

Haha, I guess I still have the old column IV limit stuck in my head. That makes sense, thanks!

[sbanders]

Thanks all! I appreciate it!