CODEWITHSUNDEEP
vb.netvbaexcel
user5712411
user5712411

Reputation:

Error inserting variable double in excel VBA

i have a program that inserts in an excel file some value. in the excel file it inserts the value of a variable(Double). The numberFormat of the cell is "€#,##0.00".It works pretty good when the value of the variable is 3.0 or 10.0 but if i put 3.5 or 10.3 excel throw an error like this:
enter image description here

In the first cell there is the right number but in the second it throw an error(number stored as text).
The code is this: 

Dim price As Double
Dim price2 As Double
price = 5.0
price2 = 6.5
oSheet.Range("E1").Value = price
oSheet.Range("E2").Value = price2
oSheet.Range("E1").NumberFormat = "€#,##0.00"
oSheet.Range("E2").NumberFormat = "€#,##0.00"

Upvotes: 0

Views: 84

Answers (1)

user4039065
user4039065

Reputation:

You are running into system regional setting differences. Add a regional code to the number format.

Dim price As Double, price2 As Double, oSheet As Worksheet

Set oSheet = Worksheets("Sheet1")
price = 5
price2 = 6.5

With oSheet
    .Range("E1").Value = price
    .Range("E2").Value = price2
    .Range("E1").NumberFormat = "[$€-C07] #,##0.00"
    .Range("E2").NumberFormat = "[$€-C07] #,##0.00"
End With

Results should be similar to the following.

                 regional_format

Upvotes: 1

Related Questions