vba - 如何检测何时未传递可选的 ByRef 参数

Question

我试图检测是否传递了操作参数，但由于某种原因，所有常用函数(IsMissing()/IsEmpty()/IsNull())总是返回 false。

这就是我正在尝试的:

Public Sub SetValue(Key As String, Optional ByRef ws As Worksheet)
    If IsMissing(ws) Or IsEmpty(ws) Or IsNull(ws) Then
        ws = ThisWorkbook.Sheets(SheetName)
    End If

我也尝试将 ws 设置为 Nothing 或 Null 但结果是一样的:

Public Sub SetValue(Key As String, Optional ByRef ws As Worksheet = Nothing)
    If IsMissing(ws) Or IsEmpty(ws) Or IsNull(ws) Then
        ws = ThisWorkbook.Sheets(SheetName)
    End If

知道为什么会发生这种情况吗？

Answer 1

试试 Is Nothing :

Private Sub CommandButton1_Click()
    Dim ws As Excel.Worksheet
    Set ws = ActiveWorkbook.Sheets("sheet1")
        
    ' Call the sub both ways.
    SetValue "a"
    SetValue "a", ws
End Sub

Public Sub SetValue(Key As String, Optional ByRef ws As Worksheet)
    If ws Is Nothing Then
        ' We got no sheet
        MsgBox "We got no sheet"
    End If

    If Not ws Is Nothing Then
        ' We got a sheet
        MsgBox ws.name
        MsgBox "We got a sheet"
    End If
End Sub