在VBA中可以嵌套函数吗?

Question

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

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

这可能吗?编译器抱怨，"期望结束函数"，因为我在结束外部函数之前开始了一个函数。和谷歌是没有帮助:(PS我不能定义清洁器()mainActionHappensHere()之外，从那时起，正确的L和U不会被传递到它。

Answer 1

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

两个可能帮助您以其他方式简化代码的特性是重载函数或可选参数。下面是使用可选参数的示例:

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

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

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

Answer 2

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

将范围限制为VBA，您的选项将是:

• 使用GoSub，这是VB中最老的命令之一，已被弃用，不受欢迎，并且在VB中没有相应的升级。净:

  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。

Answer 3

您可以使用以下语法

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