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!

Interestingly, this totally non-sense approach is used by Microsoft Excel WorksheetFunctions. See for yourself:
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:

Public Function MySUM(ParamArray args())
    For Each arg In args
        MySUM = MySUM + arg
    Next arg
End 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!

ParamArray args() as String

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