REM  *****  BASIC  *****

Private sub FindReplaceAll(findstr$, repstr$, optional myrange as string, optional regexpr as boolean )
'http://api.openoffice.org/docs/common/ref/com/sun/star/util/XReplaceable.html
' replace all findstr$ in a defined range or selected ranges on the current sheet
' replace term is   repstr$ 
' employ regular expressions if regexpr not set to false in calling routine
dim xSel,XreplaceDescr,xFound
if  myrange = 0 then
 xSel = ThisComponent.getcurrentselection
else
 xSel = Sheet.getCellRangeByName(myrange)
endif
xray ThisComponent
exit sub
 xReplaceDescr = xSel.createReplaceDescriptor()
 xReplaceDescr.SearchString = findstr$
 xReplaceDescr.ReplaceString = repstr$
 xReplaceDescr.SearchRegularExpression = regexpr 'default is true
 xSel.replaceAll( xReplaceDescr )
end sub

Private Function DropChar(str1$) As String
'drops leading character from str1 -vba
Dim ls As Long
ls = Len(str1)
If ls > 0 Then
    DropChar = Right(str1, ls - 1)
Else
    DropChar = str1
End If
End Function

Private Sub FormArray(MyStr As String, Delim As String, count As Long) as MyArray
' turns a string into an array of strings, using Delim as delimiter- vba
' delimiter follows at end of each element, including last one
' eg: FormArray(mystr, "/", mycount, anarray()) - result is in anarray, with mycount rows 
'MUCH SLOWER THAN BUILT IN SPLIT
Dim i As Long, J As Long, temp As String, k As Long
'count no of elements
count = 1
For i = 1 To Len(MyStr) - 1
    If Mid(MyStr, i, 1) = Delim And Mid(MyStr, i + 1, 1) <> Delim Then
        count = count + 1
    End If
Next i
'now place in array
ReDim MyArray(1 to count)
k = 1
For i = 1 To count
    temp = ""
    For J = k To Len(MyStr)
        If Mid(MyStr, J, 1) <> Delim Then
            temp = temp & Mid(MyStr, J, 1)
        Else
            MyArray(i) = temp

            'find start of next element by skipping multiple delimiters
Continue:   If Mid(MyStr, J + 1, 1) <> Delim Then
                k = J + 1
                Exit For
            Else
                J = J + 1
                GoTo Continue
            End If
        End If
    Next J
Next i
FormArray = MyArray
End Sub

Private Function ConvertToColumn(n As integer) As String 'sse next
'reverse of ConvertToNumber, changes integer to column reference; max jv (256)
'not nearlyas good as ColNumbertoString
Dim n1 As integer, n2 As integer, s1 As String, s2 As String
Dim base_digits As String, ms As String
Result = True
base_digits = "abcdefghijklmnopqrstuvwxyz"
Select Case n
    Case 1 To 26
        s1 = Mid(base_digits, n, 1)
    Case 27 To 256
        n2 = (n - 1) \ 26
        n1 = n - n2 * 26
        s1 = Mid(base_digits, n1, 1)
        s2 = Mid(base_digits, n2, 1)
    Case Else
        Result = False
End Select
If Result = True Then
    ConvertToColumn = s2 & s1
Else
    MsgBox ("Number reference to columns >256")
End If
End Function

'Listing 6.16: Cell address in a readable form.
'Given a cell, extract the normal looking address of a cell
'First, the name of the containing sheet is extracted.
'Second, the column number is obtained and turned into a letter
'Lastly, the row is obtained. Rows start at 0 but are displayed as 1
Function PrintableAddressOfCell(the_cell As Object) As String
  PrintableAddressOfCell = "Unknown"
  If Not IsNull(the_cell) Then
    PrintableAddressOfCell = the_cell.getSpreadSheet().getName + ":" + _
      ColumnNumberToString(the_cell.CellAddress.Column) + (the_cell.CellAddress.Row+1)
  End If
End Function

Function CountCharsinString(BigString, LocChar as String, ByVal StartPos as Integer) as Integer
Dim LocCount%, LocPos%
	LocCount = 0
	Do
		LocPos = Instr(StartPos,BigString,LocChar)
		If LocPos <> 0 Then
			LocCount = LocCount + 1
			StartPos = LocPos+1
		End If
	Loop until LocPos = 0
	CountCharsInString = LocCount
End Function