[Tutorial] Favorite Recorded Calc Macros

Forum rules
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
Locked
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

[Tutorial] Favorite Recorded Calc Macros

Post by MrProgrammer »

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.
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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Locked