Editor's note: Know a lot about this topic? Want to share your expertise? Participate in the IBM Lotus software wiki program today.
IBM Lotus Symphony supports macro programming through the OpenOffice.org Basic language. Getting started with your own extensions to Lotus Symphony documents can be as simple as 1, 2, 3:
- Open the macro editor and write your code.
- Add a control to your Lotus Symphony document.
- Associate the macro with an event from the control.
In this article, we explain these steps for a simple program. Then, we illustrate through a more practical example how to use the Universal Network Objects (UNO) component model to query data from a Lotus Symphony spreadsheet.
Macros in Lotus Symphony are stored within modules. Libraries are used to organize modules and can be attached to a document or to a template. In the first example, you create a document, then add a new module to the standard library for that document. You can use the macro editor to edit the content of the module to add the simple program code, and then you can save the new module with the macro.
Opening the macro editor
Open Lotus Symphony, and follow these steps:
- Select File - New - Spreadsheet. (Although the examples here use a spreadsheet, other OpenDocument types work too.)
- With the spreadsheet open, select Tools - Macro - Macro to display the macro dialog. See figure 1.
- On the left navigator, select the Standard library under the Untitled document (this name changes when you save the document).
- Click the New button, and accept the default module name (Module1) by clicking the OK button.
Figure 1. Opening the macro editor on a new module
Now the macro Editor is displayed as shown in figure 2.
Figure 2. The macro editor
For your first macro, you remove the templates and add your own procedure (SUB) to the content in the editor. Follow these steps:
- Select all text below the REM (remark) statement, and then delete it.
- Add this code in the editor:
MsgBox "Hello World"
- Click the Compile button shown in figure 3, and then click the Run button shown in figure 4. A simple message displays with the text Hello World as shown in figure 5.
Figure 3. Compile
Figure 4. Run
Figure 5. Results
You can click the OK button to clear the message.
Saving your work
Now that you have your macro working, it's a good idea to save your Lotus Symphony document; doing so automatically saves the macro along with the document. Select File - Save As, and enter a name in the window, leaving the file type at the default setting. Later screen captures are based on the name Hello.ods.
Enabling macro execution from documents
Before you start associating controls with your new macro, check and update the Lotus Symphony preferences to allow macros stored in documents to be executed. The default settings disable macros from loaded documents by default to enhance security, but you need to run the macros from saved files, so you modify this preference setting. See the sidebar “Macro security levels” for more information about the security levels available.
Follow these steps:
- Select File - Preferences, and then click the plus button next to IBM Lotus Symphony and the Macro Security item.
- Modify the Run Macros: list item to Always, and then select the Show warning before running option as shown in figure 6.
Figure 6. Enabling macros
- Click the OK button to update the Lotus Symphony preferences.
If you open this document later, you are prompted to allow the macros saved within the document to be run.
Adding a control to a document
Now that you have a working macro, your next step is to add a control to the document. For this step, you turn on design mode for the document, add a control, and then set some attributes of the control.
Going to design mode
The first step is to enable the Form Functions toolbar to reveal icons to assist in adding controls to a document.
Follow these steps:
- Select View - Toolbar and select the Form Functions toolbar item (if it is not already selected).
- Cick the Design Mode On icon on the toolbar. See figure 7.
Figure 7. Turn on design mode
For the simple example, you use a button control. Select the Push Button icon, and move the mouse pointer into the document. Drag the control onto the document. Don't worry too much about the size and location as they can be changed later using the positioning handles on the control. See figure 8.
Figure 8. Push Button control added
Modifying the control
You can modify the control by following these steps:
- To change the name on the button control and the help tip displayed when the mouse is over it, right-click the button control and select Control.
- Stay on the General tab. In the Label field, change the value from Button to Say Hello, and then go to the bottom field and in the Help text field enter Click here for a friendly greeting. See figure 9.
Figure 9. Update control properties
- Close the properties dialog.
Notice now that when the mouse is over the button control, the text you added to the Help text is displayed. Now it is time to associate the macro you created previously with this control.
Associating a macro with an event from a control
To associate a macro with an event from a control, follow these steps:
- Still in design mode, right-click the button control and select Control.
- Select the Events tab. Click the button to the right of the Mouse button pressed field to display the Assign Macro dialog.
- On the lower left navigation box, expand the Hello BASIC Macros list and then expand the Standard library list.
- Select the Module1 module, and the new macro is automatically selected as shown in figure 10.
Figure 10. Assign the macro to the control
- Click the Assign button. Notice how the event is now associated with the macro from the library and the module.
- Click the OK button to close the Assign Macro dialog.
Testing the control
All that is left to do now is to position or size the button control as you want and then turn off design mode to test the control. When you have finished resizing or moving the button, click the Design Mode On icon again to toggle off design mode. See figure 11.
Figure 11. Turning design mode off
After you toggle the icon, note that most of the Form Function toolbar icons are disabled, leaving only the Select and Design Mode On icons active. With design mode turned off, click the button control. When the button control is clicked, a message displays as shown in figure 12.
Figure 12. Hello World
You have mastered the basics, so let's look at a practical example that combines a push button control with a more complex macro.
Putting it into practice
The more detailed example deals with the situation in which a Lotus Symphony spreadsheet is used to track a group of projects. You want to assign each project a unique and randomly selected four-digit project identifier code. When a new project is added, the project manager clicks a button in the spreadsheet to get the project identifier code. Let's look at some of the code that you need to add to the macro to accomplish this task.
Generating random numbers
For this scenario, your macro needs to create four-digit numeric codes, which can range from the values 0000 to 9999. To get a value within this range, you can multiply the result from the Rnd() function by 10000 and discard anything after the decimal point. Before the Rnd() function can be called, a call to the Randomize procedure must be made to initialize the random number generator. Because this result is held in an integer variable, a message to display the value should use a formatting code to pad to four characters with leading zeros for codes that are numbered less than 1000. Listing 1 shows working procedure to do it all.
Listing 1. Macro to generate and display a four-digit numeric code
Sub myRandom Dim iCode as Integer Randomize iCode = Int((10000* Rnd)) REM value between 0000-9999 MsgBox Format(iCode,"0000"),0,"Random Code" End Sub
If you do not need to worry about the chance of the same code being assigned to more than one project, this macro is sufficient for your needs.
Finding a unique code value
The total number of projects that are tracked in this tool is small, probably less than 100. The approach that you use to find a unique code is to look through the current spreadsheet data and see if any of the other project codes match a new candidate. As long as the number of potential random codes is large compared to the number of records to search, this simple approach can be efficient.
To do this processing of the spreadsheet data in a macro, you use a small portion of the Universal Network Objects (UNO) API. The Lotus Symphony API is based on UNO, which is the component model of OpenOffice.org. Many language bindings for UNO are available, including BASIC. Although it sounds a bit complex, this component model can be easily applied to access your spreadsheet cell values.
Follow these steps:
- Obtain an object associated with the current worksheet through the getSheets.getByIndex method.
- Query the worksheet object for a cell object with the getCellByPosition method, which takes a column and row value.
- Query the cell object with the value method to obtain the numeric contents of the cell.
The UNO component model accesses are shown in bold in the final macro code in listing 2.
Listing 2. Macro to generate a unique four-digit numeric code
Sub randomCode Dim iCode as Integer Dim oSheet as Object Dim oCell as Object Dim bMatch as Boolean oSheet = thisComponent.getSheets.getByIndex( 0 ) bMatch = True Randomize Do While bMatch = True iCode = Int((10000* Rnd)) REM value between 0000-9999 bMatch = False For iCount = 0 To 499 oCell = oSheet.getCellByPosition( 0, iCount ) If ( oCell.value = iCode ) Then bMatch = True End If Next Loop MsgBox Format(iCode,"0000"),0,"Random Code" End Sub
This macro uses a For ... Next loop to scan through the first 500 rows of the first worksheet in the Lotus Symphony spreadsheet, and it looks in the first column for possible matches. The Boolean variable bMatch is used to track the status of the scan. If a match is found, the Do While ... Loop repeats the cycle with a newly generated random code until a scan of the rows does not produce a match.
Figure 13 shows the spreadsheet with a few project rows and the output from the macro.
Figure 13. The macro in action
Just as with the message box, the first column of the spreadsheet has been customized with a formatting code of 0000 to ensure that a four-digit code is displayed even if the assigned number has fewer significant places.
Although this example used a message to provide the random code to the project manager using the tracking spreadsheet, a simple improvement is to use the UNO getCurrentSelection method on the current document to find a location to populate with the unique code rather than having the project manager copy it from the message.
Now you have learned the basics of Lotus Symphony macro programming in the OpenOffice.org BASIC language. Along the way, you have learned how to create and organize macros, how to enable macro execution in documents, and how to associate events in controls with macros. You can find more information on these and related topics by following the links in the Resources section.
The author would like to thank Paul Bastide, IT Specialist, Lotus Symphony, IBM, for reviewing this article.
- Refer to and participate in the Lotus Symphony wiki.
- Learn more about IBM Lotus Symphony.
- The IBM Lotus Symphony Information Center is included in the installation and is available on the Internet.
- Refer to the OpenOffice.org Developer's Guide.
- Learn more about common spreadsheet operations from the OpenOffice.org wiki.
Get products and technologies
- Download IBM Lotus Symphony from the home page.
- Participate in the discussion forum.
- Share your expertise in the Lotus Symphony Developer home page.