VBA speed issues
-
The Apocalyptic Teacup wrote:
I'll fire up VB Express tonight and see if I can build the DLL.
Let us all know how it goes. Help is always here :-D
Paul Conrad wrote:
Let us all know how it goes. Help is always here
Hi Paul, Although I haven't actually built the DLL (I will reserve that for tomorrow as I'm working on Paris time), I did open up VB Express and there is indeed the ability to build a DLL. Looks like there is a solution that doesn't involve porting my existing code to C#. Will be quite the time-saver.
"Outlook not so good." That magic 8-ball knows everything! I'll ask about Exchange Server next.
-
I've written a calculation-intensive application under VBA for Excel. The program is non-trivial and quite large. It is currently being used for complicated financial calculations. The problem is that it is very slow. I have gone through the code and made many tweaks to increase the speed (reduced array re-dimensions, eliminated redundant and/or repetitive function calls, etc...) and the speed gains do not improve things "enough" for my liking. Thus, I have decided to re-write the program, a non-trivial exercise. Now, I am a c++ programmer so ideally I would re-write this in Visual C++ or something, but someone has suggested importing it into Visual Basic. My question is: will this be any faster? I am not sure if VBA is basically interpreted rather than compiled and thus slower than, say, a compiled Visual Basic app. Is this indeed the case, or will I be wasting time importing the VBA code to Visual Basic? If so, then the solution appears to be C++ despite the headache of interfacing VC++ with Excel. Any advice would be appreciated.
"Oh, I must've did somebody some good. I think I did. So I gave her the gun and I shot her!" - Led Zeppelin - In My Time of Dying
I would suggest that if you are sticking with your database in Excel, then VBA is almost as suitable as any language to process the data, unless the language functionalities of C++ can better improve perfomance (considering re-writing the code in an object orientated environment) But accessing data such as Excel databases, can decrease performance if it is called by an external application. Another thing that also i personally find in VBA, is that it is very memory intensive and that may effect the speed of the data processing. An ideal solution would be if you manage to migrate out of Excel, into a more scalable database solution, such as an SQL Server, or at least MS Access. Thus an external application can make multiple calls to data in a more optimized way. Of cource i am saying all this without an idea of exactly how or how much data is being processed. Personally, i like the combination of VB.NET and SQL Server, for an easy to use, scalable and secure environment. Hope i could help. p.s i wrote all that and then turned the page to see the replies you already got :P
-
I would suggest that if you are sticking with your database in Excel, then VBA is almost as suitable as any language to process the data, unless the language functionalities of C++ can better improve perfomance (considering re-writing the code in an object orientated environment) But accessing data such as Excel databases, can decrease performance if it is called by an external application. Another thing that also i personally find in VBA, is that it is very memory intensive and that may effect the speed of the data processing. An ideal solution would be if you manage to migrate out of Excel, into a more scalable database solution, such as an SQL Server, or at least MS Access. Thus an external application can make multiple calls to data in a more optimized way. Of cource i am saying all this without an idea of exactly how or how much data is being processed. Personally, i like the combination of VB.NET and SQL Server, for an easy to use, scalable and secure environment. Hope i could help. p.s i wrote all that and then turned the page to see the replies you already got :P
ad3z wrote:
But accessing data such as Excel databases, can decrease performance if it is called by an external application. Another thing that also i personally find in VBA, is that it is very memory intensive and that may effect the speed of the data processing.
I am "not really" using a database. Rather, I am doing alot of mathematical calculations and storing some of that data in re-sizeable arrays. So there is alot of memory access going on. The main reason that I am using VBA under Excel is for two reasons: 1. The ability to display nicely arranged data in the spreadsheet 2. The charting functions As for the number of calculations, it is basically a binomial tree[^] structure with a maximum of 4000 levels. Thus I calculate 2 levels of the tree to 4000 levels inclusive so the number of calculations is basically a sum of 4000 to 2 inclusive: 4000+3999+3998+...+3+2. In other words, it's ALOT. I think the majority of the slowness is due to the arrays I am using. I really have two options: 1. Offload the processing to a DLL and solely use Excel for the charting 2. Re-write the code in C#. I'm actually going to do both at this point to see the results and, just maybe, write a CodeProject article about it comparing the two approaches. We'll see. Thanks for the reply!
"Outlook not so good." That magic 8-ball knows everything! I'll ask about Exchange Server next.