| View previous topic :: View next topic |
| Author |
Message |
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 7664
Location: Kentucky, USA
|
 Posted: Thu Jan 25, 2007 12:47 pm Post subject: |
 |
|
| Quote: | | entered =FOO(B1:B5) in the spreadsheet. |
Was this a typo because it should be ("B1:B5")?
I think the remaining questions are for those more versed in Calc and math. He lost me at, "a chi-square fit to my data..." |
|
| Back to top |
|
 |
noranthon
Super User
Joined: 07 Jul 2005
Posts: 3323
|
| Posted: Thu Jan 25, 2007 3:33 pm Post subject: |
|
|
Thanks, JohnV. No, it wasn't a typo. Inverted commas are not used in Calc functions to enclose a cell range. rbroberts is after a format such as =AVERAGE(B1:B5) . Your post explains to me why your function worked for you and not for me.
Thinking I was alone in having the error messages, I sought advice in another quarter. This is the relevant part of the reply:
| Quote: | Your variable MyRange is not an object, it is an array. So the error : Object variable not set.
You cannot know the coordinates of the cell range relative to the sheet, you have only access to the values.
Here is an example of a foo function returning the sum of all cell values. Variable x scans the columns, variable y scans the rows of the range.
| Code: | Function foo(MyRange) as double
dim x as long, y as long, result as double
result = 0
for y = LBound(MyRange) to UBound(MyRange)
for x = LBound(MyRange, 2) to UBound(MyRange, 2)
result = result + MyRange(y,x)
next
next
foo = result
End Function |
|
That function enables me to obtain the desired result with =FOO(B1:B5)
_________________
search forum by month |
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 7664
Location: Kentucky, USA
|
| Posted: Thu Jan 25, 2007 7:02 pm Post subject: |
|
|
noranthon,
Now I see why we have had problems getting together and I like your way better. It makes a range like A1:B4 work well. |
|
| Back to top |
|
|
noranthon
Super User
Joined: 07 Jul 2005
Posts: 3323
|
| Posted: Thu Jan 25, 2007 7:41 pm Post subject: |
|
|
I wish it were my way. I'm still trying to get my head around how functions work and I don't see how I could have worked that one out even using Xray.
It seemed to me (the unknowing) that "Variable x scans the columns, variable y scans the rows of the range" was back to front but I checked and it's correct. So MyRange(y,x) has the row first, followed by the column - the opposite of .getCellByPosition( col, row ). How's a person supposed to cope with all these oddities? 
_________________
search forum by month |
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 7664
Location: Kentucky, USA
|
| Posted: Thu Jan 25, 2007 8:11 pm Post subject: |
|
|
| Using a range of A1:B2 where A1 = 1, A2 = 2, B1 = 3 and B2 = 4 the nested For/Next loops yield 1, 3, 2, 4. I think if the loops where reversed you would get 1, 2, 3, 4 if that helps in any manner. Guess it just depends on whether you want the data to be processed by rows or by columns. I tend to think in columns but you can see that from the way I entered my data. |
|
| Back to top |
|
|
noranthon
Super User
Joined: 07 Jul 2005
Posts: 3323
|
| Posted: Thu Jan 25, 2007 11:03 pm Post subject: |
|
|
Thanks. I understand that much. The point I don't get is why the column is element 2.
It must be the way StarBasic reads an array. Row is MyRange, 1 (unexpressed), column is MyRange, 2 (must be specified). An array is read the opposite way to a range. Mumble, mutter.
_________________
search forum by month |
|
| Back to top |
|
|
rbroberts
General User
Joined: 25 Nov 2004
Posts: 11
Location: Brooklyn, NY
|
| Posted: Mon Jan 29, 2007 12:18 pm Post subject: |
|
|
| My immediate need has passed, but I would still like to know if it possible to write a macro function which takes a cell range just like a built-in function. I'm not sure how to title such a request to attract the "right" people to answer it. Perhaps I should be asking in a developers forum? |
|
| Back to top |
|
|
noranthon
Super User
Joined: 07 Jul 2005
Posts: 3323
|
| Posted: Mon Jan 29, 2007 3:12 pm Post subject: |
|
|
I asked in such a place. An example was provided as set out above.
_________________
search forum by month |
|
| Back to top |
|
|
Danad
OOo Advocate
Joined: 22 Feb 2004
Posts: 293
Location: Brasil
|
| Posted: Tue Jan 30, 2007 8:49 am Post subject: |
|
|
noranthon,
| Quote: |
Thanks. I understand that much. The point I don't get is why the column is element 2.
|
In this case, MyRange is a 2d array of variants where the lower bound of each dimension is 1 and not 0. LBound (MyRange, 2) and UBound(MyRange, 2) returns the lower and upper bounds of 2nd dimension. When the second argument is missed, default (1st dimension) is assumed.
HTH |
|
| Back to top |
|
|
rbroberts
General User
Joined: 25 Nov 2004
Posts: 11
Location: Brooklyn, NY
|
| Posted: Tue Jan 30, 2007 1:21 pm Post subject: |
|
|
| Quote: | | I asked in such a place. An example was provided as set out above. |
Doh, sorry about not noticing that the discussion had gone to a second page.
I would say that does a pretty good job of covering what I would like to do. It would be nice if I could see cells instead of doubles since it appears that empty cells are converted to zero. But I can live with that. |
|
| Back to top |
|
|
martinw
General User
Joined: 11 May 2004
Posts: 26
Location: Uppsala, Sweden
|
| Posted: Thu Feb 01, 2007 5:18 am Post subject: More basic question... |
|
|
If I understand you discussion right you are talking about writing a function that could be used by writing =foo(...) in a calc cell. My question is - how do you make calc recognise a function written in Basic so that is is accessible the same way one of the built in functions are. Do you register it somewhere? I know this is a much more basic question than the one you are discussing, but I could not find that information anywhere else... Thanks,
Martin |
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 7664
Location: Kentucky, USA
|
| Posted: Thu Feb 01, 2007 7:56 am Post subject: |
|
|
| See Help > Index tab > functions / user defined, for an example. |
|
| Back to top |
|
|
|
