To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=63614 Issue #:|63614 Summary:|IIF is fragile in StarBasic Component:|framework Version:|OOo 2.0.2 Platform:|All URL:|http://www.oooforum.org/forum/viewtopic.phtml?t=33910 OS/Version:|All Status:|NEW Status whiteboard:| Keywords:| Resolution:| Issue type:|DEFECT Priority:|P3 Subcomponent:|scripting Assigned to:|npower Reported by:|pitonyak
------- Additional comments from [EMAIL PROTECTED] Sat Mar 25 05:14:52 -0800 2006 ------- I have had macros that use IIF that will suddenly fail for no particular reason. Recently, I ran accross a great example of this. The macro is shown here: http://www.oooforum.org/forum/viewtopic.phtml?t=33910 Create a new module and add the macro (which I will append at the bottom of this post). The macro works just fine. Now, add a simple comment near the top. Change the top comment from one line to two REM making the use of calc function library within OOBasic easier REM Does this mess things up? Now, run the macro again and it will fail just before the IIF statement. Now, change the IIF statement from tempVec2 = DimArray( IIf( nCols = 0, 0, nCols - 1 ) ) to If nCols = 0 Then tempVec2 = DimArray( 0 ) Else tempVec2 = DimArray( nCols - 1 ) End If Now, the macro will work. REM ***** BASIC ***** Option Explicit REM making the use of calc function library within OOBasic easier Sub testCalcFunctions Dim a(1), b(1) a(0) = 1 : a(1) = 2 b(0) = 3 : b(1) = 4 MsgBox "Dot product is " & fnCalcFunction( "SUMPRODUCT", Array( a(), b() ) ) Dim x As Integer x = 5 MsgBox "Factorial of " & x & " is " & fnCalcFunction( "FACT", Array( x ) ) Dim tempArray(1,1) As Variant tempArray(0,0) = 0 : tempArray(0,1) = 1 tempArray(1,0) = 2 : tempArray(1,1) = 3 Dim funcResult() As Variant funcResult() = fnCalcFunction( "TRANSPOSE", Array( tempArray() ) ) MsgBox "Transpose of" & chr(13) & _ tempArray(0,0) & ", " & tempArray(0,1) & chr(13) & _ tempArray(1,0) & ", " & tempArray(1,1) & chr(13) & _ "is" & chr(13) & _ funcResult(0,0) & ", " & funcResult(0,1) & chr(13) & _ funcResult(1,0) & ", " & funcResult(1,1) MsgBox "Determinate is " & fnCalcFunction( "MDETERM", Array( tempArray() ) ) End Sub REM The following are trivially simple examples of user defined functions REM which can be used as formulas in a spreadsheet cell. Function fnAbs( a ) fnAbs() = fnCalcFunction( "ABS", Array( a ) ) End Function Function fnTranspose( a() ) fnTranspose() = fnCalcFunction( "TRANSPOSE", Array( a() ) ) End Function Function fnDot( a(), b() ) fnDot() = fnCalcFunction( "SUMPRODUCT", Array( a(), b() ) ) End Function REM ############################################################## REM ############################################################## REM ############################################################## REM REM The below functions provide the basic utilities for conveniently REM using calc functions (including array functions) within OOBasic. REM REM ############################################################## REM ############################################################## REM ############################################################## Function fnCalcFunction( sFunc As String, args() ) As Variant REM this function is just a utility function for making the REM FunctionAccess service easier to use. REM first, if any of the argumenst are arrays, convert them to REM vectors of vectors Dim i As Integer For i = LBound( args() ) To UBound( args() ) If IsArray( args( i ) ) Then args( i ) = fnBasicArrayToFuncArray( args( i ) ) End If Next Dim myFuncAccess As Variant, myFuncResult As Variant myFuncAccess = createUNOService( "com.sun.star.sheet.FunctionAccess" ) myFuncResult = myFuncAccess.callFunction( sFunc, args() ) REM now restore any of the array arguments back to normal arrays For i = LBound( args() ) To UBound( args() ) If IsArray( args( i ) ) Then _ args( i ) = fnFuncArrayToBasicArray( args(i) ) Next REM and if the result is an array, convert it from a vector of REM vectors to a normal array If IsArray( myFuncResult ) Then _ myFuncResult = fnFuncArrayToBasicArray( myFuncResult() ) fnCalcFunction() = myFuncResult End Function REM ############################################################## REM ############################################################## REM ############################################################## Function fnBasicArrayToFuncArray( a() ) As Variant REM this function expects to receive a normal OOBasic array such as REM one would create with "Dim a(3)" or "Dim a(2,3)". The function REM then converts this to a "vector of vectors" which is what REM calc uses internally for ranges of cells (data arrays). It is REM these "vectors of vectors" that calc's FunctionAccess service REM is expecting to receive for arrays. REM all the LBound calls in here are to bulletproof against REM non-zero-based arrays. This is especially important because REM calc passes in ones-based arrays Dim nRows As Integer, nCols As Integer nRows = 0 : nCols = 0 On Local Error Resume Next nRows = UBound( a() ) - LBound( a() ) + 1 nCols = UBound( a(), 2 ) - LBound( a(), 2 ) + 1 On Local Error GoTo 0 Dim i As Integer, j As Integer Dim tempVec1( 0 To nRows - 1 ) As Variant Dim tempVec2() As Variant For i = LBound( a() ) To UBound( a() ) REM Since arrays copy by reference instead of value, the following REM DimArray statement is required to create a new tempVec2 each REM time we loop. By the way, DimArray is guaranteed to be zero based. tempVec2 = DimArray( IIf( nCols = 0, 0, nCols - 1 ) ) If nCols > 1 Then For j = LBound( a(), 2 ) To UBound( a(), 2 ) tempVec2( j - LBound( a(), 2 ) ) = a( i, j ) Next ElseIf nCols = 1 Then tempVec2(0) = a( i, 1 ) Else tempVec2(0) = a( i ) End If tempVec1( i - LBound( a() ) ) = tempVec2() Next REM now return the vector of vectors fnBasicArrayToFuncArray() = tempVec1() End Function REM ############################################################## REM ############################################################## REM ############################################################## Function fnFuncArrayToBasicArray( a() ) As Variant REM This function takes a "vector of vectors" and returns a normal REM OOBasic two-dimensional array. The LBound stuff makes it read REM more complicated than it should as I try to bulletproof against REM someone using non-zero-based arrays. Dim nRows As Integer, nCols As Integer nRows = UBound( a() ) - LBound( a() ) + 1 nCols = UBound( a( LBound( a() ) ) ) - LBound( a( LBound( a() ) ) ) + 1 Dim tempArray( 0 To nRows - 1, 0 To nCols - 1 ) As Variant Dim tempCols() As Variant Dim i As Integer, j As Integer For i = 0 To nRows - 1 tempCols() = a( i ) For j = 0 To nCols - 1 tempArray( i, j ) = tempCols( j ) Next Next fnFuncArrayToBasicArray() = tempArray() End Function --------------------------------------------------------------------- Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
