Re: Fastest way to work with large ranges in UDF's called MANY times.

[Govert van Drimmelen]

Hi Brett,

Indeed, I think there is some overhead in getting the large double[,]
array for every call.

You declare the parameter for the large range as
<ExcelArgument(AllowReference:=True)> myRef As Object
and then your code should check whether the value passed in is an
ExcelReference, from which you can get the actual value with a call to
myRef.GetValue(). But the problem then is that you would not know
whether the data in myRef has changed in the mean time, so you can't
reliably cache the last values between computations. There might be
some way to keep track of the changes to that range, but I can't see
an easy way to make it fast.

I would suggest changing your function into an array function:

=INTERPOLATETABLEVALUES(LookupValues, range, column)

where LookupValues is an array of values to look up (not just one
value), and you return an array of looked up results.
It would be a bit less convenient to use, but you only do the
marshaling once.

Regards,
Govert


On Feb 24, 9:33 pm, Brett Olson <brettol...@cox.net> wrote:
> Hi Everyone,
>
> I'm new to ExcelDna, but I'm confused on the fastest way to pass range data
> from Excel to a UDF (MANY times).
>
> SETUP:
> ------------------------------------------------
> For simplicity (i'll have much more complicated UDF's, but this is a simple
> case example) lets assume I'm creating an interpolate table UDF that takes
> three arguments =INTERPOLATETABLE(LookupValue, range, column). The
> LookupValue is a double, the range can be upwards of 30,000 rows x 10
> columns range address, the column argument is an integer of the column to
> interpolate. This is similar to Excel's LOOKUP function, but will also
> interpolate values.
>
> So... Let's assume I want to interpolate 24.56, from a 6x3 lookup range,
> and I want to interpolate from the second column of the table. The answer
> would be 245.6 (just an easy example). The answer for using the third
> column would be 2.456.
>
> 10 100 1
> 20 200 2
> 30 300 3
> 40 400 4
> 50 500 5
> 60 600 6
>
> PROBLEM:
> -----------------------------------------------
> I already have a VBA version that works, I'm porting into vb.net and
> ExcelDna. However, the VBA version runs faster than the ExcelDna. I've
> written a Binary search algorithm in .Net to do the actual searching of the
> double(,) array that I've passed in from Excel. I've timed the UDF for the
> searching and interpolating, and it's extremely fast (like 20 to 40
> stopwatch.elapsed.ticks which is what?, 40/10,000 of a millisecond). So I
> have no issues with the actual UDF. HOWEVER, it still takes 10-12 Seconds
> if I run 10,000 function calls on a 30,000x3 range. I really like the idea
> of getting all my Excel dependent UDF's into a .Net version and want to
> re-write my UDF's to work with arrays, rather than ranges.
>
> I'm assuming the overhead is from having to pass in a double(30,000,3)
> array for each of the 10,000 function calls. Correct?
>
> Therefore, I'm just confused when I start reading all of the posts and docs
> on using <ExcelArgument(allowreference:=True)>  myRange As Object, or using
> the COM model or C API calls.
>
> I guess my question is this (sorry for all the blabber): Passing double(,)
> is essentially passing byVal, is there a way to pass a reference to the
> actual range so when I call it 10,000 times it doesn't take so long?
>
> Some of the posts I've been reading suggest to limit the number of calls to
> the function, but this is kind of a reverse issue where the user will have
> a small number of very large tables, and a large number of function calls
> that use those tables as arguments.
>
> Thanks,
>
> Brett