数组中的Excel VBA存储函数或子程序 [英] Excel VBA store functions or subroutines in an array

问题描述

在C / C ++中，当我有一堆函数（指针）时，我可以将它们存储在一个数组或一个向量中，并按照一定的顺序调用它们。可以在VBA中做类似的事情吗？

谢谢！

解决方案

是的，但我不推荐它。 VBA并没有真正为它构建。您已经用Excel标记了这个问题，所以我将介绍如何完成该Office产品。一般概念适用于大多数Office套件，但每个不同的产品对于 Application.Run 方法具有不同的语法。

首先，了解动态调用过程（子/函数）和何时使用各种方法的两种不同方法很重要。

Application.Run

Application.Run 将运行子例程或调用存储在一个标准的 *。bas 模块。

第一个参数是过程的名称（以字符串形式传入）。之后，您可以传递最多30个参数。 （如果你的程序需要更多，那么重构代码的爱好。）

还有另外两个重要的事情要注意 Application.Run 。

1. 您不能使用命名参数。必须通过位置传递精灵。



2. 作为参数传递的对象将转换为值。这意味着如果您尝试运行一个需要具有默认属性参数的对象的过程，您可能会遇到意外的问题。

     Public Sub Test1（）
    Application.RunVBAProject.Module1.SomeFunction
    End Sub 
     

外卖：

在使用标准模块时使用Application.Run。

VBA.Interaction.CallByName

CallByName 执行对象的方法，或设置/获取对象的属性。

它将您要调用该方法的对象的实例作为参数，以及方法名称（再次作为字符串）。

  Public Sub Test2（）
 Dim anObj As SomeObject 
 Dim result As Boolean 
 
 result = CallByName（anObj，IsValid）
 End Sub 
  

外卖：

当您想要调用类的方法时，请使用 CallByName 。

没有指针。

如您所见，这些方法都不使用实际的指针（至少不是外部的）。它们使用字符串，然后使用它们来查找要执行的过程的指针。因此，您需要知道要执行的过程的 确切 名称。您还需要知道您需要使用哪种方法。 CallByName 需要额外的负担，要求您要调用的对象的实例。无论哪种方式，您可以将这些名称作为字符串存储在数组或集合中。 （Heck，甚至一本字典都可以有意义。）

所以，你可以将这些代码硬编码为字符串，或尝试在运行时提取相应的过程名称。为了提取过程名称，您需要通过 Microsoft Visual Basic应用程序可扩展性 库。解释所有这一切将需要太多的代码和努力，但我可以指出一些好的资源。

文章& SE问题：

1. Chip Pearson编程VBA编辑器


2. 扩展VBA扩展库


3. 获取vbext_ProcKind的破坏性解决方案是破坏封装


4. VBA的自动化测试框架


5. 如何获取该程序或运行时的功能名称


6. 导入代码行


7. VBA中的元编程：VBIDE和为什么文档很重要

我的一些Q& As：

1. vbeCodeModule


2. vbeProcedure


3. vbeProcedures

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?

Thanks!

解决方案

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.

First, it's important to understand the two different methods of dynamically calling a procedure (sub/function) and when to use each.

Application.Run

Application.Run will either run a subroutine or call a function that is stored in a standard *.bas module.

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.)

There are two other important things to note about Application.Run.

1. You cannot use named arguments. Args must be passed by position.

2. 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.

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.

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.)

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.

Articles & SE Questions:

1. Chip Pearson's Programming The VBA Editor
2. Extending the VBA Extensibility Library
3. Ugly workaround to get the vbext_ProcKind is breaking encapsulation
4. Automagic testing framework for VBA
5. How to get the procedure or function name at runtime
6. Import Lines of Code
7. Meta Programming in VBA: The VBIDE and Why Documentation is Important

The code from some of my Qs & As:

1. vbeCodeModule
2. vbeProcedure
3. vbeProcedures

这篇关于数组中的Excel VBA存储函数或子程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！