Macros: Text Utilities Part I

Monday, August 29th, 2005

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, andProperC. The last function capitalizes the first character only.

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

Macros: Text Utilities Part I - student2.ru

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

Macros: Text Utilities Part I - student2.ru

First - the source code…

Sub UpperC
oDesktop = createUnoService(”com.sun.star.frame.Desktop”)
oDocument = ThisComponent
oSelectedCells = oDocument.CurrentSelection
oActiveCells = oSelectedCells.RangeAddress
oSheets = oDocument.Sheets
oSheet = oSheets.getByIndex(oActiveCells.Sheet) ‘ active table
For nRow = oActiveCells.StartRow To oActiveCells.EndRow
For nCol = oActiveCells.StartColumn To oActiveCells.EndColumn
oCell = oSheet.getCellByPosition(nCol,nRow)
CellVal = oCell.getString()
oCell.setString(UCase(CellVal))
Next
Next
End Sub

Sub LowerC
oDesktop = createUnoService(”com.sun.star.frame.Desktop”)
oDocument = ThisComponent
oSelectedCells = oDocument.CurrentSelection
oActiveCells = oSelectedCells.RangeAddress
oSheets = oDocument.Sheets
oSheet = oSheets.getByIndex(oActiveCells.Sheet) ‘ active table
For nRow = oActiveCells.StartRow To oActiveCells.EndRow
For nCol = oActiveCells.StartColumn To oActiveCells.EndColumn
oCell = oSheet.getCellByPosition(nCol,nRow)
CellVal = oCell.getString()
oCell.setString(LCase(CellVal))
Next
Next
End Sub

Sub ProperC
oDesktop = createUnoService(”com.sun.star.frame.Desktop”)
oDocument = ThisComponent
oSelectedCells = oDocument.CurrentSelection
oActiveCells = oSelectedCells.RangeAddress
oSheets = oDocument.Sheets
oSheet = oSheets.getByIndex(oActiveCells.Sheet) ‘ active table
For nRow = oActiveCells.StartRow To oActiveCells.EndRow
For nCol = oActiveCells.StartColumn To oActiveCells.EndColumn
oCell = oSheet.getCellByPosition(nCol,nRow)
CellVal = oCell.getString()
oCell.setString(UCase(Left(CellVal,1)) & Right(CellVal,Len(CellVal) - 1) )
Next
Next
End Sub

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

Macros: Text Utilities Part I - student2.ru

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

Macros: Text Utilities Part I - student2.ru

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.

Macros: Text Utilities Part I - student2.ru