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 workboo
particularly when the search array is reasonably big, say of the orde
of 2000 rows by 10 columns, there is a noticeable drop in th
recalculation time.

I am advised that this is more than likely due to the overhead of usin
VBA or the premium you pay for writing them in VBA (for the ease o
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 (
can program in C relatively OK).

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

Any assistance shall be highly valued.


Best regards



Deepak Agarwa

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

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/

Hi Charles

Appreciate your quick response!

I am quite happy to use a complete C solution as you suggested
However, the central issue here is how do I pass the contents of
range of cells to a C function? This is further complicated by the fac
that this range can be multiple area range (with any number of rang
areas hence better suited for a named range with multiple range area
on one worksheet). Additionally, the cells even in one column ca
contain mixed data types. So what I have done is created a named rang
emcompassing the range of cells of interest.

With this being the fundamental requirement, is there a way to pass th
address of this Excel range to a C function. If there is, please let m
know for I do not know how to. To get around this limitation of mine
what I was trying to do was read the contents of each of the cells i
the covered range (for I am advised that Excel VBA can read cell
pretty quickly) and store them in a VBA array of variants and then pas
this array to a C function and then harness the processing speed of C t
return the post calculation value.

Any further suggestions and does this now make the requirements an
clearer?


Best regards


Deepak Agarwa

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

Hi Deepak,

To make a C function you need the Excel 97 SDK (or equivalent), which you
can find online on the Microsoft site at
http://msdn.microsoft.com/library/de...97/edkfrnt.htm
and its framework
http://download.microsoft.com/downlo...S/Frmwrk32.exe

This will tell you (amongst other things) how to pass Excel ranges to C.

I would strongly advise against using multiple area ranges as input to a UDF
as Excel has a serious bug in handling these.
(see http://www.DecisionModels.com/calcsecretsj.htm). Use multiple range
arguments instead.

for I am advised that Excel VBA can read cells pretty quickly

This is not really correct.
There is a high overhead associated with each transfer of data from Excel to
VBA.
So if you are reading cells one-by-one into VBA its is extremely slow, but
if you read a large rectangular range into a variant variable it is
relatively fast (but C is faster)
(see http://www.DecisionModels.com/calcsecretsj.htm)

If all you are doing is some kind of lookup then the process of looping
through the variant array to find the answer using VBA will be fast compared
to the time needed to read the excel range into the array.


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

"agarwaldvk " wrote in message
...
Hi Charles

Appreciate your quick response!

I am quite happy to use a complete C solution as you suggested.
However, the central issue here is how do I pass the contents of a
range of cells to a C function? This is further complicated by the fact
that this range can be multiple area range (with any number of range
areas hence better suited for a named range with multiple range areas
on one worksheet). Additionally, the cells even in one column can
contain mixed data types. So what I have done is created a named range
emcompassing the range of cells of interest.

With this being the fundamental requirement, is there a way to pass the
address of this Excel range to a C function. If there is, please let me
know for I do not know how to. To get around this limitation of mine,
what I was trying to do was read the contents of each of the cells in
the covered range (for I am advised that Excel VBA can read cells
pretty quickly) and store them in a VBA array of variants and then pass
this array to a C function and then harness the processing speed of C to
return the post calculation value.

Any further suggestions and does this now make the requirements any
clearer?


Best regards


Deepak Agarwal


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