OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

is there an equivalent to excels range objects?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
Lorenz
Guest





PostPosted: Thu May 13, 2004 11:03 am    Post subject: is there an equivalent to excels range objects? Reply with quote

Hi all,

in Excel you can have

Code:

function foo (r as range)
  rem do something using r
end function



is the an equivalent expression in OOoC?


TIA
Lorenz
Back to top
geoff_f
OOo Enthusiast
OOo Enthusiast


Joined: 21 Nov 2003
Posts: 181
Location: Canberra, Australia

PostPosted: Fri May 14, 2004 3:13 am    Post subject: Reply with quote

In OOo Calc, a range of cells is an Object, obtained by:

Code:
Dim oDoc as Object            'The document model
Dim oSheet as Object         'The sheet containing the cell
Dim oCell As Object         'A single cell
Dim oCells as Object         'A range (collection) of cells
Dim iSomeValue As Integer   'An integer value
Dim sSomeString As String   'A string value

oDoc = ThisComponent
oSheet = oDoc.Sheets.getByName("Sheet1")      'or, .getByIndex(0)
oCell = oSheet.getCellRangeByName("A2")         'or, .getCellByPosition(0, 1) - Col, then Row
'Or:
oCell =oSheet.getCellRangeByName("MyCell")      'Where 'MyCell' is a named range defined for A2

iSomeValue = 15
iSomeString = "A string"

'Then get or set contents of the cell
oCell.setValue() = iSomeValue            'Store the value 15 into cell A2
oCell.setString() = iSomeString         'Store 'A string' into cell A2

'Equally valid:
oCell.setValue() = 30                  'Store the value 30 into cell A2
oCell.setString() = "Another string"   'Store 'Another string' into cell A2

'Then:
iSomeValue = oCell.getValue()         'iSomeValue now holds the value 30
iSomeString =oCell.getString()         'iSomeString now holds 'Another string'

'With a range of cells:
oCells =oSheet.getCellRangeByName("A2:B4")
'Or:
oCells = oSheet.getCellRangeByName("MyCells")   'Where 'MyCells' is a named range for A2:B4

'Then, using .getCellByPosition() as an equivalent to Excel's .Offset method:
oCell = oCells.getCellByPosition(0, 0)      'This gives cell A2
oCell = oCells.getCellByPosition(1, 2)      'This gives cell B4

Unlike Excel's .Offset, this last method can't be used to access a cell outside the range defined by oCells (ie, A2:B4 in this case). You either have to make sure your oCells definition contains the cell you seek, or access it through the absolute address method:
Code:

oCell = oSheet.getCellByPosition(2, 4)      'This will give cell C5

So your function would look something like this:
Code:

function foo (oCell As Object) As Integer      'or oCells As Object, if multiple cells
Dim iSomeValue As Integer
   rem do something using oCell
   iSomeValue = oCell.getValue()
   foo = iSomeValue
end function

BTW, this would have been better posted in the Macros and API forum.

HTH.

<edit> typo: In function foo(), changed iCell.getValue() to oCell.getValue() </edit>


Last edited by geoff_f on Sun Jul 04, 2004 3:58 am; edited 1 time in total
Back to top
View user's profile Send private message
r0bber
General User
General User


Joined: 03 Jul 2004
Posts: 14
Location: Brisbane, Australia

PostPosted: Sun Jul 04, 2004 1:17 am    Post subject: Reply with quote

I dont think this is quite what the original poster needs.

In place of a Range object, OOCalc passes an 2D array. Note the 2D! As Danny points out elsewhere, it is ALWAYS a 2D matrix, even if only 1 column.

eg
Code:
function foo (r as array)
   rem do something using r
  dim c as integer, oAS as object,r1 as integer,r2 as integer,c1 as integer,c2 as integer
  dim  ir as integer, ic as integer
  r1=lbound(a,1):r2=ubound(a,1): c1=lbound(a,2):c2=ubound(a,2)
  for ir=r1 to r2
      for ic=c1 to c2
          Msgbox "R" & str(ir) & "C" & str(ic) & "!" & typename(a(ir,ic)) & "!" & a(ir,ic) & "!"
      next ic
  next ir
end function


I have a problem with this in that I cant determine if any of the cells are empty as thy get returned as numeric zero.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group