Today’s VBA Tip of the Day concerns dynamic parameter declaration via the VBA ParamArray. When declaring Functions/Procedures in some cases you may need to specify Optional arguments. Let’s say we want to write a procedure that works similarly as other procedures you know natively from Excel like SUM:
Now how would you create such a Function in VBA? Well certainly NOT like THIS!!!:
Optional arguments are nice to use, only when you know there is going to be only a few. But in the above example we may as well get 20 different Excel Ranges!
If only the guy writing this knew about ParamArrays…Use the VBA ParamArray
In the above example, when dealing with a large amount of optional parameters use the VBA ParamArray statement in your Function/Procedure declaration:
1 2 3 4 5 | Public Function MySUM(ParamArray args()) For Each arg In args MySUM = MySUM + arg Next argEnd Function |
Notice that I replaced Optional arg1, Optional arg2, Optional arg3, Optional arg4, Optional arg5 with ParamArray args(). Cool huh? Well that’s basically it! Nothing hard there!
1 | ParamArray args() as String |
Also the ParamArray statement can’t be used with the following variable statements:
- ByVal
- ByRef
- Optional
That’s it for today’s VBA Tip of the Day!
Next steps
Be sure to read my other posts on:
VBA Array Tutorial
VBA Collection Tutorial
VBA Dictionary and other data structures
Thank you very much for the articles, very instructive