Optional Parameters and ParamArray is not something used very often by most Excel developers I guess after seeing thousands of lines of VBA code…. I am not saying they are hard to understand or use.. you probably have used it before without even recognizing it if you have ever worked with any functions…. It’s good to know what they do and how to use them so you can take a full advantage while designing your own solutions. Personally, I find them both very useful and use them wherever its possible.
Function with no optional parameters
Most of the time functions have a fixed number of arguments they ask for. Simplest example Excel’s worksheet function =COUNTBLANK(range). It takes 1 parameter (range) and returns a number (Long) of empty cells in that specific range. Not much science about it ;)
There are a few rules that you should remember
- Optional parameters are always the last parameters in the list of parameters in a function’s definition
- Optional keyword is required to indicate that a parameter is optional
- Optional parameters can be of Variant datatype and other (Integer, Long, String, etc ) datatypes but Variants are handled differently from the primary data types ( explained below )
- Optional Parameters do not work with ParamArray ( explained below - with ParamArray )
- You cannot use UDT (User defined types) as Optional Parameters
Right, so a few rules laid out should give you an idea how to implement Optional Parameters in your own functions/subs.
Let’s create an example and analyse it
Optional Parameter of Variant Type + IsMissing() function
Stick the below code in a Module1 in your VBA project
Public Function OptionalVariant(Optional var As Variant) As String
If IsMissing(var) Then
OptionalVariant = "Parameter not provided"
OptionalVariant = "Parameter provided"
Now switch to a spreadsheet view and type below functions in two separate cells
The first call =OptionalVariant() returns “Parameter not provided” as expected. The function can be called with no parameter in this case because there is only one that the function takes and it’s optional.
The second call =OptionalVariant(foo) returns “Parameter provided” – also as expected because the function was called with an argument ( foo ).
VARIANT and IsMissing() explanation
As you have noticed in the above example `IsMissing()` is used on a Variant type. This is because Variant can be Missing. All other data types like Integer, Long, String all have default values ( 0 for Integer and Long, “” for String, False for Boolean, etc ). Variant, however, doesn’t have a default return value. This means that when you are not passing it to a function (in place of the Variant argument) you can’t do nothing with that parameter inside the body of a function/sub. If you attempt to use the Variant variable that has not been passed to a function you get a Error 448 (“Named argument not found”).
So, IsMissing() is a special function that only works with Variant data type. When you declare you function like Public Function(Optional i as Long) the default value for i will be 0, no matter if the argument has been passed to the function or not. If you do this (please don’t in real life – it’s just a failing demo)
Public Function VarIsMissing(Optional var As Long)
If IsMissing(var) Then Debug.Print var
The IsMissing(var) returns a False even though logically and practically the argument has been passed ( default is 0 ). So please remember that IsMissing() only works with Variant data type.
Now, let’s create a function which requires 1 parameter and allows you to provide the second one as optional:
Public Function CalculateNet(income As Double,Optional tax As Double=20)
CalculateNet = income - (income * tax / 100)
call it from spreadsheet like
The first call =CalculateNet(100) returns 80. The function is called using only one parameter – the income, and the tax is 20 (Optional Parameter’s default value).
The second call =CalculateNet(100, 40) returns 60 because you are overriding the default 20% with a custom 40%.
This is quite straight forward, I am sure you can think of many many examples to make yours or someone else’s life easier by designing your functions using Optional Parameters - specially in financial calculations.
Just to demonstrate you can build a function that takes more than 1 optional parameter… let’s see if this is going the right way to go about this problem
Let’s say you need a function to combine strings from cells spread out horizontally. Some cells in between may be empty… Maybe it’s easier to visualize this
I think you could/would just go with =A1&””&B1&””&C1 - trivial but let’s use it as an example for our function that user Optional Parameters
Public Function CombineStrings(separator$, Optional str1$, Optional str2$, Optional str3$)
CombineStrings = IIf(Not (str1 = vbNullString), str1 + separator, vbNullString) + _
IIf(Not (str2 = vbNullString), str2 + separator, vbNullString) + _
IIf(Not (str3 = vbNullString), str3 + separator, vbNullString)
EASY!? Ain’t it?
The CombineStrings adds an extra functionality using IIF() to handle empty cells not adding an extra space. It’s quite easy to understand if you simply analyse one line at a time IIf(Not (str1 = vbNullString), str1 + separator, vbNullString)
NOTE: & vs +
This is not an error. In VBA when concatenating string you can use either + or &. Note that + works only between all String data types. INT/LONG/DOUBLE + STRING produces a type-mismatch error.
Well, in this example you can only pass max of. 4 parameters to your function, what would happen if you needed like 1337 of those? What if they were mixed numbers, strings, booleans, etc? You would need to know which cell to pass in which place on the list of parameters ( if you werent using a Variant for the parameter type – let’s assume you were not ). What if you didn’t know until the run-time how many arguments you are actually going to need to pass to your function?
Aha! this is where a ParamArray comes in really handy!
A few rules abour ParamArrays:
- ParamArray must be an array of Variant type
- ParamArray must be the last parameter in the list of parameters in your function definition
- You cant use Optional Paramters along with ParamArray – ParamArray is Optional itself!
- ParamArray index starts at 0, if ParamArray is empty the upper bound is -1.
- You cant user UDFs as a parameter for a ParamArray
Let’s see an example —
On your spreadsheet, in any cell type =SUM( and hold it for a while… look
list of numbers… What does this mean? You can pass almost any number of parameters, there is no fixed length.. is it a ParamArray? I don’t know 100% ask Spolsky hehe
The whole point is that when you don’t know the amount of parameters you may need to use for a function at run-time the ParamArray is the way to go.
let’s modify the previous example just a bit so it allows you to concatenate any amount of words using a ParamArray.
Public Function CombineStrings(separator$, ParamArray words() As Variant) As String
' check if your words ParamArray is not empty
If UBound(words) > 0 Then
Dim word As Variant
For Each word In words
combinedStr = combinedStr & IIf(word <> " ", word + separator, vbNullString)
CombineStrings = combinedStr
CombineStrings = vbNullString
CombineStrings = combinedStr
So now you can go back to your spreadsheet and do this
And for the final exercise copy-paste the below sub and run ;)
Application.ActiveWindow.Zoom = 85
Range("A1:Z1").ColumnWidth = 3
Call PaintCells("A1", "B2", "B3", "C4", "C5", "D6", "D7", "E5", "E4", "F3", "F2", "G1", "I1", "I2", "I3", "I4", "I5", "I6", "I7", "J1", "K1", "L2", "K3", "J4", "K4", "L4", "M5", "M6", "J7", "K7", "L7", "O7", "O6", "P5", "P4", "Q3", "Q2", "R1", "Q5", "R5", "S5", "S2", "S3", "T4", "T5", "U6", "U7")
Private Sub PaintCells(ParamArray cellToPaint() As Variant)
Dim cell As Variant
For Each cell In cellToPaint
Range(cell).Interior.Color = RGB(100, 160, 255)