VBA Currency datatype and NumberFormat
 

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

VBA Currency datatype and NumberFormat
 

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

--
Jurgen


Gustaf wrote:

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