CODEWITHSUNDEEP
vbacomparison
caluthan
caluthan

Reputation: 47

How do I compare values in VBA?

I have this Sub. It is activated when pressing a button on a user form and is used to count one entry up. I have the total amount of entries in this data base stored in A1. There is another button used to count one entry down, it works just fine. They both have checks so they don't load entries that don't exist. Somehow this one doesn't work.

Private Sub ButtonRight_Click()
    
    MsgBox TextBoxID.Value
    MsgBox Cells(1, 1).Value
    MsgBox (TextBoxID.Value < Cells(1, 1).Value)
    If TextBoxID.Value < Cells(1, 1).Value Then
        LoadEntry (TextBoxID.Value + 1)
    End If

End Sub

The LoadEntry Sub is used in other places as well and it works. I have this output stuff with MsgBox for debugging. It gives the outputs 1, 2, false. So (1 < 2) = false.

For comparison here is the other one which works:

Private Sub ButtonLeft_Click()
    
    If TextBoxID.Value > 1 Then
        LoadEntry (TextBoxID.Value - 1)
    End If
    
End Sub

Upvotes: 1

Views: 235

Answers (2)

Алексей Р
Алексей Р

Reputation: 7627

Comparing values of different types in VBA is not a simple task, the result of the comparison depends on the types of variables, the possibility of conversion to a number, etc. Variant variables are compared differently than "non-Variant" variables. See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/comparison-operators

According to the documentation, the Value property of the TextBox object has a base type Variant (see https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/value-property-microsoft-forms).

Therefore, the result of comparing Variant/String (TextBox.Value with String) and Variant/Double (Cell.Value with number) - TextBox.Value is always larger than Cell.Value:

Private Sub CommandButton1_Click()
    TextBox1.Value = "123"
    [A1].Value = 9999

    Debug.Print "TextBox1.Value = " & TextBox1.Value & ", Type is " & TypeName(TextBox1.Value)
    Debug.Print "[A1].Value = " & [A1].Value & ", Type is "; TypeName([A1].Value)
    Debug.Print "TextBox1.Value > [A1].Value : (" & TextBox1.Value & " > " & [A1].Value & ") is " & (TextBox1.Value > [A1].Value)

    Me.Hide
End Sub

'Output:  
'TextBox1.Value = 123, Type is String  
'[A1].Value = 9999, Type is Double  
'TextBox1.Value > [A1].Value : (123 > 9999) is True

Therefore, it is advisable before comparing:

  • reduce the types of compared values to one;
  • to handle errors of type conversion

Simple way is to use Val() function https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/val-function

Private Sub ButtonRight_Click()
    If Val(TextBoxID.Value) < Val(Cells(1, 1).Value) Then
        LoadEntry (TextBoxID.Value + 1)
    End If
End Sub

Also for this purpose I propose to create a function:

Function getNumDef(v As Variant, Optional defV As Long = -1) As Long
    getNumDef = defV    'inintially getNumDef set as defaul value
    On Error Resume Next
    getNumDef = CLng(v) ' if error occurs, getNumDef value remains defV
End Function

It can be applied in the following way:

Private Sub ButtonRight_Click()
    Dim TBV as Long, CV as Long
    TBV = getNumDef(TextBoxID.Value)    'Type conversion and error handling
    CV = getNumDef(Cells(1, 1).Value)   'Type conversion and error handling
    
    If TBV < 0 Or CV < 0 Then
        MsgBox "Some of the values are not numeric or less than 0" _
            & vbCrLf & "Check the raw data", vbCritical + vbOKOnly, "Sub ButtonRight_Click()"
    Else
        If TBV < CV Then
            'The parentheses in `LoadEntry (TextBoxID.Value + 1)` are syntax sugar,
            ' i.e. the argument `TextBoxID.Value + 1` in parentheses is passed as ByVal.
            'If the argument without (), i.e. `LoadEntry TextBoxID.Value + 1`,
            'it is passed as described in the Sub definition or the default ByRef
            
            LoadEntry TextBoxID.Value + 1
        End If
    End If
End Sub

Upvotes: 1

Mathieu Guindon
Mathieu Guindon

Reputation: 71247

The problem is implicit conversions.

Strings are compared as text, so "10" is smaller than "2" because it sorts alphabetically as such.

Debug.Print "10" > "2" ' output: False

The value of a TextBox control is always a String; in order to treat it as a numeric value you must first convert it to a numeric value - but only if it's legal to do so (e.g. "ABC" has no equivalent numeric value).

Moreover, a cell's value is a Variant that may contain a number or another value that can (will) correctly but implicitly convert to a numeric value, but it could also be a Variant/Error (e.g. #N/A, or #VALUE! errors) that will throw a type mismatch error every time you try to compare it to anything (other than another Variant/Error value), so the cell's value should also be validated and explicitly converted before it's compared:

Dim rawValue As String
rawValue = TextBoxID.Value

If IsNumeric(rawValue) Then
    Dim numValue As Double
    numValue = CDbl(rawValue)

    Dim cellValue As Variant
    cellValue = ActiveSheet.Cells(1, 1).Value

    If IsNumeric(cellValue) Then
        If numValue < CDbl(cellValue) Then
            LoadEntry numValue + 1
        End If
    End If

End If

Note that unqualified, Cells is implicitly referring to whatever the ActiveSheet happens to be - if that isn't the intent, consider qualifying that member call with an explicit Worksheet object, e.g. Sheet1.Cells(1, 1). If it is intentional, consider qualifying it with ActiveSheet so that the code says what it does, and does what it says.

Upvotes: 2

Related Questions