VBA 中是否可以使用嵌套函数？

Are nested functions possible in VBA?

提问by TheBigAmbiguous

I'm trying to clean up code by stripping parameters from a function within a private scope, like this:

我试图通过从私有范围内的函数中剥离参数来清理代码，如下所示：

Function complicatedFunction(x as Double, param1 as Double, param2 as Double)
    ...
End Function

Function mainActionHappensHere(L as Double, U as Double ...)
    Function cleaner(x)
        cleaner = complicatedFunction(x, L, U)
    End Function
    ...
    cleaner(x)                       'Many calls to this function
    ...
End Function

Is this possible? Compiler complains, "Expected End Function", since I'm beginning a function before ending the outer one. And Google is no help :( PS I can't define cleaner() outside of mainActionHappensHere(), since then the correct L and U won't get passed into it.

这可能吗？编译器抱怨“预期的结束函数”，因为我在结束外部函数之前开始了一个函数。谷歌也没有帮助:( PS 我不能在 mainActionHappensHere() 之外定义cleaner()，因为那样正确的L和U就不会被传递进去。

回答by Joel Coehoorn

VB.Net can do this, but I don't believe VBA can.

VB.Net 可以做到这一点，但我不相信 VBA 可以。

Two features that might help you simplify this code in other ways are overloaded functions or optional parameters. Here's an example using optional parameters:

可以帮助您以其他方式简化此代码的两个功能是重载函数或可选参数。这是使用可选参数的示例：

Function complicatedFunction(x as Double, Optional param1 as Double = L, Optional param2 as Double = U) As Object
...
End Function

complicatedFunction(x)

However, L and U must be constants for this to work.

但是，L 和 U 必须是常数才能使其起作用。

FWIW, and in case it turns out that you're really working with a VB.Net dialect, the VB.Net syntax looks like this:

FWIW，如果事实证明您确实在使用 VB.Net 方言，则 VB.Net 语法如下所示：

Sub complicatedFunction(x as Double, param1 as Double, param2 as Double) 
    ...
End Sub

Function mainActionHappensHere(L as Double, U as Double ...)
    Dim cleaner As Func(Of Double, Object) = 
        Function(x) 
            Return complicatedFunction(x, L, U)
        End Function

    Dim y = cleaner(x)                       'Many calls to this function
    ...
End Function

回答by GSerg

There are no nested functions in VB, either VBA or VB6 or VB.NET.

VB 中没有嵌套函数，无论是 VBA 还是 VB6 或 VB.NET。

Limiting the scope to VBA, your options would be:

将范围限制为 VBA，您的选择是：

• Use GoSub, one of the oldest VB command, that is deprecated, frowned upon and has no upgrade equivalent in VB.NET:

  Function mainActionHappensHere(L as Double, U as Double ...)
      Dim ResultOfCleaner As Variant
      ...
      x = 5 : GoSub cleaner                       'Many calls to this function
      'Use ResultOfCleaner here
      ...
      x = 42 : GoSub cleaner                      'Many calls to this function
      'Use ResultOfCleaner here
      ...
      Exit Function
  
  cleaner:
      ResultOfCleaner = complicatedFunction(x, L, U)
      Return
  End Function
  

• Manually create a closure.

  Define a class that exposes Land Uas fields or properties. Instantiate the class, set Land Uonce, then call function Cleaner, also defined in that class, that calls complicatedFunctionwith the stored Land U.

  Obviously this creates some overhead.

• 使用GoSub，最古老的 VB 命令之一，在 VB.NET 中已被弃用、不赞成并且没有升级等效项：

  Function mainActionHappensHere(L as Double, U as Double ...)
      Dim ResultOfCleaner As Variant
      ...
      x = 5 : GoSub cleaner                       'Many calls to this function
      'Use ResultOfCleaner here
      ...
      x = 42 : GoSub cleaner                      'Many calls to this function
      'Use ResultOfCleaner here
      ...
      Exit Function
  
  cleaner:
      ResultOfCleaner = complicatedFunction(x, L, U)
      Return
  End Function
  

• 手动创建一个闭包。

  定义一个将L和U作为字段或属性公开的类。实例化类，设置L和U一次，然后调用Cleaner同样在该类中定义的函数，该函数complicatedFunction使用存储的L和 进行调用U。

  显然，这会产生一些开销。

回答by Vicenzo Martinelli

You can use this syntax

您可以使用此语法

Dim fSomeFunction As Func(Of String, Boolean) = Function(ByVal something As String) As Boolean
                                                                     Return True
                                                                 End Function