Menu
I'm using Excel VBA to a write a UDF. I would like to overload my own UDF with a couple of different versions so that different arguments will call different functions.
As VBA doesn't seem to support this, could anyone suggest a good, non-messy way of achieving the same goal? Should I be using Optional arguments or is there a better way?
582
Excel VBA User Defined Function (UDF) Microsoft serves us with many built-in functions to speed up the work in excel. However using VBA coding we can create our own functions and those functions are technically called “User-Defined Functions” (UDF). They are also called as “custom functions” in excel VBA.
Unfortunately, VBA doesn't allow multiple functions of the same name with different argument types.
Function Overloading in C++When a function name is overloaded with different jobs it is called Function Overloading. In Function Overloading “Function” name should be the same and the arguments should be different. Function overloading can be considered as an example of a polymorphism feature in C++.
There are mainly two types of overloading, i.e. function overloading and operator overloading. Function overloading improves the code readability, thus keeping the same name for the same action.
Declare your arguments as Optional Variants, then you can test to see if they're missing using IsMissing() or check their type using TypeName(), as shown in the following example:
Public Function Foo(Optional v As Variant) As Variant If IsMissing(v) Then Foo = "Missing argument" ElseIf TypeName(v) = "String" Then Foo = v & " plus one" Else Foo = v + 1 End If End Function This can be called from a worksheet as =FOO(), =FOO(number), or =FOO("string").
184
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
