VBA Specify language for Format function

Question

Hi,

In Excel, we can use an LCID code with the TEXT function to get the output in a specific language.  See http://excel.tips.net/T003299_Specifying_a_Language_for_the_TEXT_Function.html

e.g.

=TEXT(A1,"[$-409]mmmm, yyyy")

Is there an equivalent in VBA for the Format function?

CheersRich

Answer 1

Any solution on that?

Answer 2

I've used FORMAT in VBA before.  Have you tried that?

Hi Barb,

Have you used FORMAT with a language ID?

?FORMAT("1 Jan 2013","**[$-407]**mmmm, yyyy")

returns:

    January, 2013

but in Excel:

=TEXT("1 Jan 2013","**[$-407]**mmmm, yyyy")

returns:

    Januar, 2013

which is correctly in German = 0407, see:

http://excel.tips.net/T003299_Specifying_a_Language_for_the_TEXT_Function.html

Cheers

Rich

Answer 3

I've used FORMAT in VBA before.  Have you tried that?

Answer 4

Thanks Hans.

Thanks for confirming my expectations....

As I'm doing this in PPT VBA, I guess I'll be opening an Excel Application to do this - oh well.

Thanks again.

Cheers

Rich

Answer 5

No, but in Excel VBA, you can Application.WorksheetFunction.Text or Application.Text:

MsgBox Application.Text(Range("A1"), "[$-415]mmmm, yyyy")

or you can use Evaluate:

MsgBox Evaluate("TEXT(A1, ""[$-415]mmmm, yyyy"")")