Programming OpenOffice Calc Macros and Buttons
How to Assign a Macro to a Button in an OpenOffice Calc Spreadsheet
Jul 2, 2008
An OpenOffice.org Calc spreadsheet can be incredibly useful - improving productivity and reducing both the time and effort needed to carry out convoluted calculations. However, the more complex a spreadsheet becomes the more difficult it is to use. Obviously the person creating the spreadsheet can:
- Write a complete set of instructions
- the advantage is that any users of the spreadsheet will understand how to use it
- the disadvantage is that writing (and maintaing) the instructions can be as time consuming as making the spreadsheet itself
- Add macros to the spreadsheet
- the advantage is that all of the hard work is done by the computer and not by the user
- the disadvantage is that the use still needs to have some technical understanding; for example, how to run a macro
One practical (and neat) solution for the originator of the spreadsheet is to:
- create macros for the spreadsheet
- add buttons to the spreadsheet that the user can click and which will then run the correct macros
Creating Macros for an OpenOffice Calc Spreadsheet
Technically it doesn't actually matter if the designer of the spreadsheet creates all the button for the spreadsheet first and the macros second, or creates the macros first and the buttons first; however, it is always best practice to:
- write the macros
- test the macros
- add the buttons to the Calc spreadsheet
- ensure that the buttons run the macros correctly.
The OpenOffice Basic IDE is, of course, the part of the OpenOffice application used for writing macros (information on use of the IDE can be found in the article "Using Macros to Automate OpenOffice.org Calc"); and this can be used to create the macros that will be used for the buttons; macros like:
Sub date_stamp_click
Dim oCell
oCell = thisComponent.Sheets(1).getCellRangeByName("A1")
oCell.String = Now
End Sub
Sub convert_data_click
Dim oCellIn, oCellOut
oCellIn = thisComponent.Sheets(1).getCellByPosition(0,0)
oCellOut = thisComponent.Sheets(1).getCellByPosition(0,1)
oCellOut.Value = datediff ("s", oCellIn.String, Now)
End Sub
The functionality of these two macros is simple but effective:
- date_stamp_click
- selects cell A1 in the second sheet of the spreadsheet (the sheets are numbered 0, 1, 2, etc)
- puts the current date and time into the cell
- convert_data_click
- selects cells A1 and A2 in the second sheet
- puts the number of seconds between the current date/time and the date/time in cell A1 into cell A2
There is, however, a subtle difference in the ways in which the two macros select the required cells:
- getCellRangeByName - this method requires the cell reference, e.g. A1, A2, etc
- getCellByPosition - this method required the cell coordinates; A1 = (0,0), A2 = (0,1), B1 = (1,0)
Both methods do exactly the same job, but sometimes it is easier to use the cell coordinates, other times the cell reference.
Adding a Button to the Spreadsheet
Adding a button to a spreadsheet is quite simple:
- click on:
- View
- Toolbars
- Form Controls
- click on the "Push Button" icon
- use the right mouse button to draw a button on a cell on the spreadsheet (repeating this step for as many buttons as required)
The result of this part of the process is one or more buttons on the spreadsheet; and the steps needed to make these useful are:
- Changing the label to something meaningful to the user:
- right click on the button
- click on "Control" - this will open the "Properties" dialog
- select the "Label" box on the "General" tab - this is used to set the text to be displayed on the button
- Assigning a macro to the button
- click on the "Events" tab in the "Properties" dialog
- click on the button on the same row as "Mouse button pressed" - this opens the "Assign action" dialog
- click on "Macro" - this opens the "Macro Selector" dialog, and this should be used to select the correct macro for the button
This process needs to repeated for all of the buttons that have been added to the spreadsheet.
Using the Spreadsheet Buttons
At the moment all of the buttons will be useless to a user of the spreadsheet, and that's because they will still be in design mode. To change from design mode to active mode two more steps are required:
- the design mode is turned off by clicking the "Design Mode On/Off" icon (showing a pencil, ruler and set square) in the "Form Controls" toolbar
- the "Form Controls" toolbar should be closed
With that completed, the OpenOffice Calc user will not have to have an in-depth knowledge of the spreadsheet and it's macros; they will just have clearly visible, and highly useful, buttons to aid them in their daily tasks.
