Bernard Marcelly wrote:
Message de TerryJ date 2007-01-25 14:48 :
I've had this problem for some time but it was highlighted today by
my experience with a working function posted in the forum (
http://www.oooforum.org/forum/viewtopic.phtml?p=203524#203524 ):
Function foo(MyRange)
da =
ThisComponent.CurrentController.ActiveSheet.getCellRangeByName(MyRange).getDataArray()
(...)
I entered a formula in the correct format in the spreadsheet, viz:
=foo(B1:B5) which generated two identical error messages "Basic
runtime error. Object variable not set". The line raising the error
is the first line in the script.
Hi,
The above thread is a complete mess.
Functions for Calc do not work like functions for Excel, except
perhaps if you use a Novell version of OpenOffice.
When you enter a cell range in the formula, this is converted to an
Array of Variant of two dimensions (a rectangular matrix). You always
have two dimensions, even if the cell range is only a column or a row.
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.
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
Regards
Bernard
Thank you very much for your reply. That function works (of course) in
my spreadsheet, which is a relief. What puzzled me was that the
function in the forum thread appears to work for others. I will study
this question further.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
