how to set excel cell format in vb.net

Question

I'm modifying an existing application written in vb.net. There is an export option which exports information to an excel sheet. Before exporting the cell format is defined as follows.

.Cells(cRow, 17).NumberFormat = "$#,##0.00_);($#,##0.00)"

this works fine. But according to the new requirement, currency symbol is dynamic. Therefore once I set the currency (eg : Malaysian riggit -MYR),

.Cells(cRow, 17).NumberFormat = "MYR#,##0.00_);(MYR#,##0.00)"

this gives an error "Unable to set the NumberFormat property of the Range class". there can be many currencies and I tried setting the currency using a variable.

Dim strCurrency As String = ""
strCurrency = "SGD"
.Cells(cRow, 17).NumberFormat = ""+strCurrency +"#,##0.00_);("+strCurrency +"#,##0.00)"

All are giving me the same error. Someone please let me know how to set the custom currency symbol. thanks

Keith Mifsud · Accepted Answer · 2015-05-18 07:40:48Z

2

If I'm not mistaken any characters in an EXCEL numeric format must be enclosed in quotation marks so:

Dim strCurrency As String = ""
strCurrency = "SGD"
.Cells(cRow, 17).NumberFormat = """"+strCurrency +"""#,##0.00_);("""+strCurrency +"""#,##0.00)"

answered May 18, 2015 at 7:40

Keith Mifsud

7255 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1220497 Over a year ago

You are correct .it's not a mistake. Your code works nicely .thanks

Siddharth Rout · Accepted Answer · 2015-05-18 12:07:58Z

2

Use this

.Cells(cRow, 17).NumberFormat = strCurrency & "#,##0.00_);(" & strCurrency & "#,##0.00)"

There is no need to use so many quotes as shown in the answer that is accepted.

Explanation

Your string

"MYR#,##0.00_);(MYR#,##0.00)"

can also be written as

"MYR" & "#,##0.00_);(" & "MYR" & "#,##0.00)"

Now simply replace "MYR" with your variable

answered May 18, 2015 at 12:07

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Comments

Muhammad · Accepted Answer · 2015-05-18 10:09:13Z

0

Enclose the currency symbol inside quotation :

Dim strCurrency = Chr(34) & "SGD" & Chr(34)
.Cells(cRow, 17).NumberFormat = String.Format("{0}#,##0.00_);({0}#,##0.00)", strCurrency)

The Chr(34) = "

edited May 18, 2015 at 10:09

answered May 18, 2015 at 8:12

Muhammad

1,4001 gold badge16 silver badges26 bronze badges

1 Comment

EternalHour Over a year ago

Please provide an explanation as well.

Collectives™ on Stack Overflow

how to set excel cell format in vb.net

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related