Passing an empty / optional argument to a VBA function

Question

I got the impression that if a function is defined using some optional arguments, you can pass Nothing, and it will be treated in the same way as "pass nothing at all."

However, I cannot do this work. Here is a snippet of code that works fine if all the "fds ..." variables are not empty, but throws an "Invalid procedure call or argument" if any arguments 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 should I make ugly code for copy-paste (" if fdskey2 is nothing and fdskey3 is Nothing Then SortUsingKey1Only)?

UPDATE

This will work as Key2 / Key3 and Order2 / Order3 are optional, but I do not want to write three versions of the procedure call:

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

Answer 1

:

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

, .
fdsKey1 fdsSortOrders(0), .

---

, :

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)) _
)

, fdsKey1 fdsSortOrders(0) ... , -.
3 .
.

Answer 2

IsMissing , Nothing 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

, Nothing , "". Excel "" (, ="")

, If Is Nothing, :

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: } , 2 , , , :

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 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

, . , .