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

This article provides a step-by-step guide to get you started with macro programming for IBM® Lotus® Symphony™ in OpenOffice.org BASIC. It introduces the macro editor, shows how to add a control object to a Lotus Symphony document, demonstrates how to associate a control event to a macro, and introduces how to access data in a Lotus Symphony spreadsheet.

Share:

Tim Robinson (timro@us.ibm.com), Senior Software Engineer, IBM

Tim Robinson (timro@us.ibm.com) is a Senior Software Engineer working on the IBM Innovation Center team in San Mateo, CA. In that team, he provides insights to and strategy for network security, open systems, and dynamic infrastructure. No longer content with migrating common productivity applications to IBM Lotus Symphony, he's rolling up his sleeves to get the most from this innovative platform.



21 July 2009

Also available in Chinese Spanish

Editor's note: Know a lot about this topic? Want to share your expertise? Participate in the IBM Lotus software wiki program today.

Introduction

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

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

Figure 2. 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
    Figure 4. Run
    Run
    Figure 5. Results
    Results

You can click the OK button to clear the message.

Saving your work

Macro security levels

Never: Disables all document macros except macros entered in the macro editor or loaded manually.

According to path list: Only runs document macros from documents located in the paths specified.

Safe mode: Runs document macros according to the assigned events, except for those unsafe APIs that include disabled UNO APIs and StarBasic runtime methods.

Always: Runs all document macros, regardless of source.

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
  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

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

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
  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
  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

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

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

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.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into IBM collaboration and social software on developerWorks


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