VBA 函数可选参数

VBA Function Optional parameters

提问by ProtoVB

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?

如何更改函数以容纳 1 个以上的可选参数？

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:

按照 Pankaj Jaju 的建议，我设法通过将其更改为：

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

采纳答案by Pankaj Jaju

Change your sub and add ByVal

更改您的子程序并添加 ByVal

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

回答by Christopher Peisert

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

Excerpts from Chip Pearson'sexcellent explanation:

摘自Chip Pearson 的精彩解释：

Rules governing the use of optional parameters:

控制可选参数使用的规则：

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

• 该Optional关键字必须存在，使参数可选。
• 数据类型应该是（但不必是，见下文）Variant数据类型。
• 可选参数必须位于参数列表的末尾。
• 该IsMissing函数仅适用于声明为 的参数Variant。False当与任何其他数据类型一起使用时，它将返回。
• 用户定义类型 (UTD) 不能是可选参数。

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 Varianttypes, we can use IsMissingto determine whether the parameter was passed in. IsMissingreturns Trueif the Variantparameter is omitted, or Falseif the Variantparameter is included. If the data type of the optional parameter is any data type other than Variant, IsMissingwill return False.

这里，L1 和 L2 都是必需的，但 P1 和 P2 是可选的。由于两者都是Variant类型，我们可以使用IsMissing来确定参数是否被通过。IsMissing返回True如果Variant省略参数，或者False如果Variant参数包含在内。如果可选参数的数据类型是 以外的任何数据类型Variant，IsMissing则返回False。

回答by user9026558

Instead of CalculateMe(,strB)you can use

而不是CalculateMe(,strB)你可以使用

dblA = CalculateMe strB:="B"

回答by David Zemens

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

我不确定你的意思是“可选”。在您的示例中，您将 args 列为可选，但您仍将这两个参数传递给函数。

In any case, here you pass botharguments:

无论如何，在这里你传递两个参数：

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.

否则，如果您总是传递两个参数，那么使用它们就没有意义了Optional。