CODEWITHSUNDEEP
vbaexcel
s_a
s_a

Reputation: 977

VBA Inputbox strips decimal comma, converts double to string

I'm trying to create an Inputbox which has a number, with decimals, as the default value. I'm setting the Inputbox to the formula type, because the user might input a formula or reference a cell.

The problem is that the Inputbox seems to strip the comma and coerse the number to a string. I could fix this casting the number as a string with Format, and then going back to a number afterwards, but losing precision. And I'd like to understand what's going on.

The code is:

Sub test()
    Dim Defolt As Double
    Defolt = 1.1866701960364
    Dim InputValue
    InputValue = Application.InputBox("Value?", , Defolt, , , , , 0)
    'for this example, the user just clicks OK to the default value
    Debug.Print InputValue
End Sub

The results are these: VBA inputbox coerses double to string without decimal marker

Thanks!

ps: the locale is Spanish. Excel version is Excel 2010 32bits.

Upvotes: 3

Views: 1475

Answers (1)

sous2817
sous2817

Reputation: 3960

Have a look here. The important part is right under the table:

You can use the sum of the allowable values for Type. For example, for an input box that can accept both text and numbers, set Type to 1 + 2.

and a little further down in the remarks:

If Type is 0, InputBox returns the formula in the form of text — for example, "=2*PI()/360". If there are any references in the formula, they are returned as A1-style references. (Use ConvertFormula to convert between reference styles.)

Try setting the type as 1 and see if you can still use a formula and number. The documentation leads me to think that you can (basically you get formula for free). Since you're setting the type to 0, you're getting back the default Text type.

Upvotes: 2

Related Questions