VBA 中的自定义回调

Custom callbacks in VBA

提问by Joshua Honig

Note the tag: VBA, not VB6, not VB.NET.

注意标签：VBA，不是 VB6，不是 VB.NET。

This is specific to VBA in MS Access. I've built a collection of methods in an module I call "Enumerable". It does a lot of things reminiscent of the Enumerable classes and interfaces in .NET. One thing I want to implement is a ForEach method, analagous to the .NET Enumerable.Select method.

这特定于 MS Access 中的 VBA。我在一个名为“Enumerable”的模块中构建了一组方法。它做了很多让人想起 .NET 中的 Enumerable 类和接口的事情。我想要实现的一件事是 ForEach 方法，类似于 .NET Enumerable.Select 方法。

I built a version that uses the Application.Runmethod to call a function for each element, but Application.Run only works with user-defined methods. For example, the following works:

我构建了一个版本，它使用Application.Run方法为每个元素调用一个函数，但 Application.Run 仅适用于用户定义的方法。例如，以下工作：

' User-defined wrapper function:
Public Function MyReplace( _
    Expression As String, Find As String, StrReplace As String, _
    Optional Start As Long = 1, _
    Optional Count As Long = 1, _
    Optional Compare As VbCompareMethod = vbBinaryCompare)
    MyReplace = Replace(Expression, Find, StrReplace, Start, Count, Compare)
End Function

' Using Application.Run to call a method by name
Public Sub RunTest()
    Debug.Print Run("MyReplace", "Input", "In", "Out")
End Sub

RunTest prints "Output", as expected. The following does NOT work:

RunTest 按预期打印“输出”。以下不起作用：

Debug.Print Run("Replace", "Input", "In", "Out")

It throws run-time error 430: "Class does not support Automation or does not support expected interface". This is expected, because the documentation states that Application.Run only works for user-defined methods.

它抛出运行时错误 430：“类不支持自动化或不支持预期的接口”。这是意料之中的，因为文档指出 Application.Run 仅适用于用户定义的方法。

VBA does have an AddressOf operator, but that only works when passing function pointers to external API functions; function pointers created using AddressOf are not consumable in VBA. Again, this is noted in the documentation (or see for example VBA - CallBacks = Few Cents Less Than A Dollar?).

VBA 确实有一个 AddressOf 操作符，但只有在将函数指针传递给外部 API 函数时才有效；使用 AddressOf 创建的函数指针在 VBA 中不可使用。同样，这在文档中有所说明（或参见例如VBA - CallBacks =几分钱少于一美元？）。

So is there any other way to identify and call a method using a variable? Or will my callback-ish attempts be limited to user-defined functions via the Application.Run method?

那么有没有其他方法可以使用变量来识别和调用方法？或者我的回调尝试是否仅限于通过 Application.Run 方法的用户定义函数？

采纳答案by Joshua Honig

No other answers in a week...for resolution's sake here's the best I could come up with:

一周内没有其他答案......为了解决问题，这是我能想到的最好的答案：

1. I built a helper module that resolves a ParamArray to individual arguments for the sake of calling CallByName. If you pass a ParamArray through to CallByNameit will mash all the arguments into a single, actual Arrayand pass that to the first argument in the method you attempt to invoke.
2. I built two ForEachmethods: one that invokes Application.Run, and another that invokes CallByName. As noted in the question, Application.Runonly works for user-defined global (public module) methods. In turn, CallByNameonly works on instance methods, and requires an object argument.

1. 我构建了一个辅助模块，为了调用CallByName. 如果您将 ParamArray 传递给CallByName它，它将把所有参数混搭为一个单一的、实际的，Array并将其传递给您尝试调用的方法中的第一个参数。
2. 我构建了两种ForEach方法：一种调用Application.Run，另一种调用CallByName. 如问题中所述，Application.Run仅适用于用户定义的全局（公共模块）方法。反过来，CallByName只适用于实例方法，并且需要一个对象参数。

That still leaves me without a way to directly invoke built-in global methods (such as Trim()) by name. My workaround for that is to build user-defined wrapper methods that just call the built-in global method, for example:

这仍然让我无法Trim()通过名称直接调用内置的全局方法（例如）。我的解决方法是构建只调用内置全局方法的用户定义的包装方法，例如：

Public Function FLeft( _
   str As String, _
   Length As Long) As String
    FLeft = Left(str, Length)
End Function

Public Function FLTrim( _
   str As String) As String
    FLTrim = LTrim(str)
End Function

Public Function FRight( _
   str As String, _
   Length As Long) As String
    FRight = Right(str, Length)
End Function

...etc...

I can now use these to do things like:

我现在可以使用这些来做以下事情：

' Trim all the strings in an array of strings
trimmedArray = ForEachRun(rawArray, "FTrim")

' Use RegExp to replace stuff in all the elements of an array
' --> Remove periods that aren't between numbers
Dim rx As New RegExp
rx.Pattern = "(^|\D)\.(\D|$)"
rx.Global = True
resultArray = ForEachCallByName(inputArray, rx, "Replace", VbMethod, " ")