Performance question
 

Hi,

I'm develop a model in Excel that uses some fairly computationally
expensive routines.

My current implementation is just plain VBA. Though not particularly
efficient, it is reasonably elegant and does get the job done.

I am curious to know what kind of performance boost I will get if I
re-write the model in C# and use it as an Automation Add-in.

Performance question
 

If you are looking to improve the efficiency of your current code, then it
would depend on what your code does.
If you are looking to make the "computationally expensive routines"
asynchronous, then...

I don't use C#/.Net but AFAIK as long as you can control the threading, so
your calculation are performed on a different thread, your code can return
to it's other processing.
When the calculation is complete some means of callback/raising event is
required to notify you/Excel.

In VB6, you could create an ActiveX exe (rather than a DLL), although in C#
you may have more options.
These will get you started:
http://support.microsoft.com/kb/302901

http://msdn2.microsoft.com/en-us/lib...19(VS.80).aspx

You could also user a timer based method to run asynchronous code, but I've
never tried in VBA:
http://www.vbwm.com/art_2000/async1.asp

http://www.codeproject.com/vbscript/AsyncSample.asp

NickHK

"Schizoid Man" wrote in message
...
Hi,

I'm develop a model in Excel that uses some fairly computationally
expensive routines.

My current implementation is just plain VBA. Though not particularly
efficient, it is reasonably elegant and does get the job done.

I am curious to know what kind of performance boost I will get if I
re-write the model in C# and use it as an Automation Add-in.

Performance question
 

NickHK wrote:
If you are looking to improve the efficiency of your current code, then it
would depend on what your code does.
If you are looking to make the "computationally expensive routines"
asynchronous, then...

I don't use C#/.Net but AFAIK as long as you can control the threading, so
your calculation are performed on a different thread, your code can return
to it's other processing.
When the calculation is complete some means of callback/raising event is
required to notify you/Excel.

In VB6, you could create an ActiveX exe (rather than a DLL), although in C#
you may have more options.
These will get you started:
http://support.microsoft.com/kb/302901

http://msdn2.microsoft.com/en-us/lib...19(VS.80).aspx

You could also user a timer based method to run asynchronous code, but I've
never tried in VBA:
http://www.vbwm.com/art_2000/async1.asp

http://www.codeproject.com/vbscript/AsyncSample.asp

Hi Nick,

Thanks for the tip. The code performs recursion a number of times on a
pretty large data set (1500 doubles), numerical integration and some
statistics.

I've found that repeated calls to functions like NORMSINV() and
NORMSDIST() can be very expensive and I'm keen to optimize my model some.

Performance question
 

If you feel you can make a better implementation of those algorithms then
see how then they perform compared to Excel.
If it is feasible to scale your doubles to longs, perform calculations and
scale back, you may see an improvement.

NickHK

"Schizoid Man" wrote in message
...
NickHK wrote:
If you are looking to improve the efficiency of your current code, then
it
would depend on what your code does.
If you are looking to make the "computationally expensive routines"
asynchronous, then...

I don't use C#/.Net but AFAIK as long as you can control the threading,
so
your calculation are performed on a different thread, your code can
return
to it's other processing.
When the calculation is complete some means of callback/raising event is
required to notify you/Excel.

In VB6, you could create an ActiveX exe (rather than a DLL), although in
C#
you may have more options.
These will get you started:
http://support.microsoft.com/kb/302901

http://msdn2.microsoft.com/en-us/lib...19(VS.80).aspx

You could also user a timer based method to run asynchronous code, but
I've
never tried in VBA:
http://www.vbwm.com/art_2000/async1.asp

http://www.codeproject.com/vbscript/AsyncSample.asp

Hi Nick,

Thanks for the tip. The code performs recursion a number of times on a
pretty large data set (1500 doubles), numerical integration and some
statistics.

I've found that repeated calls to functions like NORMSINV() and
NORMSDIST() can be very expensive and I'm keen to optimize my model some.

Performance question
 

Schizoid Man wrote:
NickHK wrote:
If you are looking to improve the efficiency of your current code, then it
would depend on what your code does.
If you are looking to make the "computationally expensive routines"
asynchronous, then...

I don't use C#/.Net but AFAIK as long as you can control the threading, so
your calculation are performed on a different thread, your code can return
to it's other processing.
When the calculation is complete some means of callback/raising event is
required to notify you/Excel.

In VB6, you could create an ActiveX exe (rather than a DLL), although in C#
you may have more options.
These will get you started:
http://support.microsoft.com/kb/302901

http://msdn2.microsoft.com/en-us/lib...19(VS.80).aspx

You could also user a timer based method to run asynchronous code, but I've
never tried in VBA:
http://www.vbwm.com/art_2000/async1.asp

http://www.codeproject.com/vbscript/AsyncSample.asp

Hi Nick,

Thanks for the tip. The code performs recursion a number of times on a
pretty large data set (1500 doubles), numerical integration and some
statistics.

I've found that repeated calls to functions like NORMSINV() and
NORMSDIST() can be very expensive and I'm keen to optimize my model some.

The code for NORMSINV is known to be slow ( see
http://groups.google.co.uk/group/mic...7d407a71ed329/
). AS241 is much faster and more accurate.

Ian Smith