Passing an empty/optional argument to a VBA function

Question

I was under the impression that if a function has been defined with some Optional arguments then you can pass Nothing and it will be treated in the same way as "not passing anything at all".

However, I can't make that work. Here's a code snippet that runs perfectly well if all the "fds..." variables are not empty but throws "Invalid procedure call or argument" if any arguments are Nothing:

Dim fdsSortOrders(2) As Variant
Dim fdsKey1 As Variant
Dim fdsKey2 As Variant
Dim fdsKey3 As Variant

' ...skipped...

Call loFinalReport.DataBodyRange.Sort( _
    Header:=xlYes, _
    Key1:=fdsKey1, Order1:=fdsSortOrders(0), _
    Key2:=fdsKey2, Order2:=fdsSortOrders(1), _
    Key3:=fdsKey3, Order3:=fdsSortOrders(2) _
)

Is there a way to pass an Optional argument or shall I make ugly copy-paste code ("if fdskey2 is nothing and fdskey3 is Nothing Then SortUsingKey1Only)?

UPDATE

This will work since Key2/Key3 and Order2/Order3 are Optional, but I don't want to write three versions of procedure call:

Call loFinalReport.DataBodyRange.Sort( _
    Header:=xlYes, _
    Key1:=fdsKey1, Order1:=fdsSortOrders(0) _
)

Answer 1

Here is an example of function header with optional parameters:

Function FunctionName(Optional ByVal X As String = "", Optional ByVal Y As Boolean = True)

You need to ommit parameter passing so default value will kick in.
In your example your default values will be of fdsKey1 and fdsSortOrders(0), so it will sort it by the same key and by the same order.

---

Could not came up with anything else but this:

Call loFinalReport.DataBodyRange.Sort( _
    Header:=xlYes, _
    Key1:=fdsKey1, Order1:=fdsSortOrders(0), _
    Key2:=IIf(IsNothing(fdsKey2), fdsKey1, fdsKey2), _
    Order2:=IIf(IsNothing(fdsSortOrders(1)), fdsSortOrders(0), fdsSortOrders(1)), _
    Key3:=IIf(IsNothing(fdsKey3), fdsKey1, fdsKey3), _
    Order3:=IIf(IsNothing(fdsSortOrders(2)), fdsSortOrders(0), fdsSortOrders(2)) _
)

Note that at least fdsKey1 and fdsSortOrders(0) should.. well, something.
Alternatively you can make this condition for all 3 Keys and Orders and make default values.
Sorting range by multiple keys with the same value should not affect sorting in any way.