[libreoffice-users] Macro help

Hi.
I am hoping someone on this list is a lot better with accessing Calc 
functions in a macro than I am, I have have struggled with this for a 
day and resorted to a cheat in frustration.
I had an equation 300 characters long in a group of cells and decided to 
replace it with a function as it is something I use on a regular basis.

The macro (below) looks up the first column of named range RngName to 
find the position of value and interpolates between the tabulated data 
before and after value to determine an interpolated number cdgv at 
value. I have yet to add any validity checking.
The cheat I added was to pass the first column of RngName as its own 
named range for use in the MATCH function.
In a sheet I can use =MATCH(472,INDEX(CdG1data,,1)) where 472 would be 
value, CdG1data would be RngName but I just can't get this to work in my 
macro.


Function VLinterp(value, RngName, offset, firstcol)
REM firstcol is named range first column of RngName
   fc=createUnoService("com.sun.star.sheet.FunctionAccess")
   rowBefore = fc.callFunction("Match", Array(value, 
ThisComponent.NamedRanges.getByName(firstcol).ReferredCells))
   v1 = fc.callFunction("Index", 
Array(ThisComponent.NamedRanges.getByName(RngName).ReferredCells,rowBefore,1)
   v2 = fc.callFunction("Index", 
Array(ThisComponent.NamedRanges.getByName(RngName).ReferredCells,rowBefore+1,1)
   cdg1 = fc.callFunction("Index", 
Array(ThisComponent.NamedRanges.getByName(RngName).ReferredCells,rowBefore,offset)
   cdg2 = fc.callFunction("Index", 
Array(ThisComponent.NamedRanges.getByName(RngName).ReferredCells,rowBefore+1,offset)

   cdgv = cdg1+(cdg2-cdg1)*(value-v1)/(v2-v1)
   VLinterp = cdgv
End Function

Any help appreciated,
Steve

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted