Forum at OOoForum.orgThe 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 Forum Index -> Macros and API
View previous topic :: View next topic  
Author Message

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


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

is the an equivalent expression in OOoC?

Back to top
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:

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
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

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")
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:

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

So your function would look something like this:

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.


<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
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.

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 Forum Index -> 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