[Tutorial] Favorite Recorded Calc Macros

Forum rules
No question in this forum please
For any question related to a topic, create a new thread in the relevant section.

[Tutorial] Favorite Recorded Calc Macros

Postby MrProgrammer » Sun Jan 12, 2020 5:21 am

Note that questions are not allowed in the Tutorials section of the forum. Ask them in the Applications → Calc section.

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:Date&Time
→ 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:Date&Time
→ 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.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3937
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Return to Calc

Who is online

Users browsing this forum: No registered users and 1 guest