OpenOffice Calc Tips

I have plans to implement a Keno game in the OOo basic language - but first a simple Lotto number generator. If you go into the source, you can configure to your own requirements. Generating a random number between 1 and 49 is really simple. Making sure that number has not already been picked slightly complicates matters.

Download it here

Sub Lotto
Dim i
Dim j
Dim val
Dim match
oSheet = ThisComponent.Sheets(1)
For i = 1 To 6
oCell = oSheet.getCellByPosition(1,i)
Do
match = False
val = int(rnd()*49) + 1
For j = 1 To i
If val = oSheet.getCellByPosition(1,j).getValue() Then
match = True
End If
Next j
Loop Until (match = False)
oCell.setValue(val)
Next i
End Sub

Here is a simple macro that creates a new sheet in the Calc document with a listing of all the notes (comments) found.

Notes can be added to any cell with Insert - Note - see below.

The Basic code for gathering all of these notes and adding them to a new sheet is given below. Items in the listing I’d like to draw your attention to..

• Creating new sheets with the insertNewByName method.
• The PrintableAddressOfCell and ColumnNumberToString routines convert row and column cell offsets to human readable notation - and were written by Andrew Pitonyak.
• The notes (annotations) for each sheet are traversed using a For loop in the exact same way as the sheets of the document.

The newly created comment sheet is shown below.

In the first part of this series of articles, we developed some macros for basic string manipulation on a selected range of cells. We also created a new menu item for easy invocation of these macros.

In the second part, we developed a dialog to allow the user to select from a list of functions.

We will now integrate the enhanced menu, the new dialog and our new functions.

Each radio button in our dialog was given a name. They were UpperCButton, LowerCButton and ProperCButton.
Another key property associated with radio buttons is the State property which has a value of 1 if the button is checked, 0 otherwise. The default state value is 0. In our dialog, we would like to have the ProperC button selected on startup - so we set the state value as shown below..

There are no Events associated with the radio buttons. The other components/controls of the dialog will be able to query the state of each button as we shall see in the code. The subroutines listed below - RunTextUtilsDlg and ExitTextUtilsDlg will be associated with the Run and Exit command buttons. We link the macros and command buttons via the events dialog of each component as shown below. Be careful to choose the correct event type when doing the mapping.

In the dialog editor, select the button to which we wish to assign a macro. Select the Events tab. For a button control, we want to map the macro to the Mouse button pressed event. Invoke the Assign Macro dialog as shown below.

Making sure the corrct event is selected, invoke the Macro Selector as shown below.

With the macro selected,click OK to complete the macro assignment/mapping.

Here is the additonal macro code required to complete the integration. Note the mechanism by which we query the state of each radio button.

Asa final step, we customize our menu to provide a single point of entry to the TextUtils dialog via the RunTextUtilsDlg macro.

Please note that this is just one possible way of implementing the new Calc functionality we have introduced in this series of tips.

Here is the example we have just worked through for you to
download.

In this multi-part article, we will be adding a dialog in a modification of our TextUtils feature. The dialog will permit the user - via radio buttons - to choose which of our functions UpperC, LowerC, or ProperC will be applied to the selected cells.

The dialog editor is invoked through the Macro Organizer. First, select Tools - Macros - Organize Macros - OpenOffice.org Basic as shown below

This opens the OpenOffice.org Basic Macros dialog - below. Now open the OpenOffice.org Basic Macro Organizer dialog as shown below.

Now select the Dialogs tab and with the Standard LIbrary of the open document selected, create a new dialog by clicking New

We rename the new dialog from the default to TextUtils - below.

We now go into the Dialog editor by clicking Edit as shown below.

The initial view of the Dialog Editor with the toolbox on the left and a ‘blank’ dialog on the right.

The dialog components (or controls) are added by first selecting the desired component type from the floating toolbox. As you move the cursor over the toolbox, each component identifies itself. For our particular example, we want to add some option buttons (or radio buttons) as we only want to have one selected at a time. Left-click to activate a component as shown below. Not the white border around the selected component.

Moving the cursor into the dialog, you should see crosshairs (below)

Left-click and drag to define the outline of the new control.

Releaing the mouse, a new default instantiation of the control appears. The green boxes indicate the component is selected and ready for editing

With the cursor inside the boundary of the new component, right-click and select Properties as shown below.

The Properties dialog for each component has two tabs. We first focus on the position and appearance of the control while assigning it a meaningful identifier. We do this in the General tab. In the next part of the tutorial, we will be integrating the dialog and it’s controls with the macros we have created. This will involve the Events tab.

Here is the partially completed dialog - with three radio buttons and two command buttons. It may look complete - but it is not yet integrated into the document.

To be continued….

It is straightforward to enhance the capabilities of OOo Calc with your home grown functions and features. In this tip, we add the ability to change the case of the text in a range of selected cells.(UPPER CASE, lower case or Proper Case).

As you can in the screenshot below, we have added a new top level menu item TextUtils with 3 new functions, UpperC, LowerC, and ProperC. The last function capitalizes the first character only.

We could have invoked these functions with Tools - Macros etc but from a user’s standpoint, it is much more convenient to invoke them the way we have implemented it.

Pretty straightforward - the results of applying UpperC to the selected cells.

First - the source code…

Creating the new menu entries is simple. Invoke the Menu Customization dialog with Tools - Customize as shown below…

Select the Menus tab. Add a new top level menu item - TextUtils and then a seperate entry for each of our new functions.

Here is the Add Commands dialog. We select the module in the left hand box and the macro subroutine on the right. By default, the menu entry is given the same name as the subroutine but hat can be changed.

In the next installment, we will add a dialog to enhance out new functionality even further.

A simple macro that converts a numeric value to it’s English equivalent.

(more…)

A simple macro for sorting sheets in a spreadsheet in ascending alphabetical order.

(more…)

Invariably, macros written for use within the Calc application will need to access the contents of the cells on a spreadsheet. This tip is an introduction to the various available methods.

(more…)

Here is another simple macro that turns an OOo Calc spreadsheet into a real time clock.

(more…)

p>In mathematics, the greatest common divisor (abbreviated GCD), or highest common factor (HCF) of two integers which are not both zero is the largest integer that divides both numbers.

The GCD of a and b is often written as gcd(a,b) or simply (a,b). For example, gcd(12,18) = 6, gcd(-4,14) = 2 and gcd(5,0) = 5. Two numbers are called coprime or relatively prime if their greatest common divisor equals 1. For example, 9 and 28 are relatively prime.

The greatest common divisor is useful for writing fractions in lowest terms.

The algorithm for determining the GCD of two numbers is known as a recursive algorithm - and the OpenOffice Basic code is listed below..