Problem comparing numeric values

[rizwindu]

Hi all,

I have a system where a user enters values in a form, and these are checked to make sure they are within a range specified in a series of cells.

The comparison is performed like this:

Dim ctl as Control
...
If ctl.value < .Cells(row, 2) Then
    Debug.Print (ctl.Name & " = " & ctl.value & " and is lower than " & .Cells(row, 2))
End If
If ctl.value > .Cells(row, 3) Then
    Debug.Print (ctl.Name & " = " & ctl.value & " and is higher than " & .Cells(row, 3))
End If

I have found that using the code exactly as above does not perform the comparisons correctly, always indicating the value is out of range.

If I cast the values to CSng or CDbl then it mostly works as expected, however there are still some strange errors. For example, one of the inputs has a lower acceptable limit of 0.015. If an input of 0.6 gives an error, but 0.60 does not.

With 0.6 entered in the input box I get:

sampleVolume = 0.6 is lower than 0.5

When 0.60 is entered, no error occurs.

What am I missing here?

Many thanks,

Ben.

 

[Andrew Poulsom]

Does this work as expected?

If Val(ctl.value) < .Cells(row, 2).Value Then

 

[rizwindu]

Does this work as expected?

If Val(ctl.value) < .Cells(row, 2).Value Then

Unfortunately not. This has the same outcome as 'CSng' or 'CDbl'.

Values where an integer is inputted in the form are handled correctly.

Strangely, '.6' works, but '0.6' does not.

 

[Andrew Poulsom]

I can't reproduce that behaviour in this simple test:

Private Sub CommandButton1_Click()
    TextBox1.Text = "0.6"
    Cells(1, 1).Value = 0.5
    If Val(TextBox1.Text) < Cells(1, 1).Value Then
        MsgBox TextBox1.Name & " = " & TextBox1.Text & " and is lower than " & Cells(1, 1).Value
    Else
        MsgBox TextBox1.Name & " = " & TextBox1.Text & " and is equal to or greater than " & Cells(1, 1).Value
    End If
    TextBox1.Text = "0.60"
    If Val(TextBox1.Text) < Cells(1, 1).Value Then
        MsgBox TextBox1.Name & " = " & TextBox1.Text & " and is lower than " & Cells(1, 1).Value
    Else
        MsgBox TextBox1.Name & " = " & TextBox1.Text & " and is equal to or greater than " & Cells(1, 1).Value
    End If
End Sub

 

[rizwindu]

Thanks for your quick reply, Andrew.

I tried your simple test, and it does work as expected.

The code example I gave before was slightly simplified from the original code. I will see if I can recreate the issue with a minimum working example, but for now here is the original code:

' Test numeric values against acceptable ranges
Dim row As Integer
' Acceptable ranges are in the 'constants' worksheet
With Worksheets("Constants")
    ' For each control
    For Each ctl In dataInput.Controls
        ' If it matches any of the strings in the first column of the constants worksheet
        For row = 1 To .UsedRange.Rows.Count
            If VBA.UCase(.Cells(row, 1).value) = VBA.UCase(ctl.Name) Then
                ' And if it is numeric
                If IsNumeric(ctl.value) Then
                    ' Check if it is too low or too high
                    If val(ctl.value) < val(.Cells(row, 2)) Then
                        Debug.Print (ctl.Name & " = " & CDbl(ctl.value) & " and is lower than " & CDbl(.Cells(row, 2)))
                        ctl.BackColor = vbYellow
                        checkInputs = False
                    End If
                    If val(ctl.value) > val(.Cells(row, 3)) Then
                        Debug.Print (ctl.Name & " = " & CDbl(ctl.value) & " and is higher than " & CDbl(.Cells(row, 3)))
                        ctl.BackColor = vbYellow
                        checkInputs = False
                    End If
                End If
                ' Check dates
                If IsDate(ctl.value) Then
                    ' Check if it is too low or too hight
                    If CDate(ctl.value) < .Cells(row, 2) Then
                        Debug.Print (ctl.Name & " = " & ctl.value & " is lower than " & .Cells(row, 2))
                        ctl.BackColor = vbYellow
                        checkInputs = False
                    End If
                    If CDate(ctl.value) > .Cells(row, 3) Then
                        Debug.Print (ctl.Name & " = " & ctl.value & " and is higher than " & .Cells(row, 3))
                        ctl.BackColor = vbYellow
                        checkInputs = False
                    End If
                End If
            End If
        Next
    Next
End With

The code that checks the Date entries works as expected. It is just the bit that checks numeric values that gives strange results sometimes.

The 'Constants' Worksheet has three columns of data, the first is a series of Strings that relate to some of the Controls on on the UserForm, the second and third give minimum and maximum acceptable ranges for those inputs, E.g.:

Col A        Col B    Col C
cmHeight     20.00   200.00
weight       5.00    300.00
stWeight     1.00    50.00
lbWeight     0.00    14.00
bgCounts1    0.00    600.00
bgCounts2    0.00    600.00
s1Counts1    0.00    20000.00
s1Counts2    0.00    20000.00
stdVolume    0.02    3.00
syringePre   6.00    12.00

If I enter a value of 0.6 into the TextBox named 'stdVolume' then it is flagged up as an error: "stdVolume = 0.6 is lower than 0.015". However, if I enter 0.60 into the same box it works as expected.

Similar issues happen in other input boxes. For example for 'syringePre' I get no error if I enter 10, but if I put 10.0 I get "syringePre = 10.0 is lower than 6".

I really have no idea why this is all happening!

Ben.

 

[Andrew Poulsom]

Your code isn't using VBA's Val function. In addition it isn't using the cell's Value property.

Rather than looping around the UsedRange you could use the WorksheetFunction's Match function to get the row number.

It's unwise to use the name of a VBA property as the name of a variable - in your case row.

 

[rizwindu]

Your code isn't using VBA's Val function. In addition it isn't using the cell's Value property.

Rather than looping around the UsedRange you could use the WorksheetFunction's Match function to get the row number.

It's unwise to use the name of a VBA property as the name of a variable - in your case row.

I know that there are probably more efficient ways of coding this (such as a Match rather than a loop) but for now I'll just be happy with making it work.

From your first comment am I to understand that the comparison should be something along the lines of this?

If VBA.val(ctl.value) < (.Cells(row, 2).value) Then

I have tried this and it still produces the same errors.

Ben.

 

[Andrew Poulsom]

If you want to use a loop you should exit it when a match is found.

 

[rizwindu]

If you want to use a loop you should exit it when a match is found.

I've added an Exit For in the relevant part of the code, and might look to ditching the loop in favor of a Match command at some point. However, I still have the original error of the comparison not working correctly.

I have tried many forms of the comparison, but always seem to have the same issue.

If VBA.val(ctl.value) < (.Cells(row, 2).value) Then

 

[rizwindu]

I have managed to solve this issue now.

The problem was not with the comparison in the If statement, but it was an issue with some values being picked up in the IsDate command.

The value of 0.6 would result in a TRUE result to IsDate(0.6) and would get compared in that section additionally. A value of 0.60 would result in FALSE to IsDate(0.60) and therefore would not get erroneously diverted there.

Due to some prior planning of the naming of the Controls during the design phase I managed to get around this by changing the IsDate check to something like:

If ctl.Name like "*Date" or ctl.Name = "dob" Then