Joined: 31 Jan 2011
|Posted: Mon Jan 31, 2011 10:05 pm Post subject: Recursively relocate a named range
Newbie here, converting an exotic application in Excel/VBA to Calc/StarBasic. The application is an automatic Euler simulation that is periodically sampled in terms of variables residing in a range called “Buffer”. When the sampling occurs, the contents of “Buffer” are saved in another range called “NLINE”. After the sampling, “NLINE” must be deleted from the NamedRanges and recreated in the next row down. After the simulation has ended, we are left with a stack of the variables that were in “Buffer” at each of the sampling intervals; and each column in the stack is a time series that is used for plotting.
The spool macro below is functional except that I need a technique for extracting the information required to determine the address of the new “NLINE” from the address of the current “NLINE” before I delete it. As you can see, I need to compute the row number nRow and row name sRow$ of the new “NLINE”. I know it should be easy; but I can’t seem to find the relevant documentation.
' Spool Macro
Dim oSheet, oAllData as Object
Dim oRangeB, oRangeN as Object
Dim oRanges as Object
Dim sName$, sRow$
Dim nRow as Integer
Dim oCellAddress As new com.sun.star.table.CellAddress
'Rem: record contents of Buffer in NLINE
oSheet = ThisComponent.Sheets.getByName("Series")
oRangeB = oSheet.getCellRangeByName("Buffer")
oAllData = oRangeB.getDataArray()
oRangeN = oSheet.getCellRangeByName("NLINE")
'Rem: develop parameters needed to re-create NLINE
' one row below its current location
sName$ = "NLINE"
oRange = ThisComponent.NamedRanges.getByName(sName$)
nRow = 8
sRow$ = "9"
s$ = "$Series.$C$"+sRow$+":$N$"+sRow$
'Rem: delete and re-create NLINE
oRanges = ThisComponent.NamedRanges
oCellAddress.Sheet = 3 'The "Series" sheet.
oCellAddress.Column = 2 'Column C.
oCellAddress.Row = nRow 'Row . . .
oRanges.addNewByName(sName$, s$, oCellAddress, 0)