Arrays as subroutine arguments

In the last version of VBA that I used, if one wanted to use an array as an argument in a subroutine or function, one was limited to one array which had to be the last argument. This isn't the case with ooo basic. One or more arguments can be arrays, and arrays can be placed anywhere in the argument list.

start to Main

This is the start to my main macro. I'm putting it here so it's less likely for me to lose it again. It just shows different ways to get info from a dialog.

dim ten(9)
oDSheet = ThisComponent.Sheets.getbyName("Data")
oDRange = ThisComponent.Sheets.getByName("Data").getCellbyposition(0,0)
ThisComponent.getCurrentController.select(oDRange)

with oDialog1
tfield = .getcontrol("ListField").getselecteditem()
dsn = .getcontrol("Label1").text
if .getcontrol("CheckNegAdj").state then
n = right(dsn,1)
if n = "1" then n = "0" else n = "1"
dsn = left(dsn,2) & n
end if
PPS = .getcontrol("NumPPS").value

ten(0) = .getcontrol("NumShares").value
if mid(dsn,2,1) then
if ten(0) = 0 then msgbox "number of shares?" : exit sub
if right(dsn,1) = "1" and ten(0) > 0 then ten(0) = 0 - ten(0)
if right(dsn,1) = "0" and ten(0) < 0 then ten(0) = 0 - ten(0)
else
ten(0) = ""
end if
ten(1) = .getcontrol("ComboName").text
if ten(1) = "" then
if .getcontrol("CheckChange").state then
answer = msgbox("Is this a broker name change?",4,"name change/transfer")
if answer <> 6 then msgbox tfield & " name?" : exit sub
else
msgbox tfield & " name?" : exit sub
end if
end if
ten(2) = .getcontrol("DateField1").text
if not isdate(ten(2)) then msgbox "date is not date" : exit sub
if datediff("d",ten(2),now) < 0 then msgbox "date is in future" : exit sub
date1 = ten(2)
ten(3) = year(ten(2))
ten(4) = choose(month(ten(2)),1,1,1,2,2,2,3,3,3,4,4,4)
if month(ten(2)) = 1 and day(ten(2)) < 5 then
answer = MsgBox("Should this be reported as previous year income?", 4, "Reportable")
If answer = 6 Then
ten(3) = ten(3) - 1
ten(4) = 4
ten(7) = "reportable " & ten(3) & " " & ten(7)
End If
end if
ten(5) = .getcontrol("NumCash").value
if left(dsn,1) then
if ten(5) = 0 then msgbox "cash amount?" : exit sub
if right(dsn,1) = "1" and ten(5) > 0 then ten(5) = 0 - ten(5)
if right(dsn,1) = "0" and ten(5) < 0 then ten(5) = 0 - ten(5)
else
ten(5) = ""
end if
ten(6) = .getcontrol("ComboType").text
if .getcontrol("CheckPPS").state then ten(6) = "PPS"
if .getcontrol("CheckLieu").state then ten(6) = "cash in lieu"
if tfield = "cash" and ten(6) = "div" then
temp = msgbox("Is this a mm div?",4,"mm div?")
if temp = 6 then ten(6) = "mm div"
if ten(6) = "buy" then ten(6) = "dep"
if ten(6) = "sell" then ten(6) = "wth"
end if
if ten(6) = "" then msgbox "transaction?" : exit sub
if .getcontrol("CheckNonTax").state then
ten(6) = "non " & ten(6)
temp = msgbox("Is this reportable?",4 + 256,"Reportable?")
if temp = 7 then ten(6) = "n " & ten(6)
end if
if .getcontrol("CheckChange").state then
ten(6) = "change"
else
if .getcontrol("CheckCorrection").state then
oldtype = ten(6)
ten(6) = .getcontrol("ComboCorrection").text
if ten(6) = "" then msgbox "new transaction type?" : exit sub
end if
end if

ten(7) = .getcontrol("TextComments").text
if .getcontrol("CheckNegAdj").state then ten(7) = "Correction--" & ten(7)
ten(8) = .getcontrol("ComboBroker").text
ten(9) = ""
if val(ten(5)) <> 0 then
if tfield = "cash" then
if ten(6) = "dep" or ten(6) = "wth" then ten(0) = ten(5)
if .getcontrol("CheckReinv").state then ten(0) = ten(5)
end if
if val(ten(0)) <> 0 then
ten(9) = ten(5)
if PPS = 0 then PPS = Rounding(ten(5)/ten(0),3)
end if
end if
end with

revised Search

Here is the Search function from before, revised to include the macro SheetObj. SheetObj and SheetName are in other posts here. (Both the start and the end of the macro have been changed, the latter for date searches.)

Function FindTriple(trange, tsheet, c, r, tname, tname2, offset2, tname3, offset3, n, go)
'if c (or r) is a number, look for nth occurence in column c (or row r) of trange on tsheet
'with tname2 offset offset2 cols and tname3 offset offset3 cols
'columns and rows start at 0 ... c and r are initially range (not sheet) references
'before calling function, set r (or c) to "" to search all rows (or all columns)
'returns sheet (not range) col c and row r where found, or if not found first cell of searched range,
'or ... col and row of last cell where found (for n > 1)
'trange can be chosen as "" to search entire sheet or an entire column or row
'if both trange and tsheet are "", current selection is searched
'tname2 or tname3 can be chosen as "" for simpler searches

FindTriple = 0
oDoc = ThisComponent
dim clrw(3)
oSheet = SheetObj(trange,tsheet,clrw())

if isnumeric(c) then clrw(0) = clrw(0) + c : clrw(2) = clrw(0)'to search in only one column
if isnumeric(r) then clrw(1) = clrw(1) + r : clrw(3) = clrw(1)'to search in only one row
oRange = oSheet.getcellrangebyposition(clrw(0), clrw(1), clrw(2), clrw(3))
oCell = oSheet.getcellbyposition(clrw(0), clrw(1))
c = clrw(0)'change from range coordinates to sheet coordinates
r = clrw(1)

k = 0
if tname = oCell.string then gosub FoundOne

xSearchD = oRange.createSearchDescriptor()
With xSearchD
.SearchString = tname
.SearchCaseSensitive = false
.SearchWords = true 'forces the entire cell to contain only the search string
End With

do
xFound = oCell
xFound = oRange.findNext(xFound, xSearchD)
if IsNull(xFound) then exit do
oCell = xFound
gosub FoundOne
loop
exit function

FoundOne:
c = oCell.rangeaddress.startcolumn
r = oCell.rangeaddress.startrow
temp2 = oSheet.getcellbyposition(c + offset2,r).string
temp3 = oSheet.getcellbyposition(c + offset3,r).string

if len(tname2) > 2 and temp2 <> tname2 then return

if len(tname3) < 2 then
k = k + 1
else
if isdate(tname3) and isdate(temp3) then
temp = DateDiff("d",tname3,temp3)
if temp > 1 and temp < 5 then k = k + 1
else
if temp3 = tname3 then k = k + 1
end if
end if
If k = n Then
FindTriple = -1
If go Then
oCell = oSheet.getcellbyposition(c,r)
oDoc.getCurrentController.select(oCell)
End If
exit function
End If
Return
End Function

Open dialog

From my limited experience, it seems that all the macros that refer to a given dialog have to be on the same page (ie the same basic module). A library can have quite a few other basic module (pages). Mine has modules named: Main, Merger, NewYear, Place, Refresh, Search, SimCity, SSum, and Taxes. (The macro in the SimCity module is to sort and reformat a range containing info on rewards and other plop-able "buildings" in SimCity4.) There is also a dialog module in the library called Dialog1. Here's the beginning of the Main module with a macro which opens a dialog with some hidden controls and a combo box populated with elements from a range in a document. The library itself is called "Accounts".

Dim oDialog1 as Object
dim oControl as Object
dim oDoc as Variant

Sub Dialog1Show '---------------open Dialog1, populate ComboBroker----------------------
oDoc = ThisComponent
DialogLibraries.LoadLibrary( "Standard" )
oDialog1 = CreateUnoDialog( DialogLibraries.Accounts.Dialog1 )
oControl = oDialog1.getcontrol("ComboBroker")

oSheet = ThisComponent.Sheets.getbyname("Misc")
oRange = oSheet.getcellrangebyname("Brokers")

for j = 1 to oRange.rows.count -1'first line is column heading
temp = oRange.getcellbyposition(0,j).string
oControl.additem(temp,0)
next

'make option buttons invisible
for j = 1 to 5
oDialog1.getcontrol("OptionButton" & j).setvisible(0)
next
oDialog1.getcontrol("ComboCorrection").setvisible(0)

oDialog1.Execute()

End Sub

Sheet object

After I found myself copying the first few lines of the search macro into another macro, I decided to make them into their own macro.

Function SheetObj(trange,tsheet,clrw())
'trange can be chosen as "" to get entire sheet and coordinates for entire sheet, not just range on sheet
'if both trange and tsheet are "", current selection is searched
'do not "dim clrw(3)"here, it's equivalent to putting redim clrw(3) at the end of the macro
oSheets = ThisComponent.sheets
oRanges = ThisComponent.namedranges
if oSheets.hasbyname(tsheet) then
SheetObj = oSheets.getbyname(tsheet)
oRange = SheetObj'changed later if trange <> ""
else
if oRanges.hasbyname(trange) then
tsheet = SheetName(trange,0)
SheetObj = oSheets.getbyname(tsheet)
else
SheetObj = ThisComponent.CurrentController.getActiveSheet
oRange = ThisComponent.getcurrentselection()
end if
end if

if oRanges.hasbyname(trange) and trange <> "" then oRange = SheetObj.getcellrangebyname(trange)
if isnull(oRange) then oRange = SheetObj

with oRange.rangeaddress
clrw(0) = .startcolumn
clrw(1) = .startrow
clrw(2) = .endcolumn
clrw(3) = .endrow
end with
end function

Find number in string; also Sort

The first macro finds the first number (not a single digit but an entire number) in a string. The second sorts a range which may or may not have a header.

Function FindNum(ttext)
'returns the first number expression in ttext
n = 1
Do While n <> " " then exit do
n = n + 1
Loop
FindNum = Val(Mid(ttext, n))
end Function

Sub Sorting(trange,tsheet,c1,c2,c3,dir1,dir2,dir3,header)
myarray1 = Array(c1,c2,c3)'range cols start w 0 as the first one
myarray2 = Array(dir1,dir2,dir3)'dir = true for ascending or false otherwise
n = -isnumeric(c1) - isnumeric(c2) - isnumeric(c3)
if n = 0 then exit sub
Dim aSortFields(n) as New com.sun.star.util.SortField
Dim aSortDesc(1) as New com.sun.star.beans.PropertyValue

oSheet = ThisComponent.Sheets.getByName(tsheet)
oRange = oSheet.getCellRangeByName(trange)

for j = 0 to n-1
aSortFields(j).Field = myarray1(j)
aSortFields(j).SortAscending = myarray2(j)
next

aSortDesc(0).Name = "SortFields"
aSortDesc(0).Value = aSortFields()
aSortDesc(1).Name = "ContainsHeader"
aSortDesc(1).Value = header

oRange.Sort(aSortDesc())
End Sub

Current / used area

a very short macro

Sub FindRange(tsheet, c1, r1, c2, r2, ttype)
'finds end of either current or used area
oSheet = ThisComponent.sheets.getbyname(tsheet)
oCell = oSheet.getcellbyposition(c1,r1)
oCursor = oSheet.createCursorbyrange(oCell)

if ttype = "current" then
oCursor.gotoEnd
elseif ttype = "used" then
oCursor.gotoEndOfUsedArea(true)
end if

c2 = oCursor.rangeaddress.endcolumn
r2 = oCursor.rangeaddress.endrow
End Sub

Search

This macro searches records in a range to find a record that matches up to 3 criteria. The function itself returns true or false. If the first criteria one wants to match is in range column 0, for example, before calling the function, set c = 0 and r = "". If the record is found, the sheet column and row of the found record will be returned as well. This also does simple searches. (FindSheet is a function. It returns the name of a sheet that a given range is on.)

Function FindTriple(trange, tsheet, c, r, tname, tname2, offset2, tname3, offset3, n, go) as Boolean
'if c (or r) is a number, look for nth occurence in column c (or row r) of trange on tsheet
'with tname2 offset offset2 cols and tname3 offset offset3 cols
'columns and rows start at 0 ... c and r are initially range (not sheet) references
'before calling function, set r (or c) to "" to search all rows (or all columns)
'returns sheet (not range) col c and row r where found, or if not found first cell of searched range,
'or ... col and row of last cell where found (for n > 1)
'trange can be chosen as "" to search entire sheet
'if both trange and tsheet are "", current selection is searched
'tname2 or tname3 can be chosen as "" for simpler searches

FindTriple = 0
oDoc = ThisComponent

if oDoc.sheets.hasbyname(tsheet) then
oSheet = oDoc.sheets.getbyname(tsheet)
if oDoc.NamedRanges.hasByName(trange) then
oRange = oSheet.getcellrangebyname(trange)
else
oRange = oSheet
end if
else
if oDoc.NamedRanges.hasByName(trange) then
tsheet = FindSheet(trange,0)
oSheet = oDoc.sheets.getbyname(tsheet)
oRange = oSheet.getcellrangebyname(trange)
else
oSheet = oDoc.CurrentController.getActiveSheet
oRange = oDoc.getcurrentselection()
if isnull(oRange) then oRange = oSheet
end if
end if

with oRange.rangeaddress
c1 = .startcolumn
c2 = .endcolumn
r1 = .startrow
r2 = .endrow
end with

if isnumeric(c) then c1 = c1 + c : c2 = c1'to search in only one column
if isnumeric(r) then r1 = r1 + r : r2 = r1'to search in only one row
oRange = oSheet.getcellrangebyposition(c1, r1, c2, r2)
oCell = oSheet.getcellbyposition(c1, r1)
c = c1
r = r1
k = 0
if tname = oCell.string then gosub FoundOne

xSearchD = oRange.createSearchDescriptor()
With xSearchD
.SearchString = tname
.SearchCaseSensitive = false
.SearchWords = true
REM SearchWords forces the entire cell to contain only the search string
'from Andrew Pitonyak's ooDocument. He used bWholeWord instead of true
'.SearchWords = bWholeWord
End With

do
xFound = oCell
xFound = oRange.findNext(xFound, xSearchD)
if IsNull(xFound) then exit do
oCell = xFound
gosub FoundOne
loop
exit function

FoundOne:
c = oCell.rangeaddress.startcolumn
r = oCell.rangeaddress.startrow
temp2 = oSheet.getcellbyposition(c + offset2,r).string
temp3 = oSheet.getcellbyposition(c + offset3,r).string
if (len(tname2) < 2 or temp2 = tname2) _
and (len(tname3) < 2 or temp3 = tname3) then k = k + 1
If k = n Then
FindTriple = -1
If go Then
oCell = oSheet.getcellbyposition(c,r)
oDoc.getCurrentController.select(oCell)
End If
exit function
End If
Return
End Function

Create range, Column number to letter, Rounding

Three short macros ... The first creates a named range. The second converts a column number to a letter. (Note: Column 0 is column A, column 26 is column AA. Also, row 0 is row 1.) The third rounds a number to n decimal places.

Sub NewRange(trange, tsheet, c1, r1, c2, r2)
'defines trange on tsheet with these coordinates
if trange = "" then exit sub
r1 = r1 + 1 : r2 = r2 + 1
temp = "$" & tsheet &amp;amp;amp; ".$" & Letter(c1) & "$" & r1 & ":$" & Letter(c2) & "$" & r2
with ThisComponent.NamedRanges
If .hasByName(trange) Then .removeByName (trange)
.addNewByName(trange, temp, createUnoStruct( "com.sun.star.table.CellAddress" ),0 )
end with
r1 = r1 - 1 : r2 = r2 - 1
End Sub

Function Letter(c)
temp = Int(c/26)
if temp > 0 then Letter = Chr(64 + temp) else Letter = ""
Letter = Letter & Chr(65 + c - 26 * temp)
End Function

Function Rounding(x,n)' rounds x to n decimal places
Rounding = int(x * 10^n)/10^n
end Function

saving a dialog

I recently updated Open Office and clicked the wrong button and lost all the macros and the dialog that I had spent so long on. They were nowhere to be found. However, in looking through computer files and then finally doing a search at the OpenOffice Forum, I now know where my newly created macros are -- which doesn't help too much since they're pretty well buried.
However, while playing around, I found out that one can "export" a library of macros and dialogs. So, now I have the library that I'm recreating copied to a folder on the desktop. It could be worse. I still have my old Visual Basic macros and dialog.

load speed

It's amazing what a little change can make. I was complaining to one of my sons about how slow Open Office loaded. He went to options and unchecked Java runtime environment. Now Open Office loads much more quickly.

objects

I don't know if I just didn't know enough Visual Basic to make full use of objects. However, I'm finding that it's natural to change the find-something-or-other functions that I wrote to return an object instead of a boolean and then to check the object to see if it has the characteristic (such as the right string value) that I was searching for. (The FindFirst function that comes with OOo Basic returns an object -- a cell -- if the search was successful.) I'm also finding that I'm not creating temporary range names (as I did in VB) but instead just using oCell= or oRange= .
On another vein, I also find I'm liking using the Input function followed by a MsgBox function that says in effect "Are you sure?" as recommended in the Help info for Input function.

sorting

I was reading the OpenOffice forum message boards and saw a question about sorting on more than 3 categories. I haven't joined the forum since I know very little about Basic but thought I'd at least write down my thoughts somewhere. To sort on, say, 5 categories, first sort on the last two then sort the result on the first 3.
Finally, I'm finding that it isn't as hard as I thought to rewrite macros from Visual Basic to OOo Basic. There are a few differences to keep one on one's toes, of course. A few of the provided macro functions are different (like IFF instead of IF for the function). The workbook functions are mostly the same -- except for using semicolons instead of commas to separate elements.

Changing controls in a dialog

oControl = oDialog1.getControl("ComboBox1")
msgbox oControl.itemcount
msgbox oControl.text
oControl = oDialog1.GetControl("Label1")
msgbox oControl.text
oControl.text = "kitty"
oControl = oDialog1.GetControl("CheckBox1")
msgbox oControl.state
oDialog1Model = oDialog1.Model
oControlModel = oDialog1Model.CheckBox1
oControlModel.state = 1
oControlModel.label = "hello"

Range entries to listbox

Again, there's probably an easier way to do this, but this works to copy entries from a spreadsheet range to a listbox (or combobox) in a dialog.

rngname="Jason" 'the name of the range (one column by unknown number of rows)
oNamedRange = ThisComponent.NamedRanges.getByName(rngname)
sName = oNamedRange.getContent() 'gives full address starting with $
xPos = InStr(1,sName,".") - 2 'sheet name ends with .
sName = Mid(sName,2,xPos)
oSheet = ThisComponent.Sheets.getByName(sName)
oRange = oSheet.getcellrangebyname(rngname)
rtot=oRange.rows.count -1
dim a(rtot)
for j=0 to rtot
oCell=oRange.getcellbyposition(0,j)
a(j)=ocell.string
next
DialogLibraries.LoadLibrary( "Standard" )
oDialog1 = CreateUnoDialog( DialogLibraries.Standard.Dialog1 )
oControl = oDialog1.GetControl("ComboBox1") 'provided it already exists
oControl.additems(a(),0)

oDialog1.Execute() 'entries can be added afterwards also
'but in a separate sub

Turn to sheet having range and select range

This is not the most elegant way of doing things, but it works for turning to the sheet containing "rngname" and then selecting the range. It doesn't turn to the desired sheet until the last line.

oDoc = ThisComponent
oRanges = oDoc.NamedRanges
oNamedRange = oRanges.getByName(rngname)
oAddr = oNamedRange.getContent()
xPos = InStr(1,oAddr,".")-2
oAddr = Mid(oAddr,2,xPos)
oCells = ThisComponent.Sheets.getByName(oAddr).getCellRangeByName(rngname)
ThisComponent.getCurrentController.select(oCells)

Columns before rows

In cell addresses, the column position comes before the row position. (Both start with 0.) So, I hope these are right,
oSheet.getCellByPosition(1,3)
is equivalent to
oSheet.getCellRangeByName("B4")
To get ranges:
oRange = ThisComponent.NamedRanges.getByName("Jason")
oRange = oSheet.getCellRangeByPosition(0,0,1,3)
oRange = oSheet.getCellRangeByName("a1:b3")
These are equivalent, provided Jason is a1:b3.
temp = oCell.getSpreadSheet().getName
temp2 = oCell.CellAddress.Column
temp3 = oCell.CellAddress.Row
get the name of the spreadsheet that oCell is on, the column number, and the row number.
temp = oRange.getSpreadSheet().getName
doesn't work.
oSheet = oDoc.Sheets.getByName("Brian")
oSheet = oDoc.Sheets.getByIndex(1)
are equivalent, provided the 2nd sheet is named Brian.

Make global

Note to self: Make dialog and listbox global in order not to lose them when subs end.