VBA ParamArray

The VBA ParamArray for a dynamic list of VBA arguments

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:
Excel SUM
Now how would you create such a Function in VBA? Well certainly NOT like THIS!!!:
SUM Excel example
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:
WorksheetFunctions.Sum declaration 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!

Remember! The ParamArray statement works only with Variant variables. So don’t try the following

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

1 Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.