VBA Function Optional parameters

问题:

I am calling a specific piece of code several times therefore I would like to use optional parameters. I can write something like:

Public Sub main()

strA = "A"

'Calling the function
CalculateMe (strA)

End Sub

Public Sub CalculateMe(strA As String)

    Set rs = DB.OpenRecordset("tbl_A")
    rs.MoveFirst
        Do Until rs.EOF
            If rs.Fields(0) = strA Then
                dblA = rs.fields(2).Value
            End If
            rs.MoveNext
        Loop
End Sub

How can I change the function to hold more than 1 optional parameters?

Something like:

Public Sub main()
strA = "A"
strB = "B"

'Calling the function
CalculateMe (strA, strB)

more code
End Sub

Public Sub CalculateMe(Optional strA As String, Optional strB as String)

    Set rs = DB.OpenRecordset("tbl_A")
    rs.MoveFirst
        Do Until rs.EOF
            If rs.Fields(0).Value = strA And rs.Fields(1).Value = strB Then
                dblA = rs.Fields(2).Value
            End If
            rs.MoveNext
        Loop
End Sub

Following Pankaj Jaju's advice, I have managed to have it run by changing it to:

Public Sub main()
strA = "A"
strB = "B"

'Calling the function
dblA = CalculateMe (strA, strB)

End Sub

Public Function CalculateMe(Optional ByVal strA As String, Optional ByVal strB as String)

Set rs = DB.OpenRecordset("tbl_A")
rs.MoveFirst
    Do Until rs.EOF
        If rs.Fields(0).Value = strA And rs.Fields(1).Value = strB Then
            dblA = rs.Fields(2).Value
        End If
        rs.MoveNext
    Loop
End Sub

Now, how can I clear the value of an optional parameter? I will need this for some of the calculations. Something like:

Set strA = Nothing

回答1:

Change your sub and add ByVal

Public Sub CalculateMe(Optional ByVal strA As String, Optional ByVal strB As String)

回答2:

Public Sub CalculateMe(Optional varA As Variant, Optional varB as Variant)

Excerpts from Chip Pearson's excellent explanation:

Rules governing the use of optional parameters:

• The Optional keyword must be present to make a parameter optional.
• The data type should be (but need not be, see below) a Variant data type.
• The optional parameter(s) must be at the end of the parameter list.
• The IsMissing function will work only with parameters declared as Variant. It will return False when used with any other data type.
• User defined types (UTDs) cannot be optional parameters.

Example

Function Test(L1 As Long, L2 As Long, _
    Optional P1 As Variant, Optional P2 As Variant) As String

    Dim S As String

    If IsMissing(P1) = True Then
        S = "P1 Is Missing."
    Else
        S = "P1 Is Present (P1 = " & CStr(P1) & ")"
    End If

    If IsMissing(P2) = True Then
        S = S & "  " & "P2 Is Missing"
    Else
        S = S & "  " & "P2 Is Present (P2 = " & CStr(P2) & ")"
    End If

    Test = S
End Function

Here, both L1 and L2 are required but P1 and P2 are optional. Since both are Variant types, we can use IsMissing to determine whether the parameter was passed in. IsMissing returns True if the Variant parameter is omitted, or False if the Variant parameter is included. If the data type of the optional parameter is any data type other than Variant, IsMissing will return False.

回答3:

Instead of CalculateMe(,strB) you can use

dblA = CalculateMe strB:="B"

回答4:

I'm not sure you really mean "optional". In your example, you're listing the args as optional, but you're still passing both arguments to the function.

In any case, here you pass both arguments:

Public Sub main()
strA = "A"
strB = "B"

'Calling the function
dblA = CalculateMe(strA, strB)

more code
End Sub

If you want to pass only one of the arguments, then do like:

dblA = CalculateMe(, strB)

Or:

dblA = CalculateMe(strA)

Otherwise, if you are always passing both arguments, then it doesn't make sense to have them Optional.