[Solved] Request for comments: Favorite Recorded Calc Macros

[MrProgrammer]

I would appreciate comments from the volunteers here about the following material which I plan to publish in the Tutorials → Calc forum.

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, but 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 do you 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
• 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 just 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 just 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 just 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.

The first three macros are used to copy just 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.

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.

[RusselB]

Looks like a good addition to the rest of the Calc tutorials.

[RoryOF]

I use Calc so little, never with macros, that I can make no constructive comment, other than to say that I would trust Mr Programmer's expertise and welcome his contribution.

[Villeroy]

Dispatching frequently used special characters works with Writer, Calc, Draw, Impress:

Code: Select all   Expand viewCollapse view

Sub insertOmega()
InsertSymbol "Ω"
End Sub

Sub insertAlpha()
InsertSymbol "α"
End Sub

sub InsertSymbol(s)
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Symbols"
args1(0).Value = s

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

end sub

[MrProgrammer]

Dispatching frequently used special characters works with Writer, Calc, Draw, Impress:

Yes, I tested your idea just now. I have occasional need for this, maybe enough to record a macro but not enough to assign a shortcut key to it. In Writer I can easily insert special characters (and more) with AutoText, and I can insert favorite characters in any application using methods specific to my operating system, MacOS.

Sub insertOmega()
InsertSymbol "Ω"
End Sub

Sub insertAlpha()
InsertSymbol "α"
End Sub

sub InsertSymbol(s) …

The goal of this topic is to show people they can use the macro recorder for many useful tasks without knowing anything about programming. I think the code in your post was not recorded, but written, perhaps by altering a recorded macro. I have, in fact, done something similar since the versions of PasteFormats/PasteFormulas/PasteValues which I use are three-line routines which call a common Sub based on a recorded macro. I've been a programmer for a long time, and this type of modification is simple and fun.

But the recorded versions of them will work just as well and are simple for anyone to implement. The same will be true of macros which insert a special character. If one records a dozen of them, say for a toolbar, the code will be long and repetitive, but OpenOffice doesn't care, and today's processors are fast and have gigabytes of storage so a lengthy macro library will work just fine, unlike the days when IBM wrote a COBOL compiler for a machine with 4000 bytes of storage. I'm sure this achievement would be regarded as "impossible" by today's programmers. It really was 4000 bytes, not 4096, and they had 6 bits per byte, not 8.

[RoryOF]

The first computer I used was an IBM 1620, with 40K of magnetic core storage, using 28 bit words. Its party piece was to sing "Baa baa black sheep" when a particular program was run, through a transistor radio placed on its console. Fortran II.

Edit: From memory - please check - I think there is a 64KB limit to each OO Macro library. Read this some years ago.

[Villeroy]

A fake database with input form inserting new rows with values and formulas by means of a recorded macro. I used the name box to navigate the right ranges while recording this.

1. Go to named range "InputValues" having links to some user input in range "InputForm" plus a time stamp and a record number and copy this row.
2. Go to the top-left cell of a list range (Database.A4) and insert a new row,
3. Paste-special dates, values and text.
4. Copy a named range "InputFormulas" calculating a total amount and VAT for the current row.
5. Go to the top-left cell of a calculated list range (Database.G4) and paste-special formulas into the same previously inserted row.
6. Go back to named range "InputForm" where the user input happens.

On the "Database" sheet we now have a new record with inserted cells having constant input from "InputForm" a constant row number, a constant time stamp and a set of calculated fields. The constant values are copied from "InputValues". The formulas are copied from "InputFormulas".

The advantage of a named range is that you can rearrange cell ranges freely and rename sheets without losing the references to any kind of macro code. Goto "InputValues" will work no matter where those cells can be found in this document.

Code: Select all   Expand viewCollapse view

REM Recorded macro to insert a new row, copy constant values and formulas of calculated fields.
sub Submit()
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "InputValues"

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

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$Database.$A$4"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:InsertRows", "", 0, Array())

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())

rem ----------------------------------------------------------------------
dim args6(5) as new com.sun.star.beans.PropertyValue
args6(0).Name = "Flags"
args6(0).Value = "SVDT"
args6(1).Name = "FormulaCommand"
args6(1).Value = 0
args6(2).Name = "SkipEmptyCells"
args6(2).Value = false
args6(3).Name = "Transpose"
args6(3).Value = false
args6(4).Name = "AsLink"
args6(4).Value = false
args6(5).Name = "MoveMode"
args6(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args6())

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "ToPoint"
args7(0).Value = "InputFormulas"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args7())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = "ToPoint"
args9(0).Value = "$Database.$G$4"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args9())

rem ----------------------------------------------------------------------
dim args10(5) as new com.sun.star.beans.PropertyValue
args10(0).Name = "Flags"
args10(0).Value = "FT"
args10(1).Name = "FormulaCommand"
args10(1).Value = 0
args10(2).Name = "SkipEmptyCells"
args10(2).Value = false
args10(3).Name = "Transpose"
args10(3).Value = false
args10(4).Name = "AsLink"
args10(4).Value = false
args10(5).Name = "MoveMode"
args10(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args10())

rem ----------------------------------------------------------------------
dim args11(0) as new com.sun.star.beans.PropertyValue
args11(0).Name = "ToPoint"
args11(0).Value = "InputCells"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args11())


end sub



The example document viewtopic.php?t=73154&p=329930#p329930 includes another 2-line msgbox macro demonstrating how the validation of a database record can be achieved with the help of cell validations and a calculated macro-URL.

[MrProgrammer]

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 the desired date/time format, perhaps as DDMMM for a date, or as HH:MM or YYYY-MM-DD HH:MM:SS 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 these macros. Because people have recorded the macro themselves, they know there is no malware in it, which can be a concern when they are given a macro which they 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 time in               Insert → Function → Category:Date&time    
                     the selected cell                      → Function:NOW → Next → OK → Edit → Copy            
                                                            → Edit → Paste Special → Selection:Date&Time    
                                                            → Options:None → Operations:None            
                                                            → ShiftCells:Don't → OK