Date: prev next · Thread: first prev next last
2016 Archives by date, by thread · List index

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
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,

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.