CODEWITHSUNDEEP
vba
Greedo
Greedo

Reputation: 5523

Considerations when using Function name as a variable

In VBA, you can treat a UDF name just like a Dimmed variable. For example

Function ADD(a, b) As Long
ADD = a
ADD = ADD + b
End Function

Where ADD stores the intermediate value as well as the end result. My question is; in terms of what's going on behind the scenes, is there any difference between storing a number/piece of data in a standard variable vs a function name variable.

I worry that perhaps the routine that called the function gets an update whenever the variable changes if you use the function name, rather than only when End Function is executed. IE. if you have some code

answer = ADD(1, 2)

then in memory answer gets written to twice, once when ADD = a, once when ADD = ADD + b. Of course we don't see this, because answer is left with whatever the final ADD value is

I ask because I often find I build up a function answer in stages using an intermediate variable, then pass that to the function name itself, where instead I could just write directly to the function name.

E.g.

Function ADD(a, b) As Long
Dim tot As Long
tot = a
tot = tot + b
ADD = tot
End Function

vs the first example. They acheive the same thing, in the second example tot represents the formula result, and so we need a final ADD = tot line at the end. For speed I would like to reduce any writes that are made, so is there any drawback in terms of speed, not readability to using the first method as opposed to declaring intermediates?

NB, to clarify, that's not all intermediates, I just mean the single intermediate that represents the function result, and could be replaced by the function name in the code.

Upvotes: 2

Views: 125

Answers (1)

Vityata
Vityata

Reputation: 43575

In speed the first method should be slightly faster - you declare one variable less (but I doubt that someone would be able to notice it).

In general, using the first method can bring you to a recursion, if you are not careful (or if you are a VBA beginner):

Option Explicit

Dim lng10 As Long

Public Sub Test()

    lng10 = 0
    Debug.Print ADD2(1, 1)

End Sub

Function ADD2(a As Long, b As Long) As Long

    lng10 = lng10 + 1
    ADD2 = a + b

    If lng10 < 10 Then
        ADD2 = ADD2 + ADD2(1, 1)
    End If

End Function

And if the recursion does not have a bottom, it would go to an overflow error. With other words, this would be an runtime error:

Option Explicit

Dim lng10 As Long

Public Sub Test()

    lng10 = 0
    Debug.Print ADD2(1, 1)

End Sub

Function ADD2(a As Long, b As Long) As Long

    lng10 = lng10 + 1
    ADD2 = ADD2(a, b)

End Function

Upvotes: 3

Related Questions