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.
In LibreOffice you must first enable option LibreOffice → Advanced → Optional Features → Enable macro recording. Options are set with LibreOffice → Preferences on a Mac, Tools → Options on other platforms.
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 give the macro a name
• You should save the macro in My Macros if you want to use it with any document when opened on your computer
• You should save the macro in your document if you want to use it with your document when opened on any computer
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. For LibreOffice, any Edit → Paste Special operation below must be replaced by Edit → Paste Special → Paste Special.
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 currently «Shift+Space» → Edit → Cut → Edit selected rows to the clipboard and → Delete Cells delete the rows 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 CopyText Copy the value (as text) from the «F2» → Edit → Select All formula bar to the clipboard → Edit → Copy → «Escape» (without a Newline character)
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. When an entire column has been copied to the clipboard, PasteFormat also copies its column width to the target column. 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. To record macros for columns instead of rows replace Shift+Space with Command+Shift+Space on a Mac or with Ctrl+Shift+Space on other platforms.
• FillRightDown is useful for populating a formula in the first cell of a selected range into all of its cells.
• CopyText can copy a formula's text so you can paste it unchanged in a different cell. It can also copy a non-formula value without appending a NewLine; when you copy a cell to the clipboard without this macro, it ends in a NewLine.
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.