[Solved] Request for comments: Favorite Recorded Calc Macros

Discuss the spreadsheet application

[Solved] Request for comments: Favorite Recorded Calc Macros

Postby MrProgrammer » Mon Dec 16, 2019 4:55 am

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.
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: 3962
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Request for comments: Favorite Recorded Calc Macros

Postby RusselB » Mon Dec 16, 2019 7:21 am

Looks like a good addition to the rest of the Calc tutorials.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6156
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Request for comments: Favorite Recorded Calc Macros

Postby RoryOF » Mon Dec 16, 2019 3:22 pm

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.
Apache OpenOffice 4.1.7 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31534
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Request for comments: Favorite Recorded Calc Macros

Postby Villeroy » Mon Dec 16, 2019 8:24 pm

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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28641
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Request for comments: Favorite Recorded Calc Macros

Postby MrProgrammer » Wed Dec 18, 2019 3:15 am

Villeroy wrote: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.

Villeroy wrote:
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.
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: 3962
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Request for comments: Favorite Recorded Calc Macros

Postby RoryOF » Wed Dec 18, 2019 11:09 am

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. 
Apache OpenOffice 4.1.7 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31534
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Request for comments: Favorite Recorded Calc Macros

Postby Villeroy » Wed Dec 18, 2019 11:50 am

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28641
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Request for comments: Favorite Recorded Calc Macros

Postby MrProgrammer » Wed Jan 08, 2020 2:15 am

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
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: 3962
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: Bill, RPG and 19 guests