Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Performance | Excel Discussion (Misc queries) | |||
Performance IF(IF or IF(AND | Excel Worksheet Functions | |||
XLL performance in NT and XP | Excel Programming | |||
performance question | Excel Programming | |||
Bad performance | Excel Programming |