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.

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. — Cor_Blimey
– Cor_Blimey, Commented May 12, 2014 at 17:54
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! — TheBigAmbiguous
– TheBigAmbiguous, Commented May 12, 2014 at 18:01
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. — Cor_Blimey
– Cor_Blimey, Commented May 12, 2014 at 18:30

Joel Coehoorn · Accepted Answer · 2014-05-12 20:05:31Z

7

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

edited May 12, 2014 at 20:05

answered May 12, 2014 at 18:18

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

GSerg Over a year ago

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

Joel Coehoorn Over a year ago

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

TheBigAmbiguous Over a year ago

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.

GSerg · Accepted Answer · 2014-05-12 18:30:35Z

5

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.

answered May 12, 2014 at 18:30

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

6 Comments

Cor_Blimey Over a year ago

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

Joel Coehoorn Over a year ago

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

GSerg Over a year ago

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

GSerg Over a year ago

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

Joel Coehoorn Over a year ago

It amounts to the same thing for this purpose.

|

chernipeski · Accepted Answer · 2025-02-05 00:24:24Z

Here in this example -- rather than having global Public functions to sanitize parameters -- I used a couple of small Private Sub's to do the job

Public Function stg( _
   Optional s, _
   Optional n)
err:
   If (err.Number > 0) Then
      Debug.Print MsgBox("Error: " & err.Number, , _
      "fxx ¦ Steg Text ¦ stg")
      End
   End If
   On Error GoTo err
ini:
   sas s, xs
   san n, 101, 199
   If (xs) = (0) Then GoTo fin
run:
   ' do things here
fin:
   stg = s
ver:
   ' OfficePape®.cc
   ' ©[email protected]
   ' 250204
End Function


Private Sub sas(s, xs)
   ' sanitize a string
   s = IIf(IsMissing(s), "", CStr(Nz(s, "")))
   xs = Len(s)
End Sub

Private Sub san(n, n0, n1)
   ' sanitize a number
   n = IIf(IsMissing(n), 0, Nz(n))
   n = IIf(n < n0, n0, n)
   n = IIf(n > n1, n1, n)
End Sub

Vicenzo Martinelli · Accepted Answer · 2019-06-14 12:30:34Z

-1

You can use this syntax

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

answered Jun 14, 2019 at 12:30

Vicenzo Martinelli

293 bronze badges

Collectives™ on Stack Overflow

Are nested functions possible in VBA?

4 Answers 4

3 Comments

6 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related