Excel VBA中的动态函数调用 [英] Dynamic Function Calls in Excel VBA

问题描述

如果可能有动态调用函数的方法，我只是好奇。例如。

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

或者这是必要的：

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.

谢谢！

edit1：
这是我有的代码，下面列出的错误：

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

错误是：

运行时错误'1004'无法运行宏foo1。该宏可能在此工作簿中不可用，或者所有宏可能被禁用。

推荐答案

href =http://msdn.microsoft.com/en-us/library/office/aa220716%28v=office.11​​%29.aspx>运行方法！

You want the run method!

Sub callSomeFoo(i as Integer)
   Application.Run "foo" & i
End Sub

但是不行，VBA不喜欢这个名字c $ c> foo1 ，所以它不会工作。

But that wont work, VBA doesn't like the name foo1 and so it won't work.

这是因为FOO1也可以是一个单元格参考。
Application.Run的第一个参数可以是Range对象，因此它评估FOO1，认为
它是一个单元格，并且由于该单元格为空，因此不知道该怎么做。 -
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. – 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

这篇关于Excel VBA中的动态函数调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！