Hi Deepak,

There is a significant overhead in transferring data from an Excel range to
a VBA array.
VBA is actually quite fast at referring to arrays (although not as fast as
C).

If you want to use C then I would recommend using a complete C solution with
the C API rather than using VBA which then calls C.

Check out http://www.DecisionModels.com/Calcsecretsj.htm for some tips,
examples and discussion on speeding up VBA worksheet functions. Maybe you
can speed up your VBA function without using C.

regards
Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com


"agarwaldvk " wrote in message
...
Hi All

I have written a couple of custom functions for use in Excel using VBA.
Whilst the time taken to execute them individually is not awfully long,
when I have reasonably extensive use of them in any workbook
particularly when the search array is reasonably big, say of the order
of 2000 rows by 10 columns, there is a noticeable drop in the
recalculation time.

I am advised that this is more than likely due to the overhead of using
VBA or the premium you pay for writing them in VBA (for the ease of
coding in VBA particularly in the handing of variant data types)
against writing them in C++ or C for that matter.

If this is the case, I would like to rewrite these functions in C (I
can program in C relatively OK).

I would need to read a range of cells from a worksheet and transfer the
cell contents in to an array of variants. I would now need to pass this
array just created to a C function that I would call from another VBA
custom function - the idea being to do the processing of data in C
rather than in VBA with the called C function returning a double
values.

Any assistance shall be highly valued.


Best regards



Deepak Agarwal


---
Message posted from http://www.ExcelForum.com/