vb.net Excel and NumberFormat for Currency

Question

I am trying to progamatically set an Excel column to type Currency

Hi,

I am using a vb.net application to get data from a database and put it in an Excel worksheet.

In the vb.net code, I'm doing:-

I'm selecting the cell into which I wish to put the data, setting the numberformat to "£#,##0.00", then putting the value in e.g. £200.00

When I subsequently open the Excel workbook, the value correctly appears as e.g. £200.00 but on the Home tab, in the Number section, the dropdown box says 'Custom'

Is there a way to definitively make the column type 'Currency' please?

Thanks,

Kevin

Answer 1

If you manually set a cell to Currency and then would read it's numberformat in VBA, you'd receive something like $#,##0_);[Red]($#,##0)

To showcase the differences:

Sub exampleFormat()
    Dim rng As Range
    Set rng = Me.Range("A5")
    rng.NumberFormat = "$#,##0_);[Red]($#,##0)"
    rng.Value = 300 'will show as (regional) Currency
    Set rng = Me.Range("B5")
    rng.NumberFormat = "$#,##0.00"
    rng.Value = 400 'will show as (regional currency but) Custom format
    Set rng = Me.Range("C5")
    rng.NumberFormat = "£#,##0_);[Red]($#,##0)"
    rng.Value = 300 'will show as Custom format regardless
    Set rng = Me.Range("D5")
    rng.NumberFormat = "£#,##0.00"
    rng.Value = 400 'will show as Custom format as well
End Sub

I know I said VBA but I haven't read anything contradicting VB.NET being different, correct me if I'm wrong.

Answer 2

I did what you suggested and manually set a cell to Currency and then read its numberformat in vb.net.

 It came out as "$#,##0.00_);[Red]($#,##0.00)".

However, using that to put the value into Excel, as in my original post, still resulted in 'Custom' being displayed in the dropdown box in the Home tab, in the Number section.

I really don't know what else I can try. 

Thanks,

Kevin