CODEWITHSUNDEEP
functionvbaintrospectionoptional-parameters
James Eichele
James Eichele

Reputation: 119164

Is there a way to determine which optional arguments were given in a VBA function call?

Say I have a VBA function with an optional argument. Is there a way to tell, from within that function, whether the calling code has supplied the optional argument or not?

Public Function SomeFunction(optional argument as Integer = 0) As Integer

End Function

i.e. Is there a way to tell the difference between the following calls?

x = SomeFunction()
x = SomeFunction(0)

Upvotes: 1

Views: 384

Answers (2)

Tipx
Tipx

Reputation: 7515

No, there is not.

The same problem exists in other language, such as C#, as expressed clearly in "C# In Depth", Chapter 13.1.1 (I read this part 15mins ago, it's normal I remember this!)

What I suggest you is not to set the default value in the function declaration. In the function, if argument is null, you set it to 0 and you know it wasn't supplied in the function call.

Edit : Just like @Remnant said in his answer, to be able to do this, the parameter type needs to be a variant.

Upvotes: 0

Alex P
Alex P

Reputation: 12497

As far as I am aware it is not possible. If no argument is passed then the argument is initiliazed to its default value (0 in this case).

One way around this is to change the variable type to Variant and use the IsMissing function to check whether an argument is passed or not. Example:

Public Function SomeFunction(Optional argument As Variant) As Integer
    If IsMissing(argument) Then
        argument = 0
    Else
        // Code if argument not = 0....
    End If
End Function

The IsMissing function works only with the Variant data type as any other data type will always have a default initialization value.

Upvotes: 4

Related Questions