Contents


A quick start to using macros and controls in IBM Lotus Symphony

Comments

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:

  1. Open the macro editor and write your code.
  2. Add a control to your Lotus Symphony document.
  3. 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.

Creating macros

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:

  1. Select File - New - Spreadsheet. (Although the examples here use a spreadsheet, other OpenDocument types work too.)
  2. With the spreadsheet open, select Tools - Macro - Macro to display the macro dialog. See figure 1.
  3. On the left navigator, select the Standard library under the Untitled document (this name changes when you save the document).
  4. 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
Opening the macro editor on a new module
Opening the macro editor on a new module

Now the macro Editor is displayed as shown in figure 2.

Figure 2. The macro editor
The macro editor
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:

  1. Select all text below the REM (remark) statement, and then delete it.
  2. Add this code in the editor:

    Sub HelloWorld
    MsgBox "Hello World"
    End Sub
  3. 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
    Compile
    Compile
    Figure 4. Run
    Run
    Run
    Figure 5. Results
    Results
    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:

  1. Select File - Preferences, and then click the plus button next to IBM Lotus Symphony and the Macro Security item.
  2. 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
    Enabling macros
    Enabling macros
  3. 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:

  1. Select View - Toolbar and select the Form Functions toolbar item (if it is not already selected).
  2. Cick the Design Mode On icon on the toolbar. See figure 7.
Figure 7. Turn on design mode
Turn on design mode
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
Push Button control added
Push Button control added

Modifying the control

You can modify the control by following these steps:

  1. 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.
  2. 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
    Update control properties
    Update control properties
  3. 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:

  1. Still in design mode, right-click the button control and select Control.
  2. Select the Events tab. Click the button to the right of the Mouse button pressed field to display the Assign Macro dialog.
  3. On the lower left navigation box, expand the Hello BASIC Macros list and then expand the Standard library list.
  4. Select the Module1 module, and the new macro is automatically selected as shown in figure 10.
    Figure 10. Assign the macro to the control
    Assign the macro to the control
    Assign the macro to the control
  5. Click the Assign button. Notice how the event is now associated with the macro from the library and the module.
  6. 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
Turning design mode off
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
Hello World
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:

  1. Obtain an object associated with the current worksheet through the getSheets.getByIndex method.
  2. Query the worksheet object for a cell object with the getCellByPosition method, which takes a column and row value.
  3. 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
The macro in action
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.

Conclusion

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.

Acknowledgment

The author would like to thank Paul Bastide, IT Specialist, Lotus Symphony, IBM, for reviewing this article.


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Collaboration
ArticleID=413765
ArticleTitle=A quick start to using macros and controls in IBM Lotus Symphony
publish-date=07212009