Type mismatch when trying to assign return value to variable (VBA Excel)

Question

Long time listener, first time caller.

I am having an issue with VBA in Excel 2010. I am trying to compare various fields on a user form to determine if they are empty. I will then highlight red, and set focus on the first one on the list.

To do this, I have created functions for each type that can be blank/not selected. They are all declared AS BOOLEAN. The idea was to do the function call to highlight, pass the function calls with the appropriate sub functions, and use the results of those in the highlight function:

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

That gave me nojoy; the values always came back false. So I created three boolean variables to assign the values of those functions, and it started givinng me byRef errors. After declaring each variable rather than all on one line, that solved the byref, but now I get a type mismatch:

Dim foo As Boolean

foo = DoThis01(Me.Object1)

if Blah(foo, bar, ..)

It doesn't like that center line; that is where I am running into the error.

Below is the code snippet I am working with, along with an example of the functions I am trying to pass. I can't figure out why my As Boolean functions aren't compatible with an As Boolean variable. Also, the functions to determine whether or not the fields are filled are giving me some issues as well, not registering the correct values. Google had lots of answers, but none that worked for me so far. I appreciate any insight you can offer.

Thanks,

J

PS I realize some of the logic may be a bit off when triggering the message. That is ok, because I am not getting the right values anyways. I will clean it up if I can get the rest working.

Working Code:

Save Button:

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

...More stuff

To Highlight Empty Form Objects:

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

My functions to determine if empty:

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

Your functions will always return false unless otherwise declared as true. Add an else statement like this:

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

---

As @Timwilliams pointed out you can reduce this to one line since nme.Value = "" will evaluate to True or False. You might find however, that you need to test if the cell is empty in other ways such as isBlank() and isEmpty(). In this case use the previous example.

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

---

EDIT

To check the value just pass the text string to the function rather than the textbox object. Try This:

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


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