<p>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. </p>

<p>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?</p>

[{'answer_id': 64494, 'author': 'theo', 'author_id': 7870, 'author_profile': 'https://Stackoverflow.com/users/7870', 'pm_score': 0, 'selected': False, 'text': "<p>VBA is messy. I'm not sure there is an easy way to do fake overloads:</p>\n\n<p>In the past I've either used lots of Optionals, or used varied functions. For instance </p>\n\n<pre><code>Foo_DescriptiveName1()\n\nFoo_DescriptiveName2()\n</code></pre>\n\n<p>I'd say go with Optional arguments that have sensible defaults unless the argument list is going to get stupid, then create separate functions to call for your cases.</p>\n"}, {'answer_id': 65023, 'author': 'Jon Fournier', 'author_id': 5106, 'author_profile': 'https://Stackoverflow.com/users/5106', 'pm_score': 0, 'selected': False, 'text': "<p>You mighta also want to consider using a variant data type for your arguments list and then figure out what's what type using the TypeOf statement, and then call the appropriate functions when you figure out what's what...</p>\n"}, {'answer_id': 70526, 'author': 'Joel Spolsky', 'author_id': 4, 'author_profile': 'https://Stackoverflow.com/users/4', 'pm_score': 7, 'selected': True, 'text': '<p>Declare your arguments as <code>Optional Variants</code>, then you can test to see if they\'re missing using <code>IsMissing()</code> or check their type using <code>TypeName()</code>, as shown in the following example:</p>\n\n<pre><code>Public Function Foo(Optional v As Variant) As Variant\n\n    If IsMissing(v) Then\n        Foo = "Missing argument"\n    ElseIf TypeName(v) = "String" Then\n        Foo = v &amp; " plus one"\n    Else\n        Foo = v + 1\n    End If\n\nEnd Function\n</code></pre>\n\n<p>This can be called from a worksheet as <strong>=FOO()</strong>, <strong>=FOO(<em>number</em>)</strong>, or <strong>=FOO("<em>string</em>")</strong>.</p>\n'}, {'answer_id': 71162, 'author': 'Mike Woodhouse', 'author_id': 1060, 'author_profile': 'https://Stackoverflow.com/users/1060', 'pm_score': 3, 'selected': False, 'text': '<p>If you can distinguish by parameter count, then something like this would work:</p>\n\n<pre><code>Public Function Morph(ParamArray Args())\n\n    Select Case UBound(Args)\n    Case -1 \'\' nothing supplied\n        Morph = Morph_NoParams()\n    Case 0\n        Morph = Morph_One_Param(Args(0))\n    Case 1\n        Morph = Two_Param_Morph(Args(0), Args(1))\n    Case Else\n        Morph = CVErr(xlErrRef)\n    End Select\n\nEnd Function\n\nPrivate Function Morph_NoParams()\n    Morph_NoParams = "I\'m parameterless"\nEnd Function\n\nPrivate Function Morph_One_Param(arg)\n    Morph_One_Param = "I has a parameter, it\'s " &amp; arg\nEnd Function\n\nPrivate Function Two_Param_Morph(arg0, arg1)\n    Two_Param_Morph = "I is in 2-params and they is " &amp; arg0 &amp; "," &amp; arg1\nEnd Function\n</code></pre>\n\n<p>If the only way to distinguish the function is by types, then you\'re effectively going to have to do what C++ and other languages with overridden functions do, which is to call by signature. I\'d suggest making the call look something like this:</p>\n\n<pre><code>Public Function MorphBySig(ParamArray args())\n\nDim sig As String\nDim idx As Long\nDim MorphInstance As MorphClass\n\n    For idx = LBound(args) To UBound(args)\n        sig = sig &amp; TypeName(args(idx))\n    Next\n\n    Set MorphInstance = New MorphClass\n\n    MorphBySig = CallByName(MorphInstance, "Morph_" &amp; sig, VbMethod, args)\n\nEnd Function\n</code></pre>\n\n<p>and creating a class with a number of methods that match the signatures you expect. You\'ll probably need some error-handling though, and be warned that the types that are recognizable are limited: dates are TypeName Double, for example.</p>\n'}]
['https://Stackoverflow.com/questions/64436', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/69157/']