2

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?

Nicholas Humphrey
  • 1,220
  • 1
  • 16
  • 33

2 Answers2

3

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?

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • Thanks for the answer. Makes sense, seems I'm fancying myself to use "advanced" feature which is very easy to implement in C++ but kind of weird in VBA. Since for now I only have two cases, I'll split them into two functions. – Nicholas Humphrey Jun 14 '18 at 20:49
  • 1
    @NicholasHumphrey depending on specifically what's going on, a more "advanced" OOP design could implement a *strategy pattern* and receive some `IAction` parameter (whatever) that the call site could be responsible for supplying - IDK, design is pretty vague here. Just know that pretty much everything you can think of in terms of OOP, is achievable in VBA - as long as you don't try to involve delegates and other exotic constructs. – Mathieu Guindon Jun 14 '18 at 20:52
  • Thanks Mathieu! Can you elaborate a bit about the Strategic Pattern? From what I read online, it involves creating an Interface, say `IAction`, and `Implements IAction` for the real actions. So the users are supposed to provide these objects and proper function calls will be made. But as you said, this is very vague. I had some experience with C++ so I know Polymorphism, but only vaguely. Is this the same thing you are talking about? Thanks! – Nicholas Humphrey Jun 14 '18 at 21:12
  • 1
    Yes, polymorphism is precisely it =) – Mathieu Guindon Jun 14 '18 at 21:18
  • Thanks I'll see if it fits in. For now, TBH I only have two different cases, guess it's too much, – Nicholas Humphrey Jun 14 '18 at 21:20
1

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.
Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • Thanks, I guess that's one of the only two ways (and the `optional` is the other) to implement this. Very cumbersome, I'll ditch the design. – Nicholas Humphrey Jun 14 '18 at 20:50