VBA Currency datatype and NumberFormat

[Gustaf]

I use the Currency datatype for decimal numbers. It appears the Currency datatype affects the NumberFormat presentation in Excel. For instance, in my Swedish locale, I get "4,00 kr" rather than "4" as I want. So each time I add something in a cell, I also specify NumberFormat = "General". And it only works if I set NumberFormat *after* setting the cell's value. But when doing that I see a short flicker for each number added, because of the instant change from "4,00 kr" to "4". It works, but the flicker is annoying, and I get a lot of code for each cell. Is there any way to specify that one area has a certain NumberFormat *before* adding data?

Gustaf

 

[keepITcool]

Gustaf

using the value2 property of the range will convert the
variant/currency
before inserting the value, thus preventing the "currency" autoformat.

remember excel uses the double datatype internally.
turn off screenupdating when inserting 30+ numbers..


Sub AddMoney()
Dim sek As Currency
sek = 1234.56

With Range("A1:A2")
.NumberFormat = "general"
.Cells(1).Value = sek
.Cells(2).Value2 = sek
End With
End Sub