LO Basic Calc Cell.NumberFormat -- is there a list somewhere?

[ocorlett]

I needed to format a cell as a date in my macro, in the US format MM/DD/YY. I recorded a macro formatting it by hand in order to get some code to do it. The code is a bit cumbersome involving the use of the UNO/API. But I noticed it included the lines:

args1(0).Name = “NumberFormatValue”
args1(0).Value = 37

So as an experiment I tried doing it this way instead:

	   	oColDCell.NumberFormat=37

where oColDCell is the name of the cell I was trying to format as a date.

And it worked.

I assume that 37 is some kind of ID for the US MM/DD/YY format. But I cannot find anywhere a list of codes/IDs corresponding to different number formats. Is there such a thing? I think it could be very useful.

[mikekaganski]

How to create a Macro to insert the current date in the current language

My experiments show the following: StarBasic Format() run-time function accepts all the number format codes mentioned here. You may use the [$-langid] syntax to use the desired locale in the Format() output, like: sText = Format(Now(),"[$-41e]D MMMM YYYY") ' Thai The LangIDs might be taken from this page. This, however, needs the parts from @Lupp’s answer to get the current char locale to create complete solution. EDIT 2017-10-31 To go with field creation, you may use this code: …

[sokol92]

See also the FindCreateNumberFormatStyle function in the famous book by A. Pitonyak OOME_4_1.odt.
Example:

  ThisComponent.CurrentSelection.NumberFormat = _
    FindCreateNumberFormatStyle("MM/DD/YY", ThisComponent)

[Villeroy]

reportNumberFormats.odt (59.2 KB)

Not the smartest macro on earth. Install it and find it under Tools>Macros>reportNumberFormats.
It tests the current Writer or Calc document for number format keys from 0 to 100,000. A million does not yield any more formats.
Finally, it reports the number formats to a new spreadsheet. The first block without locale info is about the number formats of the current default locale. They are translated to an equivalent format when opened with a different locale.

[erAck]

@Villeroy

number format keys from 0 to 100,000. A million does not yield any more formats.

Note that in Calc the first 0…9999 format keys are of the default locale, all other (every 10000) format keys of locales are in arbitrary order in which a locale was first used in the document. You’d get format keys up to a million (or more) if you used formats of 100 (or more) different locales…

[mikekaganski]

Please see

Basic NumberFormat Codes

Note that linked page is outdated from 2015 and covered only the languages their Speech API supported back then. Also, MS likes to move things around and not update existing pages. The one canonical page for some years now seems to be [MS-LCID]: Windows Language Code Identifier (LCID) Reference | Microsoft Learn . It’s complicated… in Calc (!) the format key constants 0 to 61 represent predefined number formats of the current LibreOffice locale (system locale if not set differently) when the …

In general: the “fixed” code mapping is not part of the API. There is a range of the numbers, that looks “stable”. But there is a chance that any such code can change at some point (e.g., South African number format changed in LibreOffice, because of the official documentation was pointed out; then, there is a different matter of de-facto use, which may warrant another change … so the code pointing at that format, may change over versions).

The answer there (as well as my answer quoted above) points to the proper API. Better create a number format code for a wanted format in a needed locale explicitly, than rely on some hardcoded magic code.