Macro to apply userdefined date format

[jlittlenz]

Can I have a macro to apply a user-defined format?

If I record a macro, it records what appears to be an index into the list of formats:

dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "NumberFormatValue"
args1(0).Value = 117

dispatcher.executeDispatch(document, ".uno:NumberFormatValue", "", 0, args1())

If I run the macro in another spreadsheet, it does not work, as the user-defined format is not there. Note that I’d like to have macros for many formats, often on the fly, and using formulas is not a solution.

[EarnestAl]

@jlittlenz

using formulas is not a solution

No. Creating a cell style with the wanted format would be a more typical solution.

I can’t help with macros. You might care to look at this page, Apache OpenOffice Community Forum - [Solved] Custom Number Format - (View topic), to work out what to do.

Or you could edit your question to add your desired format, see 150464 – calc macro using uno:numberformatvalue displays different formats in cell on different spreadsheets and computers for the same value

[sokol92]

Look at the famous FindCreateNumberFormatStyle function from A. Pitonyak’s book OpenOffice.org Macros Explained.

[Lupp]

The NumberFormat KEYS (in the questioner’s example example the key 117) cannot be directly assigned to NumberFormat CODES because the "SPACE" of allowed codes never can be finally fixed (development!), and even the currently defined syntax addressing the currently implemented features is not finite.
Therefore the KEYS are generally assigned to CODES per document. A KEY generated and assigned for one document may simply be undefined for a different one - or may be interpreted in a completely different way there.
(The name FindCreateNumberFormatStyle chosen by Andrew Pitonyak is therefore slightly misleading because it doesnt return a style but a key. Alas, also the API uses .NumberFormat for the property which should be named .NumberFormatKey and expects this property’s value to be passed to NumberFormats.getByKey() .
Who’s perfect?
NB: The suggestion to create a cell style and to assign it to respective cells (probably using a macro) isn’t afflicted by the mentioned facts. The CellStyle object knows the CODE, and Calc, when opening a file containing the style will know how to make it work correctly.
You should do it this way.

[Villeroy]

You can’t have a macro to apply userdefine date format. But you can have a userdefined cell style with a keyboard shortcut assigned to the cell style.

[jlittlenz]

Thanks, all. A cell style is what I need.