Re: Functions working for others produce "object variable not set"

Thread: Re: Functions working for others produce "object variable not set"

Search this list only Search all lists
Re: Functions working for others produce "object variable not set"
	2007-01-25 11:22:29
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 --------------------------------------------------------------------- To unsubscribe, e-mail: dev-unsubscribeapi.openoffice.org For additional commands, e-mail: dev-helpapi.openoffice.org
Re: Functions working for others produce "object variable not set"
	2007-01-25 16:55:55
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: dev-unsubscribeapi.openoffice.org For additional commands, e-mail: dev-helpapi.openoffice.org