In C/C++, when I have a bunch of functions (pointers), I can store them in an array or a vector and call some of them together in a certain order. Can something similar be done in VBA?
在C/ c++中，当我有一堆函数(指针)时，我可以将它们存储在数组或向量中，并按一定的顺序调用它们中的一些。在VBA中可以做类似的事情吗?
Thanks!
谢谢!
1 个解决方案
#1
11
Yes, but I don't recommend it. VBA isn't really built for it. You've tagged this question with Excel, so I will describe how it is done for that Office Product. The general concept applies to most of the Office Suite, but each different product has a different syntax for the Application.Run method.
是的，但我不推荐。VBA并不是为它而建的。您已经用Excel标记了这个问题，因此我将描述如何为该办公产品做这些工作。一般概念适用于大多数的办公套件，但是每个不同的产品对应用程序有不同的语法。运行方法。
First, it's important to understand the two different methods of dynamically calling a procedure (sub/function) and when to use each.
首先，理解动态调用过程(sub/function)的两种不同方法以及何时使用它们是很重要的。
Application.Run
Application.Run will either run a subroutine or call a function that is stored in a standard *.bas module.
应用程序。Run将运行子例程或调用存储在标准*中的函数。bas模块。
The first parameter is the name of the procedure (passed in as a string). After that, you can pass up to 30 arguments. (If your procedure requires more than that, refactor for the love of code.)
第一个参数是过程的名称(作为字符串传入)。之后，您可以传递最多30个参数。(如果您的过程需要的不止这些，那么重构代码吧。)
There are two other important things to note about Application.Run.
关于Application.Run，还有另外两件重要的事情要注意。
- You cannot use named arguments. Args must be passed by position.
- 不能使用命名参数。Args必须通过位置。
-
Objects passed as arguments are converted to values. This means you could experience unexpected issues if you try to run a procedure that requires objects that have default properties as arguments.
作为参数传递的对象被转换为值。这意味着，如果您试图运行一个需要具有默认属性的对象作为参数的过程，您可能会遇到意想不到的问题。
Public Sub Test1() Application.Run "VBAProject.Module1.SomeFunction" End Sub
The takeaway:
Use Application.Run when you're working with a standard module.
使用应用程序。在使用标准模块时运行。
VBA.Interaction.CallByName
CallByName executes a method of an object, or sets/gets a property of an object.
CallByName执行对象的方法，或设置/获取对象的属性。
It takes in the instance of the object you want to call the method on as an argument, as well as the method name (again as a string).
它接受作为参数调用方法的对象的实例，以及方法名(同样作为字符串)。
Public Sub Test2()
Dim anObj As SomeObject
Dim result As Boolean
result = CallByName(anObj, "IsValid")
End Sub
The takeaway:
Use CallByName when you want to call a method of a class.
当您希望调用类的方法时，请使用CallByName。
No pointers.
As you can see, neither of these methods use actual pointers (at least not externally). They take in strings that they then use to find the pointer to the procedure that you want to execute. So, you'll need to know the exact name of the procedure you want to execute. You'll also need to know which method you need to use. CallByName having the extra burden of requiring an instance of the object you want to invoke. Either way, you can stores these names as strings inside of an array or collection. (Heck, even a dictionary could make sense.)
如您所见，这两种方法都不使用实际指针(至少不是在外部)。它们接收字符串，然后使用这些字符串找到要执行的过程的指针。因此，您需要知道要执行的过程的确切名称。您还需要知道需要使用哪种方法。CallByName具有要求要调用的对象的实例的额外负担。无论哪种方式，都可以将这些名称作为字符串存储在数组或集合中。(见鬼，即便是一本字典也有意义。)
So, you can either hard code these as strings, or attempt to extract the appropriate procedure names at runtime. In order to extract the procedure names, you'll need to interface with the VBIDE itself via the Microsoft Visual Basic for Applications Extensibility library. Explaining all of that here would require far too much code and effort, but I can point you to some good resources.
因此，您可以将它们硬编码为字符串，或者尝试在运行时提取适当的过程名称。为了提取过程名称，您需要通过Microsoft Visual Basic与VBIDE本身进行接口，以实现应用程序可扩展性库。在这里解释所有这些将需要太多的代码和精力，但是我可以为您指出一些好的资源。
Articles & SE Questions:
文章和SE的问题:
- Chip Pearson's Programming The VBA Editor
- Chip Pearson的VBA编辑器编程
- Extending the VBA Extensibility Library
- 扩展VBA扩展库
- Ugly workaround to get the vbext_ProcKind is breaking encapsulation
- 要获得vbext_ProcKind，最糟糕的解决方案是破坏封装
- Automagic testing framework for VBA
- VBA的自动测试框架
- How to get the procedure or function name at runtime
- 如何在运行时获得过程或函数名?
- Import Lines of Code
- 导入的代码行
- Meta Programming in VBA: The VBIDE and Why Documentation is Important
- VBA中的元编程:vbe和文档为什么重要
The code from some of my Qs & As:
我的一些q & As代码:
- vbeCodeModule
- vbeCodeModule
- vbeProcedure
- vbeProcedure
- vbeProcedures
- vbeProcedures
#1
11
Yes, but I don't recommend it. VBA isn't really built for it. You've tagged this question with Excel, so I will describe how it is done for that Office Product. The general concept applies to most of the Office Suite, but each different product has a different syntax for the Application.Run method.
是的，但我不推荐。VBA并不是为它而建的。您已经用Excel标记了这个问题，因此我将描述如何为该办公产品做这些工作。一般概念适用于大多数的办公套件，但是每个不同的产品对应用程序有不同的语法。运行方法。
First, it's important to understand the two different methods of dynamically calling a procedure (sub/function) and when to use each.
首先，理解动态调用过程(sub/function)的两种不同方法以及何时使用它们是很重要的。
Application.Run
Application.Run will either run a subroutine or call a function that is stored in a standard *.bas module.
应用程序。Run将运行子例程或调用存储在标准*中的函数。bas模块。
The first parameter is the name of the procedure (passed in as a string). After that, you can pass up to 30 arguments. (If your procedure requires more than that, refactor for the love of code.)
第一个参数是过程的名称(作为字符串传入)。之后，您可以传递最多30个参数。(如果您的过程需要的不止这些，那么重构代码吧。)
There are two other important things to note about Application.Run.
关于Application.Run，还有另外两件重要的事情要注意。
- You cannot use named arguments. Args must be passed by position.
- 不能使用命名参数。Args必须通过位置。
-
Objects passed as arguments are converted to values. This means you could experience unexpected issues if you try to run a procedure that requires objects that have default properties as arguments.
作为参数传递的对象被转换为值。这意味着，如果您试图运行一个需要具有默认属性的对象作为参数的过程，您可能会遇到意想不到的问题。
Public Sub Test1() Application.Run "VBAProject.Module1.SomeFunction" End Sub
The takeaway:
Use Application.Run when you're working with a standard module.
使用应用程序。在使用标准模块时运行。
VBA.Interaction.CallByName
CallByName executes a method of an object, or sets/gets a property of an object.
CallByName执行对象的方法，或设置/获取对象的属性。
It takes in the instance of the object you want to call the method on as an argument, as well as the method name (again as a string).
它接受作为参数调用方法的对象的实例，以及方法名(同样作为字符串)。
Public Sub Test2()
Dim anObj As SomeObject
Dim result As Boolean
result = CallByName(anObj, "IsValid")
End Sub
The takeaway:
Use CallByName when you want to call a method of a class.
当您希望调用类的方法时，请使用CallByName。
No pointers.
As you can see, neither of these methods use actual pointers (at least not externally). They take in strings that they then use to find the pointer to the procedure that you want to execute. So, you'll need to know the exact name of the procedure you want to execute. You'll also need to know which method you need to use. CallByName having the extra burden of requiring an instance of the object you want to invoke. Either way, you can stores these names as strings inside of an array or collection. (Heck, even a dictionary could make sense.)
如您所见，这两种方法都不使用实际指针(至少不是在外部)。它们接收字符串，然后使用这些字符串找到要执行的过程的指针。因此，您需要知道要执行的过程的确切名称。您还需要知道需要使用哪种方法。CallByName具有要求要调用的对象的实例的额外负担。无论哪种方式，都可以将这些名称作为字符串存储在数组或集合中。(见鬼，即便是一本字典也有意义。)
So, you can either hard code these as strings, or attempt to extract the appropriate procedure names at runtime. In order to extract the procedure names, you'll need to interface with the VBIDE itself via the Microsoft Visual Basic for Applications Extensibility library. Explaining all of that here would require far too much code and effort, but I can point you to some good resources.
因此，您可以将它们硬编码为字符串，或者尝试在运行时提取适当的过程名称。为了提取过程名称，您需要通过Microsoft Visual Basic与VBIDE本身进行接口，以实现应用程序可扩展性库。在这里解释所有这些将需要太多的代码和精力，但是我可以为您指出一些好的资源。
Articles & SE Questions:
文章和SE的问题:
- Chip Pearson's Programming The VBA Editor
- Chip Pearson的VBA编辑器编程
- Extending the VBA Extensibility Library
- 扩展VBA扩展库
- Ugly workaround to get the vbext_ProcKind is breaking encapsulation
- 要获得vbext_ProcKind，最糟糕的解决方案是破坏封装
- Automagic testing framework for VBA
- VBA的自动测试框架
- How to get the procedure or function name at runtime
- 如何在运行时获得过程或函数名?
- Import Lines of Code
- 导入的代码行
- Meta Programming in VBA: The VBIDE and Why Documentation is Important
- VBA中的元编程:vbe和文档为什么重要
The code from some of my Qs & As:
我的一些q & As代码:
- vbeCodeModule
- vbeCodeModule
- vbeProcedure
- vbeProcedure
- vbeProcedures
- vbeProcedures