Custom date format in Excel VBA

Question

I am trying to use the custom format to change the date from English to French. The custom code in regular Excel is [$-C0C]d mmmm;@ and yields the output as 20 juillet.

When using the following VBA code:

Format(Range("B2"), "[$-C0C]d mmmm;@")

The output is 20 July

[$-C0C] is supposed to be the code for French Canada

Answer 1

Try this

'~~> Replace Sheet1 below with the sheet code name of the cell which has date
Debug.Print Sheet1.Evaluate("text(B2, ""[$-C0C]d mmmm;@"")")

Explanation: The format function can only take one of the pre-defined values as mentioned in Format Function (Visual Basic for Applications). Check out the section User-Defined Date/Time Formats (Format Function) And hence we use an alternative method.

Answer 2

This did it for me:

Sheet1.Range("B2").NumberFormat = "[$-C0C]d mmmm;@"