CODEWITHSUNDEEP
vbaexcel
flowers1234
flowers1234

Reputation: 347

Inputbox is not accepting double number VBA excel

I have a declaration like number= InputBox("Number for:", "Number:"), number is declared as Dim number As Double but when I enter a double number, for example 5.4, into the Inputbox and transmit it into a cell, the cell shows me 54, it deletes the point.

How can I fix this?

THX

Upvotes: 0

Views: 2414

Answers (2)

Jakob Busk Sørensen
Jakob Busk Sørensen

Reputation: 6081

Unfortunately, VBA is horrible at handling differences in decimal seprators. In your case, you should probably use a comma (,), instead of a punctuation/dot (.).

Edit: Using the Application.DecimalSeparator method, it now works regardless of regional settings. Be aware though, it seems to cause some issues if you change the comma separator settings for Excel (it seems that VBA somewhat ignores this setting). If you do not change that however, the example should work in all other cas

Sub GetNumberFromInputBox()
    Dim val As String
    Dim num As Double

    'Get input
    val = InputBox("Number for:", "Number:")

    Debug.Print Application.DecimalSeparator

    If IsNumeric(val) Then
        'Try to convert to double as usual
        num = CDbl(val)

        'If the dot is removed automatically, then
        'you will se a difference in the length. In
        'those cases, replace the dot with a comma,
        'before converting to double
        If Len(val) <> Len(num) Then
            If Application.DecimalSeparator = "," Then
                num = CDbl(Replace(val, ".", ","))
            Else
                num = CDbl(Replace(val, ",", "."))
            End If
        End If

        'Pring the number
        Debug.Print "You selected number: " & num

    Else
        'If its not a number at all, throw an error
        Debug.Print "You typed " & val & ", which is not a number"

    End If

End Sub

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33672

If you want to detect which settings your Excel uses for the Decimal seperator, try the code below:

MsgBox "Excel uses " & Chr(34) & Application.DecimalSeparator & Chr(34) & " as a decimal seperator"

if you want to change it to ., then use the line below:

Application.DecimalSeparator = "."

Upvotes: 3

Related Questions