Re: [libreoffice-users] Macro help

Steve, I know a little bit about macros and I am willing to take a 
look, but, I am trying to avoid having to create a sample document to 
test what you are trying to do and I have very little time.

can you send a sample Calc document to "andrew@pitonyak.org"


If possible:

1. Add your macro to the document.
2. make sure the document contains the named range with appropriate 
data.
3. Indicate exactly what parameters should be used to call the function 
and what you expected the result to be.

I know, it is a lot to ask, but otherwise I need to set all that up 
myself and I think it would be much faster for you (since you probably 
already have this).

On 06.01.2016 15:19, Steve Edmonds wrote:
> 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