In VBA, you need to compare two variables, one of which is requested via the user form, and the second is taken from the Excel table. As a result, it turns out that a numeric variable comes from Excel, and a text string comes from (for some reason). As a result of the comparison, it always turns out that the text is greater than numeric, which is wrong.

How to make so that from the user form came numerical value?

  • And to the number is not an option? - VladD
  • @VladD tell me how to do this? - anj1817
  • val() ? - VladD
  • Google works like. - u_mulder
  • one
    @ anj1817: Well, you have string s containing a number. To get this very number, get a new variable v and assign it the value of Val(s) . Now this number ( v ) can be compared with others. - VladD

3 answers 3

You have string s containing a number. To get this very number, get a new variable v and assign it the value Val(s) ( documentation ). Now this number ( v ) can be compared with others.

    The fact that the text comes from the form is correct - all the fields on the form are text. And the fact that a text entry needs to be translated into a numeric one is also correct.

    But the use of the Val operator is not always correct.

     Sub test() MsgBox Val(1.5) End Sub

    The result of the work is one, not 1.5 (in VBA, the separator is a period). This happens because Val counts as the number of everything that was found before the NOT NUMERIC character and in this example will cut the fractional part.

    Therefore, in many cases it is more correct to convert to a Double number:

     Sub test() MsgBox CDbl(1.5) End Sub

    And, of course, variables must be of the appropriate type.

      The number from the form must be checked for correctness, and only then converted. Similar to the number in the cell. Alternatively, you can use Replace for a game with a point and a comma in the number that is entered in the form. Below is the function to check on Double

        Private Function IsDouble(ByVal value As Variant) As Boolean IsDouble = (VarType(value) = vbDouble) End Function

      And now you can convert and compare. Below is a sample code

       Sub Comparison() a = InputBox("Введите число", "ЧИСЛО А") 'Число из бокса, можно с формы... If IsDouble(a) = False Then 'Проверка. Является ли число Double MsgBox " Число а введено некорректно", vbInformation Exit Sub End If b = Range("A1").value 'Считываем из ячейки А1, это пример If IsDouble(b) = False Then 'Проверка. Является ли число Double MsgBox " Число b введено некорректно", vbInformation Exit Sub End If If CDble(a) > b Then MsgBox " A > B ", vbInformation ElseIf a < b Then MsgBox " A < B ", vbInformation Else 'Другие варианты уже сам.... End If End Sub

      That's more correct