Archive for the OpenOffice Basic Category
Previous Entries
Macros: A Lotto Number Generator
Saturday, October 22nd, 2005
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
Posted in OpenOffice Basic | No Comments
Listing Cell Notes
Wednesday, September 14th, 2005
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 Id 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.
Sub AddCommentSheet
Dim oSheets, oSheet
Dim oRange, oCell
Dim oAnnotations, oNote
Dim i As Integer
Dim j As Integer
oSheets = ThisComponent.Sheets
oSheets.insertNewByName (Comments, oSheets.getCOunt())
oSheet = oSheets.getByName(Comments)
oRange = oSheet.getCellRangeByName(B1:C1″)
oRange.merge(True)
oCell = oSheet.getCellByPosition(1,0)
oCell.setString(Comment listing)
oCell.CellBackColor = 16764057
oCell.HoriJustify = com.sun.star.table.CellHoriJustify.CENTER
oCell = oSheet.getCellByPosition(1,1)
oCell.setString(Created on & Now())
oCell = oSheet.getCellByPosition(1,3)
oCell.setString(Sheet)
oCell.CellBackColor = 16764057
oCell = oSheet.getCellByPosition(2,3)
oCell.setString(Cell)
oCell.CellBackColor = 16764057
oCell = oSheet.getCellByPosition(3,3)
oCell.setString(Note)
oCell.CellBackColor = 16764057
CurRow = 4
For j = 0 To oSheets.getCount()-1
oSheet2 = oSheets.getByIndex(j)
oAnnotations = oSheet2.getAnnotations()
For i = 0 To oAnnotations.getCount()-1
oNote = oAnnotations.getByIndex(i)
oCell = oSheet.getCellByPosition(1,CurRow+i)
oCell.setString(oSheet2.Name)
oCell = oSheet.getCellByPosition(2,CurRow+i)
oCell.setString(PrintableAddressOfCell(oNote.getParent())
oCell = oSheet.getCellByPosition(3,CurRow+i)
oCell.setString(oNote.getString())
Next
CurRow = CurRow + i
Next
End Sub
Function PrintableAddressOfCell(oCell) As String
If IsNull(oCell) OR IsEmpty(oCell) Then
PrintableAddressOfCell = Unknown
Else
PrintableAddressOfCell = ColumnNumberToString(oCell.CellAddress.Column) &_
Cstr(oCell.CellAddress.Row+1)
End If
End Function
Function ColumnNumberToString(ByVal nColumn As Long) As String
Dim s As String
Do While nColumn >= 0
s = Chr$(65 + (nColumn MOD 26)) & s
nColumn = nColumn \ 26 - 1
Loop
ColumnNumberToString = s
End Function
The newly created comment sheet is shown below.
Posted in OpenOffice Basic | No Comments
