The fastest VBA?

[eros]

Guys,


I have a speed and performance issue.


I use an Excel sheet to read live data feed from a server. I run a lot of functions which are called from many and different cells as the live data is updated, say, hundreds of times a second.


I am afraid I will be facing speed and performance issues when I need to add more functions which need to work simultaneously together with the previous hundreds of functions.


Therefore, I am looking to a way to speed up my VBA code. I have to use Excel sheets and therefore I am stuck to Excel VBA ver 6.5 as I use Office 2003.


Any suggestions as to how I could improve the performance and speed of my code? I feel there is a bottleneck with Excel as there has to be a max limitation as to how many functions can be called if cells values change simultaneously. Is there a best practices or common mistakes guide for efficient VBA coding? Will there be any performance improvement if I use Visual Studio 2011 instead of VBA 6.5? But bear in mind I still need to use Excel worksheets. What about other languages C#, J# and the like as alternatives to VBA? Which one is faster?


Many thanks in advance

 

[Peter_SSs]

If your vba code isn't too long, the best way to get advice on improving it would be to post it here. If you do, please use Code Tags - see my signature block.

Without the code, only very general advice can be given and it may not all apply to your situation. Here are a few ideas that can mostly be employed to speed code:

1. Turn off ScreenUpdating while the code is running (& turn it back on again at the end).

2. Turn off automatic calculation while the code is running (& turn it back on again at the end).

3. Do not 'Select' things to work with them.

4. Keep interaction between the code and the worksheet to a minimum. One way may be to read the relevant worksheet values in to an array and work with the array then just put any results back on the worksheet.