OpenOffice Calc Tips

The paste special feature is useful for preserving the values in a spreadsheet - but removing the formulae that generated those values. This may be required to preserve proprietary equations.

To remove the formulae for a range of cells, select and copy the desired range. Do not unselect the range. Now click on Edit - Paste Special - see below.

Make sure the Formulas box is unchecked. After you click OK, the formula is removed but the value is preserved.

In a series of short tips, we introduce the Paste Special feature.

A quick and easy way to multiply an array of numbers by a constant without using a formula…

In the example below, we wish to multiply B5:B20 by 10. Temporarily enter 10 into an empty cell. Select it and then Cut or Copy

Select the target range, then click on Edit - Paste Special . In the Paste Special dialog, unselect Paste All and Formats. Also select Multiply. Now click OK …

We are done! The modified cell range.

Open Office Calc has an interesting Easter egg - a hidden feature that might not be obvious or documented. Programmers sometimes bury Easter Eggs in their programs or web sites to add extra depth and challenge users to find them.

In an empty cell, type the following formula..=GAME(”StarWars”)
This is what you will see…

After you have selected your hero, the game begins. Another screen shot…

You can only play the game once. To play again - exit OOo Calc and the quickstarter before restarting.

There are many mathematical and statistical constants in use that are needed in spreadsheets. For OpenOffice Calc, only PI has been hardwired as a function call - PI()

Using defined names is a quick and easy way to define constants in your spreadsheet that will also make your formulae more legible and maintainable. There will also be no need to have hidden sheets where the constants are defined. In this example, we will define Euler’s constant - e

Open the Define Names dialog as shown below.

Instead of assigning/associating the name with a particular cell, assign a fixed value as shown below. Exit the dialog

The named constant can now be used in formulae as shown below. Note that Names defined in this way do not appear in the name box - which is just above the cells area on the left.

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.

The CHOOSE function comes in handy when there are a number of options for a particular value based on the result of an expression or result. It saves having to construct a complex formula involving nested-ifs.

In the example below, the CHOOSE function is used to select the text form for the day of the week - according to the numerical value returned by the WEEKDAY function

A checkerboard shading effect is easily achieved with conditional formatting as shown below. The conditional formatting dialog is invoked with Format - Conditional Formatting

Please review earlier articles on conditional formatting here , here and here.

When the condition (formula) evaluates to true, then the specified style is applied to the cell. We have seen this before. ISODD is a boolean function so is ideal in this situation.

The resulting pattern…

You can annotate your spreadsheet with pop-up notes attached to any cell you wish. The data validation function is invoked as shown below..

Choose the Input Help tab and enter the desired annotation for the selected cell.

This is what will be displayed each time you select the cell.

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