Change to Canadian (CDN) French number formatting in Excel VBA

Question

I would like to build a piece of code which can convert numbers in my workbook from English to French number formatting.

For example,

• English formatting would be $1,000,000.99, and
• French would be 1 000 000,99 $

I can not seem to find any default French codes built into excel and using a , instead of a . for the decimal does not show numbers less than $1.

Any assistance or advice is appreciated!

Answer 1

To make the currency symbol appear to the right of your numbers, use the NumberFormat property of a Range object and set it to:

• For dollars: #,##0.00 [$$-C0C]
• For euro: #,##0.00 [$€-C0C]

The behavior of commas and periods for decimals and digit grouping is usually controlled by your Region and Language Settings in Windows.

You can change this setting application-wide (i.e. it will affect any workbook opened on your machine) by changing the values of Application.DecimalSeparator and Application.ThousandsSeparator in VBA. Alternatively, you can achieve the same effect by going to File > Options > Advanced and uncheck "Use system separators"

Answer 2

To get the code for any action in Excel:

1. start recording a macro
2. do what you want to replicate via code
3. stop the recording
4. check the macro code (ALT+F11)

In this case applying the formatting gives this code:

Selection.NumberFormat = "#,##0.00 $"

Hope this helps.