Validate that ParamArray contains the right types of parameters

Question

I'm trying to use ParamArray to pass different kinds of parameters to the same function to mimic Function Overloading.

• One single String
• One Workbook object, one String as well as an Integer

So user may pass different arguments to the same function:

Function func(ParamArray params() As Variant)

blah = func("This is a string")

blah = func(wbSource, "SheetName", iRow)

Is there a way to validate the types of arguments? I need to make sure that params() contains the right types of arguments (one workbook, one string and one integer).

For sure I can hard code with a bunch of If-Else, but what if in the future there are more arguments? I'm thinking about using TypeName() to dump the types to a String array and then do the comparison. But this still seems to be cumbersome. Is there a better way to achieve this?

BTW I don't think Optional is a good idea because who knows how many arguments there will be?

Answer 1

I'm trying to use ParamArray to pass different kinds of parameters to the same function to mimic Function Overloading.

IMO that's a terrible idea: you lose the compile-time check on argument counts, lose intellisense at call sites telling you what the expected parameters & types are.

C# started off with method overloading, and evolved to support optional parameters. Now that optional parameters are supported, method overloading is, in C#, simply an alternative to optional parameters, and vice-versa.

VBA supports optional parameters, but not method overloading. Big deal: you can use optional parameters and achieve the exact same as C#'s alternative to method overloading.

ParamArray isn't an option.

The answer to "is there a way to validate the types of the arguments" is - YES! Let the compiler do its job!

I don't think Optional is a good idea because who knows how many arguments there will be?

If you don't, nobody knows! A method with too many arguments is often a code smell: is the method responsible for too many things? Does the name accurately convey everything it does? Hard to tell with what you've supplied, but I'd argue that if you're writing a method and you don't know how many arguments it is going to need, how do you know what that method needs to do?

Answer 2

I think you answered your own question in the question. You need to use an array to store which types are valid for you and check if the type is inside. You can to that using TypeName(), or using VarType(), as Mathieu Guindon explained in the comment section. Example:

Dim IntVar, StrVar, DateVar, MyCheck
' Initialize variables.
IntVar = 459: StrVar = "Hello World": DateVar = #2/12/69# 
MyCheck = VarType(IntVar)    ' Returns 2.
MyCheck = VarType(DateVar)    ' Returns 7.
MyCheck = VarType(StrVar)    ' Returns 8.