向txtBox_Exit()事件传递Boolean变量时为何出现类型不匹配错误？

Hey there, let's walk through the thinking process to solve this type mismatch issue—since you asked for problem-solving frameworks instead of a direct fix, here's how I'd break it down:

1. First, Understand the Core Type Difference #

The error boils down to a fundamental type mismatch: MSForms.ReturnBoolean isn't a standard VBA Boolean. ReturnBoolean is a reference-type object (it has a .Value property that holds the boolean state), while your cancel variable is a basic value-type Boolean. VBA won't let you pass a value type where a reference type is expected, which is why you're seeing the error.

To confirm this, open the VBA Object Browser (press F2) and look up MSForms.ReturnBoolean—you'll see it's a class with a single Value property, designed to let event procedures pass boolean values back to the caller (even when the parameter is declared ByVal).

2. Test Correct Parameter Creation (If You Want to Keep Calling the Exit Event) #

If you still want to directly invoke the Exit event procedure, you need to create a proper ReturnBoolean instance instead of a plain Boolean:

Private Sub txtCurrentValue_KeyDown(ByVal Keycode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If Keycode = vbKeyTab Or Keycode = vbKeyReturn Or Keycode = vbKeyUp Or Keycode = vbKeyDown Then
        Dim cancel As MSForms.ReturnBoolean
        Set cancel = New MSForms.ReturnBoolean ' Create the object instance
        cancel.Value = False ' Set the initial boolean state
        Call txtCurrentValue_Exit(cancel)
    End If
End Sub

A quick heads-up: Event procedures are meant to be triggered by the system, not manually called. This might lead to unexpected behavior (like focus state inconsistencies) down the line, so it's not the most robust approach.

3. Refactor for Modularity (The Cleaner, Maintainable Approach) #

The best long-term fix is to extract the shared logic from the Exit event into a separate, reusable subroutine. This avoids parameter type headaches entirely and makes your code easier to debug and update:

Step 1: Create a Shared Subroutine #

Private Sub ProcessCurrentValue()
    ' Storage variable for later calculations before punctuation added
    If txtCurrentValue.TextLength > 0 Then
        ' Validate numeric input
        If Not IsNumeric(txtCurrentValue) Then
            MsgBox ("Please ensure you only enter numeric values into this box. Punctuation such as £ or % signs will be added automatically.")
            txtCurrentValue.Value = ""
            Exit Sub
        End If
        ' Store validated value
        storedCurrentValue = txtCurrentValue.Value
        ' Format value with decimal places if missing
        If InStr(txtCurrentValue, ".") = 0 Then ' Fixed: InStr returns position, not Boolean
            txtCurrentValue.Value = txtCurrentValue + ".00"
        End If
        ' Add £ symbol if missing
        If InStr(txtCurrentValue, "£") = 0 Then
            txtCurrentValue.Value = "£" + txtCurrentValue
        End If
        ' Move focus to next control
        txtBridgeOffer.SetFocus
    End If
End Sub

(Note: I fixed a small bug here—InStr returns an integer position, not a Boolean. Using = 0 correctly checks for the absence of a character, whereas = False would only catch an invalid position of 0.)

Step 2: Update Both Events to Call the Shared Sub #

Private Sub txtCurrentValue_KeyDown(ByVal Keycode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If Keycode = vbKeyTab Or Keycode = vbKeyReturn Or Keycode = vbKeyUp Or Keycode = vbKeyDown Then
        ProcessCurrentValue()
    End If
End Sub

Private Sub txtCurrentValue_Exit(ByVal cancel As MSForms.ReturnBoolean)
    ProcessCurrentValue()
    ' If you ever need to use the cancel parameter later, you can handle it here
End Sub

This approach eliminates the type mismatch entirely, keeps your logic DRY (Don't Repeat Yourself), and lets you modify the behavior in one place instead of two.

4. Check for Contextual Differences #

One last thing to consider: The Exit event fires when the textbox is losing focus, while KeyDown fires when the textbox still has focus. When you call the shared sub from KeyDown, make sure actions like txtBridgeOffer.SetFocus don't cause unexpected focus jumps (though in your case, it seems intentional). Testing edge cases here will help you catch any odd behavior.

内容的提问来源于stack exchange，提问作者Daniel Barrow