Macros for Calc can be created in two ways:
• By recording actions and letting OpenOffice create the macro for you
• By learning the complex Application Programming Interface and writing the macro yourself
Recording macros is much simpler then writing them. You don't need any programming experience, though there are some limitations on what you can do. However, many situations do not encounter any of them. All of the following important macros can be recorded by following the instructions given here in the right column. You can record all of them now, or you can record them as you need them.
How does one record a macro? As always, it is really best to read the documentation. In summary:
• Use Tools → Macros → Record Macro (a small window is displayed so you know that OpenOffice.org is recording)
• Perform an appropriate series of operations, like those below
• Click the Stop Recording button and save the macro, giving it a name
The macro names given here are just suggestions. You can choose any allowed (begin with a letter, don't use spaces) name when you record your macro.
Name Description Record these steps
PasteFormats After a selection of cells has Edit → Paste Special → Selection:Formats
been copied to the clipboard, → Options:None → Operations:None
paste only their formatting/style → ShiftCells:Don't → OK
PasteFormulas After a selection of cells has Edit → Paste Special
been copied to the clipboard, → Selection:Formulas → Options:None
paste only their formulas (no → Operations:None → ShiftCells:Don't
formatting) → OK
PasteValues After a selection of cells has Edit → Paste Special
been copied to the clipboard, → Selection:Text,Numbers,Date&Time
paste only their values (no → Options:None → Operations:None
formulas/formatting) → ShiftCells:Don't → OK
PasteUnformatted After text/web content has been Edit → Paste Special
copied to the clipboard, paste it → Selection:UnformattedText → OK
without formatting (text only)
Duplicate Fill the content of the cells above «Shift+UpArrow» → Edit → Fill → Down
the selected range down to it → «DownArrow»
CutRows Move all the cells in the current «Shift+Space» → Edit → Cut → Edit
row to the clipboard and delete → Delete Cells
the row
InsertCut After using CutRows, insert new «Shift+Space» → Edit → Paste → Edit
rows above the current row and → Undo → Insert → Cells → Edit → Paste
paste the clipboard there
FillRightDown In a selection, fill the content Edit → Fill → Right → Edit → Fill → Down
of the upper left cell to all the
others; in a single column, fill
the top selected cell down; in a
single row, fill the left cell to
the right
I use all of them often, so each one has a keyboard shortcut assigned. One could also have a toolbar button for these macros. You can run any macro using Tools → Macros → Run Macro.
The first three macros are used to copy only part of a cell or cell range: the format, the formula, or the value. When using Styles, PasteFormat copies the style from one cell to a range. PasteValues converts formulas to values. To record PasteUnformatted you must first place some text on the clipboard, so first copy something from a text document or a web page, not from Calc. This macro is useful for triggering the Text Import dialog. Duplicate copies the formula or value in the cells above; it is useful if you've inserted a new row and need to copy formulas from above to it. The macro can fill multiple rows, but active cell must be in the row immediately below the one you are copying from. CutRows and InsertCut are used to move rows up or down. The Mac user interface does not provide a simple way to do that. The «Shift+Space» keyboard shortcut selects the current row. The Paste and Undo steps select the correct number of rows for the Insert → Cells operation. FillRightDown is useful for populating a formula into a cell range.
One can use recorded macros to put the current date or time in a cell as a constant, a timestamp, unlike the =TODAY() and =NOW() functions which change their value dynamically. After recording these macros, and before using then, format the timestamp cells with your desired date/time format, perhaps as DDMMM for a date, or as YYYY-MM-DD HH:MM:SS or just HH:MM for a time. This feature is a common request in the forum, and a recorded macro is an easy solution. One would normally assign a keyboard shortcut or toolbar button to them. Because you have recorded the macro yourself, you know there is no malware in it, which can be a concern when you use a macro which you don't understand that has been written by someone else.
Name Description Record these steps
DateStamp Puts the current date in Insert → Function → Category:Date&time
the selected cell → Function:TODAY → Next → OK → Edit → Copy
→ Edit → Paste Special → Selection:Numbers
→ Options:None → Operations:None
→ ShiftCells:Don't → OK
TimeStamp Puts the current date/time Insert → Function → Category:Date&time
in the selected cell → Function:NOW → Next → OK → Edit → Copy
→ Edit → Paste Special → Selection:Numbers
→ Options:None → Operations:None
→ ShiftCells:Don't → OK
LibreOffice may provide other ways to perform some of these actions. I do not use LibreOffice. I realize that some of these actions may be available in OpenOffice via the context menu. I prefer to use keyboard shortcuts.
Note that questions are not allowed in the Tutorials section of the forum. Ask them in the Applications → Calc section.
