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

Running a quick test with an Optional Variant, using the IsMissing function shows that Nothing is not the same as Missing:

Option Explicit

Private Function IsItMissing(Optional vTMP As Variant) As Variant
    IsItMissing = CVErr(xlErrNA)
    On Error GoTo FuncErr
    IsItMissing = IsMissing(vTMP)
FuncErr:
End Function

Public Sub TestValues()
    MsgBox IsItMissing("Nope") 'False
    MsgBox IsItMissing 'True
    MsgBox IsItMissing(Nothing) 'False
End Sub

The problem is that Nothing is a Special Object, rather than actually being 'nothing'. It's like the difference in Excel between a Blank cell and a cell with no data (e.g. ="")

I suspect you would want to chain If statements with Is Nothing to create your sort:

If fdsKey2 Is Nothing Then
    loFinalReport.DataBodyRange.Sort Header:=xlYes, _
        Key1:=fdsKey1, Order1:=fdsSortOrders(0)
ElseIf fsdKey3 Is Nothing Then
    loFinalReport.DataBodyRange.Sort Header:=xlYes, _
        Key1:=fdsKey1, Order1:=fdsSortOrders(0), _
        Key2:=fdsKey2, Order2:=fdsSortOrders(1)
Else
    loFinalReport.DataBodyRange.Sort Header:=xlYes, _
        Key1:=fdsKey1, Order1:=fdsSortOrders(0), _
        Key2:=fdsKey2, Order2:=fdsSortOrders(1), _
        Key3:=fdsKey3, Order3:=fdsSortOrders(2)
End If

{EDIT:Following discussions in comments} Here is a function which uses 2 arrays to work out which, if any, of the Keys are not Nothing:

Private Function SortNothing(Target As Range, Optional Key1 As Variant, Optional Order1 As XlSortOrder = xlAscending, Optional Key2 As Variant, Optional Order2 As XlSortOrder = xlAscending, Optional Key3 As Variant, Optional Order3 As XlSortOrder = xlAscending) As Boolean
    Dim iArr As Integer, aKeys(1 To 3) As Variant, aOrders(1 To 3) As XlSortOrder
    iArr = 0 'This pointer will track how many non-Nothing Keys we have
    SortNothing = False
    On Error GoTo FuncErr
    If Not IsMissing(Key1) Then
        If Not (Key1 Is Nothing) Then
            iArr = iArr + 1
            aKeys(iArr) = Key1
            aOrders(iArr) = Order1
        End If
    End If
    If Not IsMissing(Key2) Then
        If Not (Key2 Is Nothing) Then
            iArr = iArr + 1
            aKeys(iArr) = Key2
            aOrders(iArr) = Order2
        End If
    End If
    If Not IsMissing(Key3) Then
        If Not (Key3 Is Nothing) Then
            iArr = iArr + 1
            aKeys(iArr) = Key3
            aOrders(iArr) = Order3
        End If
    End If

    Select Case iArr
        Case 3:
            Target.Sort Key1:=aKeys(1), Order1:=aOrders(1), Key2:=aKeys(2), Order2:=aOrders(2), Key3:=aKeys(3), Order3:=aOrders(3), Header:=xlYes
        Case 2:
            Target.Sort Key1:=aKeys(1), Order1:=aOrders(1), Key2:=aKeys(2), Order2:=aOrders(2), Header:=xlYes
        Case 1:
            Target.Sort Key1:=aKeys(1), Order1:=aOrders(1), Header:=xlYes
    End Select
    SortNothing = True
FuncErr:
End Function

Answer 2

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.

Answer 3

Private Sub PassingVariables(ByRef strNotOptionalText As String, _
                        Optional ByRef strOptionalText As String = "Random or Null", _
                        Optional ByRef wbMainOptional As Workbook = Nothing)

' Statements
End Sub

Public Sub CallingSub()

' Here I'm passing just the required parameter (strNotOptionalText).
' strOptionalText will be "Random or Null" as default and wbMainOptional will be Nothing as default.
Call PassingVariables("Name")

' Here I'm actually passing personalized variabled and not using the default Optionals.
Call PassingVariables("Name", "My Personlalized Text", ThisWorkbook)
End Sub

Here's an example commented on how to use the code optional parameters. Always remember to put your optional parameters after the required ones.