Using Excel VBA to set number format

Question

I am trying to set some monetary values in Excel in a specific format used by my department, but it does not accept my specific format. I tested a very simple format which was accepted, so I am 99% sure it is the format that is fighting me.

My code is

ThisWorkbook.Sheets("Calculatie").Range("G2:H1000").NumberFormat = "_ € * #.##0,00_ ;_ € * -#.##0,00_ ;_ € * " - "??_ ;_ @_ "

Expected output has € on the left of the cell and the value alignet on the right (0-values are "€ -"). Instead I recieve an error;

Error 13 during execution: Types don't match (Translated from Dutch, exact wording might be off)

The following code does work;

ThisWorkbook.Sheets("Calculatie").Range("G2:H1000").NumberFormat = "0.00;0.00;0.00;@"

I copied the format from the "custom number notations" menu in Excel, and checking it with the site below and it appears to be correct.

https://www.excelanytime.com/excel/index.php?option=com_content&view=article&id=121:custom-number-formats-date-a-time-formats-in-excel-a-vba-numberformat-property&catid=79&Itemid=475

Thanks in advance for the help!

Try "_ € * #.##0,00_ ;_ € * -#.##0,00_ ;_ € * "" - ""??_ ;_ @_ " — BigBen
– BigBen, Commented Jul 30, 2021 at 13:43
It's running now! Thats a great start, but for some reason, 56 euros become €56,000 instead of €56,00. I can't see where the additional 0 comes from, but removing a 0 fixes it; "_ € * #.##0,0_ ;_ € * -#.##0,0_ ;_ € * "" - ""??_ ;_ @_ " — Rick
– Rick, Commented Jul 30, 2021 at 13:50

Rick · Accepted Answer · 2021-07-30 14:40:19Z

1

User BigBen helped me along a great way! The problem was a double "" around the "-".

EDIT; I had to change my code. I still had weird issues after my change. The issue was no solved by removing a 0 as I though earlier. Probably due to my Dutch setting, I had to swap decimal points and commas. The code that works for me is;

ThisWorkbook.Sheets("Calculatie").Range("G2:H1000").NumberFormat = "_ € * #,##0.00_ ;_ € * -#,##0.00_ ;_ € * "" - ""??_ ;_ @_ "

edited Jul 30, 2021 at 14:40

answered Jul 30, 2021 at 13:51

Rick

531 silver badge8 bronze badges

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

2 Comments

BigBen Over a year ago

Based on your edit, can you try using .NumberFormatLocal, instead of .NumberFormat, with the original format?

Rick Over a year ago

I have tested it real quick and it appears that works just as well. Thanks for the additional advice!

Collectives™ on Stack Overflow

Using Excel VBA to set number format

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related