Are nested functions possible in VBA?

Question

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.

Assessor

no there are no nested functions in VBA, but if you want to make it more streamlined then declare the function private then only that module can see it. And why can't you declare it outside with parameters of L, U and x. Pass in L and U from mainActionHappensHere and it is equivalent. – Tambourine

It is logically equivalent, but it is not cleaner. I'll have complicatedFunction(x,L,U) written all over the place, and I want the code to be easier to understand by having cleaner(x) written all over the place. PS If I declare cleaner() outside of mainActionHappensHere(), mahh() will still be able to "see" cleaner(), but when I call complicatedFunction() from within cleaner(), it will have no idea what "L" and "U" are referring to, since they are out of scope! – Assessor

if you really want this then declare L and U as module level rather than method level. But I still think this is an issue created from nothing - calling mySub x, y, z compared to calling mySub x that nevertheless functionally implies mySub x, y, z in implementation is hardly 'cleaning' anything in my opinion. – Tambourine

Answer 1

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

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.

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

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

Defection

Not only VB.NET, but also VB.NET >= 2010 where multiline lambdas first appeared. – Waxler

True, though this is simple enough to reduce to a single-line lambda if needed. – Defection

Dang, I wish I was using VB.net right now... these lambda expressions would be my solution! And for the record, cleaner(x) does have a return value (it needs to be a Function, not a Sub), I omitted that above. – Assessor

Answer 2

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

Limiting the scope to VBA, your options would be:

• 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 L and U as fields or properties. Instantiate the class, set L and U once, then call function Cleaner, also defined in that class, that calls complicatedFunction with the stored L and U.

  Obviously this creates some overhead.

Waxler

i suppose a final option, depending on his set up, is to declare L and U module level, and have cleaner declared as a normal function that passes in the module level L and U to the horrendously complicatedFunction. 'Twould be ugly though ;) – Tambourine

vb.net can nest functions, as I demonstrate below. – Defection

@Tambourine With L and U at the module level the top function becomes non-reentrant-safe. – Waxler

@JoelCoehoorn These are not nested functions, these are lambdas. You declare a local variable in the function and assign an object of type 'lambda' to it. You still have no nested functions. – Waxler

It amounts to the same thing for this purpose. – Defection

I had considered the class-based approach, but like you said, too much overhead for something that should be trivial. And @Cor_Blimey: you're right, putting L and U in global(er) scope would be ugly. And considering multiple instances of complicatedFunction can potentially be executed at the same time, I'm afraid of bugs related to a "race condition" (as GSerg might be pointing out). – Assessor

Answer 3

You can use this syntax

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

Loisloise