Textbox returning different decimals in VBA

Question

I have a very simple project, but I have a problem that I cannot solve.

I believe the problem is something related to language settings, but all the changes I’ve made so far have not returned the expected result.

On a computer, when entering input data and calculating, I have decimal output separated by comma. The result of this Textbox is used later in other calculations in the project, and in this case the project works normally.

Image 1:

On another computer, when entering the same input data and calculating, I have the output with decimal separated by point. When the obtained result is reused in the code for other calculations, the VBA simply ignores the point. For example, treat 2.25 as 225. This implies wrong results later.

Image 2:

I have already checked both computers are with language settings as Portuguese. The global decimal symbol of the system is the same for both machines and in Excel configured to use the decimal separator used by the system.

In the input data, as shown in the images, the comma was used as decimal separator.

Follows code:

Private Sub CommandButton1_Click()

    If TextBox1.Value = "" Or TextBox2.Value = "" Then

        MsgBox "Preencha os campos 'Comprimento (m)' e 'Altura (m)'"

    Else

        TextBox3.Value = Round(TextBox1.Value * TextBox2.Value, 2)

        TextBox4.Value = Round(TextBox3.Value / 30, 2)

        TextBox5.Value = Round(TextBox3.Value / 100, 2)

        TextBox6.Value = Round(TextBox3.Value / 200, 2)

        TextBox7.Value = Round(TextBox3.Value / 150, 2)


    End If

End Sub

Answer 1

Problem

With an example form with a Textbox and a Commandbutton:

You can check the data type of the text box with Vartype or Typename:

Debug.Print VarType(TextBox1.Value)
Debug.Print TypeName(TextBox1.Value)

That will return the number 8 and String, respectively. Then you have a String variable and are leaving for Excel to perform the conversion to numbers automatically, which can create errors, as in the case of language change between different versions of Excel.

Conversion

To convert the input data, be it a number in a comma or dot text box and turn it into a number. The following function can be used in a Module:

Public Function converter_numero(ByVal num As String) As Double
    On Error GoTo erros
    Dim pos_ponto As Long, pos_virgula As Long
    pos_ponto = InStr(1, num, ".", vbBinaryCompare)
    pos_virgula = InStr(1, num, ",", vbBinaryCompare)
    If pos_ponto > 0 Then
        converter_numero = CDbl(Mid(num, 1, pos_ponto - 1)) + CDbl((Mid(num, pos_ponto + 1, Len(num))) / 10 ^ (Len(num) - pos_ponto))
    ElseIf pos_virgula > 0 Then
        converter_numero = CDbl(Mid(num, 1, pos_virgula - 1)) + CDbl((Mid(num, pos_virgula + 1, Len(num))) / 10 ^ (Len(num) - pos_virgula))
    ElseIf IsNumeric(num) Then
        converter_numero = CDbl(num)
    Else
        MsgBox "Insira um número válido!"
    End If
    Exit Function
erros:
    'Criar o tratamento de erros
    MsgBox "O número " & num & " gerou o seguinte erro. " & Err.Number & " : " & Err.Description
    On Error GoTo 0
End Function

Explanation

1. Checks the position of the comma "," or point "." in the variables pos_virgula and pos_ponto, respectively.
2. Checks whether the position is greater than zero, i.e., whether it has a comma or a dot.
3. Creates the decimal number by adding the part before the separator and the part after the separator, i.e., the whole part and the decimal part.
4. Converting the number before the separator CDbl(Mid(num, 1, pos_ponto - 1)) to Double, where it converts the numbers that are in String from position 1 to separator position minus 1.
5. Converting the number after the separator CDbl((Mid(num, pos_ponto + 1, Len(num))) / 10 ^ (Len(num) - pos_ponto)) to Double, where it converts the numbers that are in the String from the separator position plus 1 to the full size of the string (final position) and then divides by 10 high the number of decimals. For example, it has two decimal places, 10 2 = 100.
6. Returns the function result converter_numero

Note: The error handling can be performed using the function Isnumeric

Upshot

Then with the following code:

Private Sub CommandButton1_Click()
    Debug.Print VarType(TextBox1.Value)
    Debug.Print TypeName(TextBox1.Value)
    TextBox1.Value = Round(converter_numero(TextBox1.Value) / 30, 2)
End Sub

The following result can be obtained:

Formatting

Conversion of output data is also required.

Formatting the result in the text box can be performed as follows after the calculations:

TextBox1 = Format(TextBox1, "##0.00")

Function

And then the dot can be replaced by the comma with a function in a module:

Public Function converter_virgula(num) As String
    Dim str As String
    str = CStr(num)
    converter_virgula = Replace(str, ".", ",")
End Function

Example

Then the example would be:

Private Sub CommandButton1_Click()
    TextBox1 = Format(Round(converter_numero(TextBox1.Value) / 30, 2), "###0.00")
    TextBox1 = converter_virgula(TextBox1.Value)
End Sub

Or

Private Sub CommandButton1_Click()
    TextBox1 = converter_virgula(Format(Round(converter_numero(TextBox1.Value) / 30, 2), "###0.00"))
End Sub