excel - 如何忽略使用可选参数的代码？

Question

我有一个 sub 需要一个必需的参数和一个可选的参数:

Sub selectRange(txtbox As MSForms.TextBox, Optional lbl As MSForms.Label)

我必须同时传递两个参数，否则会出错。

该错误是可以理解的，因为 sub 包含直接引用可选参数 (lbl) 的行，例如:

If Len(s) = 0 Then
    lbl.ForeColor = RGB(255, 0, 0)
    lbl.Font.Italic = True
    lbl.Caption = "{!this range doesn't contain values!}"
    Exit Sub
End If

代码中的许多其他地方都引用了可选参数。

我做了哪些修改，以便 selectRange 可以在只传递所需参数的地方运行？

Answer 1

您需要检查控件是否通过，然后进行相应处理。例如

Sub selectRange(txtbox As MSForms.TextBox, Optional lbl As MSForms.Label)
    '
    '~~> Rest of the code which has nothing to do with the label
    '

    '~~> For label, Check it is passed
    If Not lbl Is Nothing Then
        If Len(s) = 0 Then
            lbl.ForeColor = RGB(255, 0, 0)
            lbl.Font.Italic = True
            lbl.Caption = "{!this range doesn't contain values!}"
            Exit Sub
        End If
    End If
End Sub

这是您可以测试它的方法

Private Sub CommandButton1_Click()
    '<~~ This is will give the first message box
    selectRange TextBox1, Label1

    '<~~ This is will give the Second message box
    selectRange TextBox1
End Sub

Sub selectRange(txtbox As MSForms.TextBox, Optional lbl As MSForms.Label)
    '~~> For label, Check it is passed
    If Not lbl Is Nothing Then
        MsgBox "Label control is passed as a parameter"
    Else
        MsgBox "Label control is not passed as a parameter"
    End If
End Sub