sub _0_create_last_bal
' ----------------------------
'  On error exit
On Error goto Label

' define variables
Dim oDoc As Object    ' The spreadsheet we are working on
Dim oSheet As Object  ' The sheet (usually first one)
Dim oCell As Object   ' The particular cell we select
Dim oRange            ' The created range
Dim oRanges           '  All named ranges
Dim sName$            ' Name of the named range to create
msg$ = "I' m ready to accept new records: for each new one copy formula " +_
    "in last balance cell (or run macro 'create_new_rec')."

oDoc=ThisComponent
ocell=ThisComponent.CurrentSelection
oRanges = ThisComponent.NamedRanges
sName$="last_bal"

' Here we verify whether there is already a range with that name and,
' in the affirmative, erase it so that the new one can be created:
If oRanges.hasByName(sName$) Then
   oRanges.getByName(sName$)
   oRanges.removeByName(sName$)
End If

' We go to the last cell on the desired column
odoc   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
Dim args1(0) as new com.sun.star.beans.PropertyValue
' We save the file to make sure what is displayed is read correctly
' SEEMS NOT NECESSARY dispatcher.executeDispatch(oDoc, ".uno:Save", "", 0, array())
args1(0).Name = "ToPoint"
args1(0).Value = "$F$6"
dispatcher.executeDispatch(oDoc, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(oDoc, ".uno:GoDownToEndOfData", "", 0, args1())

' and check whether its value is zero
ocell=ThisComponent.CurrentSelection ' gets value of new current cell
vlc = oCell.getValue()    ' get value of selected cell (last cell)
if vlc = 0 then
   dispatcher.executeDispatch(oDoc, ".uno:Cut", "", 0, args1())
   dispatcher.executeDispatch(oDoc, ".uno:GoUp", "", 0, args1())
   ' we determine the row number of the selected cell: 
   ' numbering starts from 0, so we need add 1:
   r$ = oCell.CellAddress.row+1
   xcl$ = "Sheet1.$F$"+r$                            ' ref to col F may be changed
else
   ' This is a special case, so we need add 2
   r$ = oCell.CellAddress.row+2
   xcl$ = "Sheet1.$F$"+r$                            ' ref to col F may be changed
End if

' we set our named range name:
Dim oCellAddress As new com.sun.star.table.CellAddress
oCellAddress.Sheet = 0       ' The first sheet
oRanges.addNewByName(sName$,xcl$,oCellAddress,0)    

' We arrange for copying the formula from last used row to the next one
dispatcher.executeDispatch(oDoc, ".uno:Copy", "", 0, Args1())
Dim args2(1) as new com.sun.star.beans.PropertyValue
args2(0).Name = "By"
args2(0).Value = 1
args2(1).Name = "Sel"
args2(1).Value = false
dispatcher.executeDispatch(oDoc, ".uno:GoDown", "", 0, args2())
dispatcher.executeDispatch(oDoc, ".uno:Paste", "", 0, Args2())
dispatcher.executeDispatch(oDoc, ".uno:GoToStartOfRow", "", 0, args2())
Print msg$
Exit sub

Label:
    print "Error!"
Exit Sub

End Sub

Rem ###################################################################

Sub create_new_rec
'  we call the previous macro:
sub _0_create_last_bal
End Sub   

' #####################################################################