类型不匹配时，试图分配返回值给变量(VBA Excel)

Question

长期听众，第一次来电

我有一个问题与VBA在Excel 2010。我试图比较用户表单上的各个字段，以确定它们是否为空。然后我将突出显示红色，并将焦点设置在列表中的第一个。

为此，我为每种类型创建了可以为空白/未选中的函数。它们都被声明为AS BOOLEAN。我们的想法是做函数调用来突出显示，通过适当的子函数传递函数调用，并在突出显示函数中使用这些函数的结果:

function Blah(DoThis01(Me.Object1), DoThis02(Me.Object2), ...) As Boolean

这让我不高兴;值总是返回false。所以我创建了三个布尔变量来分配这些函数的值，它开始给我byRef错误。在声明每个变量而不是在一行中声明所有变量之后，这解决了byref问题，但现在我得到了类型不匹配:

Dim foo As Boolean
foo = DoThis01(Me.Object1)
if Blah(foo, bar, ..)

它不喜欢这个中线;这就是我遇到错误的地方

下面是我正在使用的代码片段，以及我试图传递的函数的示例。我不明白为什么我的As布尔函数与As布尔变量不兼容。此外，确定字段是否填充的函数也给了我一些问题，没有注册正确的值。谷歌有很多答案，但到目前为止还没有一个适合我。我很感谢你提供的任何见解。

谢谢,J

PS我意识到触发消息时一些逻辑可能有点偏离。没关系，因为我没有得到正确的值。如果我能把其余的修好，我就把它打扫干净。

工作代码:

保存按钮:

Dim emptyRow As Long
Dim isDuplicate As Boolean, nameSelect As Boolean, comboSelect As Boolean, listSelect As Boolean
Dim listLength As Integer
Dim blah As Integer
' check for empty on form
nameSelect = IsSelectedName(Me.SignalNameTxtBox)
comboSelect = IsSelectedCombo(Me.ComboBox1)
listSelect = IsSelectedList(Me.ListBox1)
If HighlightEmpty(nameSelect, comboSelect, listSelect) Then
    blah = MsgBox("Empty fields required!", vbOKOnly)
    Exit Sub
End If

…更多的东西

显示空表单对象:

Function HighlightEmpty(nameSelect As Boolean, comboSelect As Boolean, listSelect As Boolean) As Boolean
' Highlight empty fields
If Not nameSelect Then Enter_New_Form.SignalNameTxtBox.BackColor = RGB(255, 0, 0)
If Not comboSelect Then Enter_New_Form.ComboBox1.BackColor = RGB(255, 0, 0)
If Not listSelect Then Enter_New_Form.ListBox1.BackColor = RGB(255, 0, 0)
' Set focus to first empty field on form
If Not nameSelect Then
    Enter_New_Form.SignalNameTxtBox.SetFocus
ElseIf Not comboSelect Then
    Enter_New_Form.ComboBox1.SetFocus
ElseIf Not listSelect Then
    Enter_New_Form.ListBox1.SetFocus
End If
' Return boolean to trigger message
HighlightEmpty = nameSelect Or comboSelect Or Not listSelect
End Function

判断是否为空的函数:

Function IsSelectedList(lst As ListBox) As Boolean
    Dim I As Integer
    For I = 0 To lst.ListCount - 1
        IsSelectedList = lst.Selected(I)
        If IsSelectedList Then Exit Function
    Next I
End Function
Function IsSelectedCombo(cbo As ComboBox) As Boolean
    If cbo.Value = "" Then IsSelectedCombo = False
End Function
Function IsSelectedName(nme As TextBox) As Boolean
    If nme.Value = "" Then IsSelectedName = False
End Function

Answer 1

您的函数将始终返回false，除非另外声明为true。像这样添加else语句:

Function IsSelectedName(nme As TextBox) As Boolean
    If nme.Value = "" Then
        IsSelectedName = False
    Else
        IsSelectedName = True
    End If
End Function

---

正如@Timwilliams指出的那样，您可以将其减少到一行，因为nme.Value = ""将评估为True或False。但是，您可能会发现，您需要通过其他方式(如isBlank()和isEmpty())来测试单元格是否为空。在本例中，使用前面的示例。

Function IsSelectedName(nme As TextBox) As Boolean
    IsSelectedName = Len(nme.Value) > 0
End Function

---

编辑

要检查值，只需将文本字符串传递给函数而不是文本框对象。试试这个:

Private Sub CommandButton1_Click()
    MsgBox (IsSelectedName(Me.SignalNameTxtBox.Text))
End Sub

Function IsSelectedName(nme As String) As Boolean
    IsSelectedName = Len(nme) > 0
End Function