Hi, just converting from Excel to Calc, so if this question is simple, I apologize. Also new to forum, I’m still learning how to use the forum also. But I’m stuck.
Trying to call the offset function through a uno service callfunction in a UDF, but keep getting an object undefined runtime error. Strange thing is, I have very similar code in other functions that work as expected.
Things I’ve tried:
- Copied and then modified working code from working routines.
- Turned on Option Explicit as recommended on several pages. And defined everything.
- Added watches on all the objects involved and added a breakpoint to the line with the error so I could check the objects were initialized.
- Added some simple debugging code just before the error line and verified I could access the objects. (If that messed things up, realize the line was erroring before they were added.)
- Have tried using set in the lines were each object is defined. No change. Also not used in the working functions.
- Have tried defining (dim) the objects both as objects and variants with no change to the error behavior.
Here is the code:
'*************************************************************************************************************
Function GetYearRow(Yr as double, optional sRangeName as string) as integer
dim oFuncAccess as object
dim oRanges as object
Dim oNamedRange As Object
dim oRange as variant
oRanges = ThisComponent.NamedRanges
if ismissing(sRangeName) then sRangeName = "TaxYears"
oRange = oRanges.getByName(sRangeName).getReferredCells().getdataarray()
oFuncAccess = createUnoService("com.sun.star.sheet.FunctionAccess")
GetYearRow=oFuncAccess.CallFunction("MATCH",array(Yr,oRange,0))
'The above call works.
end function
'*************************************************************************************************************
Function GetTaxTableColumn(rw as integer, Status as string, TaxIncome as double) as integer
'=IF(F8="MFJ",MATCH(M8,OFFSET(MFJTIThresholds ,N8,0,1)),MATCH(M8,OFFSET(SingleTIThresholds,N8,0,1))) Formula that works in calc that I'm trying to mimic here.
dim oFuncAccess as object
dim oRanges as object
dim oRange as variant
dim sRangeName as string
dim YearsTIThresholds as variant
oRanges = ThisComponent.NamedRanges
if Status="MFJ" then sRangeName="MFJTIThresholds" else sRangename="SingleTIThresholds"
oRange=oRanges.getByName(sRangeName).getReferredCells().getdataarray()
oFuncAccess = createUnoService("com.sun.star.sheet.FunctionAccess")
YearsTIThresholds=oFuncAccess.CallFunction("OFFSET",array(oRange,rw,0,1))
'The above call works.
GetTaxTableColumn=oFuncAccess.CallFunction("MATCH",array(TaxIncome,YearsTIThresholds))
'The above call works.
end function
'*************************************************************************************************************
Option explicit
Function Taxes(TaxableIncome as Double,Status as String, Year as Integer) as Double
dim oFuncAccess as object
dim oRanges as object
dim oRange as variant
dim Yrw as variant
dim MargRate as double
dim sRangeName as string
dim TaxColumn as integer
dim test as double
YRw=GetYearRow(Year)
TaxColumn=GetTaxTableColumn(YRw, Status, TaxableIncome)
'=OFFSET(MFJMarginalRates,N8,O8-1,1,1) Formula that works in calc that I'm trying to mimic here.
oRanges = ThisComponent.NamedRanges
oRange=oRanges.getByName("MFJMarginalRates").getReferredCells().getdataarray()
oFuncAccess = createUnoService("com.sun.star.sheet.FunctionAccess")
'Next few lines are debugging code to assess which object is not defined. Both appear to be defined.
test=oRange(0)(0)
'Above is testing if oRange is initialized. Note I'm able to access values in oRange and it appears as an object in the watch window so it appears initialized to me.
test = oFuncAccess.CallFunction( "PI",array())
'returns value of pi so looks like oFuncAccess is defined, and it appears as an object in the watch window also.
'back to code that initially had error.
'Next code line generates object not initialized runtime error. I see only 2 objects involved, oRange and oFuncAccess, both of which seem to be initialized.
'Note the next line (and object initialization) is nearly identical in structure to code in GetTaxTableColuimn above which works.
'Have tried using set on where the two objects are defined, no change, and not required in working GetTaxTableColumn function.
'Have tried defining both objects as objects (as they are in the other function) and as variants, no change.
MargRate = oFuncAccess.CallFunction("OFFSET",array(oRange,YRw,TaxColumn-1))
'The above call generates a runtime object not defined error.
'=IF(F8="MFJ",OFFSET(MFJTIThresholds,N8,O8-1,1,1),OFFSET(SingleTIThresholds,N8,O8-1,1,1)) Formula that works in calc that I'm trying to mimic here.
if Status="MFJ" then sRangeName="MFJTIThresholds" else sRangeName="SingleTIThresholds"
oRange=oRanges.getByName(sRangeName).getReferredCells().getdataarray()
Threshold=oFuncAccess.CallFunction("OFFSET",array(oRange,YRw, TaxColumn-1,1,1))
'=IF(F8="MFJ",OFFSET(MFJTaxAtThreshold ,N8,O8-1,1,1),OFFSET(SingleTaxAtThreshold ,N8,O8-1,1,1)) Formula that works in calc that I'm trying to mimic here.
if Status="MFJ" then sRangeName="MFJTaxAtThreshold" else sRangeName="SingleTaxAtThreshold"
oRange=oRanges.getByName(sRangeName).getReferredCells().getdataarray()
Threshold=oFuncAccess.CallFunction("OFFSET",array(oRange,YRw, TaxColumn-1,1,1))
Taxes=Threshold+MargRate*(TaxableIncome-Threshold)
End Function
'*************************************************************************************************************
The line where the error occurs is:
MargRate = oFuncAccess.CallFunction("OFFSET",array(oRange,YRw,TaxColumn-1))
in the last function. Also tried it with the two optional parameters (,1,1).
To me this appears to use two objects, oFuncAccess and oRange.
Wanted to include screenshots of the watch window FYI, but can’t figure out how to. (Newbie problems)
With the function at a breakpoint at the error generating line (before the error) the watch window shows:
oFuncAccess is showing as type com.sun.star.sheet.FunctionAccess. It also contains several items whose values seem reasonable.
oRange is showing as a Object(0 to 40). Each item is a variant(0 to 6) and each item in that is a variant/double as expected.
In the code I’ve added debugging lines just before the call where I access one of the values in oRange and call another function from oFuncAccess (“PI”), which returns the expected value.
So both objects involved in the call seem initialized to me.
I’m stuck, I can’t see what I’m doing wrong or different from the working functions. Any help the forum can provide would be greatly appreciated.
Thansk
