|
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
|
|
|
|
|
The Apocalyptic Teacup wrote: I am not sure if VBA is basically interpreted rather than compiled and thus slower than, say, a compiled Visual Basic app.
VBA is interpreted according to wiki, VBA wiki[^]
I am sure porting to C++ would be worthwhile 
|
|
|
|
|
PaulC1972 wrote: I am sure porting to C++ would be worth
I agree. However, there seems to be a large problem. I'm trying to drive Excel 2000 through C#, but I'm encountering some problems with Primary Interop Assemblies...I think I need Excel 2003 or even more recent... 
"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
|
|
|
|
|
The Apocalyptic Teacup wrote: 
My thoughts exactly 
|
|
|
|
|
PaulC1972 wrote: My thoughts exactly
Yeah. I guess I convince my employer that the simulation runs for 12 hours while I get a day off! Oh, if only...
"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
|
|
|
|
|
The Apocalyptic Teacup wrote: the simulation runs for 12 hours
Sounds like it could use some optimizing then.
|
|
|
|
|
PaulC1972 wrote: Sounds like it could use some optimizing then.
Indeed - hence this thread!
"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
|
|
|
|
|
Did you run an analysis, identify the procedures that are consuming the most time, and optimize them? This would be the first step before making a decision to rewrite in another language.
How large is the program? Converting to VB.Net might be the most prudent and quickest.
|
|
|
|
|
arcticbrew wrote: Did you run an analysis, identify the procedures that are consuming the most time, and optimize them? This would be the first step before making a decision to rewrite in another language.
No, but I have a pretty good idea where the slow code is. I execute a number of loops and the number of calculations can reach a maximum of 15000 iterations of a particular function. As it stands right now, it is taking approximately 12 hours to complete a simulation and that is, honestly, unacceptable. I really need to trim this down.
arcticbrew wrote: How large is the program? Converting to VB.Net might be the most prudent and quickest.
The program is about 5000 lines. So it's not exactly trivial to port it to C# or C++, for example. It also interfaces extensively with Excel 2000 (uses the charting functions as well as some spreadsheet entries), but on a first investigation, there seems to be a problem driving Excel 2000 with C#...something about Primary Interop Assemblies...Oh dear.
"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
|
|
|
|
|
1. Within Excel, ensure that the calculation mode is set to 'Manual' and not automatic.
2. VBA is interpreted; anything compiled should be faster.
3. If the application is already written in VBA in Excel, porting it to VB should be a breeze... essentially cut and paste.
Good, bad, or otherwise, I work in the VBA/Excel environment just about every day.
Tim
|
|
|
|
|
Hi Tim, thanks for the response.
Tim Carmichael wrote: 1. Within Excel, ensure that the calculation mode is set to 'Manual' and not automatic.
Yes, have already done this.
Tim Carmichael wrote: 2. VBA is interpreted; anything compiled should be faster.
That's what I was thinking. But two issues. The first is: How much faster? The second is that I read that VBA is basically interfaced with Excel from a DLL and thus should have really low overhead. I am therefore suspicious as to whether or not porting it to VB is really going to improve the situation noticeably...
Tim Carmichael wrote: 3. If the application is already written in VBA in Excel, porting it to VB should be a breeze... essentially cut and paste.
Yes, but it uses the charting functionality of Excel extensively. I was trying to do a preliminary investigation as to whether or not I could write this in C#, but there seems to be an issue with the Primary Interop Assemblies between C# and Excel 2000. From what I have read, it would seem that I need Excel 2003 or even Office XP to get around this. I don't know if there is a solution for Excel 2000. Gah, what a mess!
"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 feel your pain...
If you need to use the charting functions in Excel, it it possible to offload the processing into a DLL?
I have some user-written Excel spreadsheets (again, for charting) that use a great deal of data... as much as possible, I have modified the VBA code to offload processing to a DLL or a stored procedure. The only interaction with Excel then is: get the data, put the data, chart the data...
If I can help, let me know.
Tim
|
|
|
|
|
Tim Carmichael wrote: I feel your pain...
Tim Carmichael wrote: If you need to use the charting functions in Excel, it it possible to offload the processing into a DLL?
I'm not exactly sure what you mean. Are you suggesting I write the calculation/simulation code in Visual Basic and compile it to a DLL and then call the DLL from VBA in excel? Or is it the other way around? Package the Excel charting capability into a DLL and call that from my VB app?
"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
|
|
|
|
|
Well... from my end, the charting was done directly in Excel with VBA calling DLLs written in VB to do the bulk of the work.
I have had opportunity to write code that opens Excel spreadsheets... but that caused other issues and was difficult to debug what was happening in Excel.
But... either way works.
|
|
|
|
|
Tim Carmichael wrote: Well... from my end, the charting was done directly in Excel with VBA calling DLLs written in VB to do the bulk of the work.
I have had opportunity to write code that opens Excel spreadsheets... but that caused other issues and was difficult to debug what was happening in Excel.
But... either way works.
Yes, packaging the simulation code into a DLL could be a solution rather than involving C#. However, I was unaware that you could call a DLL using VBA under Excel. Do you have any documentation on how to do that? Maybe that's a silly question, but I mean my experience with VBA is quite minimal...
I only have access to Visual Basic Express (the one Microsoft released for free). Can I build a DLL using that? This would be a nice approach because the simulation code is already written in VBA so it would just be a matter of packaging it up into the DLL, building it and then calling it from my charting macro under Excel.
By the way: thanks for all the help. Greatly appreciated.
Well, who the hell wrote your fast, stable Slackware distribution? Wandering minstrels?"
"Outlook not so good." That magic 8-ball knows everything! I'll ask about Exchange Server next.
If I had wanted your website to make noise I would have licked my finger and rubbed it across the monitor.
|
|
|
|
|
We frequently call COM objects written in VB from VBA (either in Excel or a reporting package). So, in theory, you should be able to take the code, massage it into VB Express, write a COM object and call it from Excel...
I just ran a current VB 6 DLL through the upgrade wizard to confirm what it would create... a new project as a class library.
Assuming you can create a class library from VB Express, you should then be able to call the component.
I don't have VB Express on my work PC, but I do on my home PC... I can look at it this weekend and hopefully provide a simple working example.
Also, talked to a co-worker who said that we are currently calling .NET components through SQLServer jobs using scripting, so... it should work.
Tim
|
|
|
|
|
Oh this is good. It should take a minimal amount of time to do this and avoid the C# route. That would be great!
A working example would be excellent if you have the time. I can't say how helpful you've been. Thanks alot, Tim.
I'll fire up VB Express tonight and see if I can build the DLL.
"Outlook not so good." That magic 8-ball knows everything! I'll ask about Exchange Server next.
|
|
|
|
|
And.. isn't this help the purpose of Code Project? I may not necessarily use a lot of code from here, but I can certainly get ideas on how to improve items...
Tim
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
Sorry for another reply, but it seems as though I've gotten around the problem with the interop assemblies. I've posted my work-around here[^]. I know now that I can access and write to an Excel 2000 spreadsheet. If necessary, I can fill the cells and then use my existing VBA code to do the charting from button_click() event or something. Beautiful!
I am still curious about your DLL suggestion, however...
"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
|
|
|
|
|
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.
|
|
|
|
|
I'm developing a project of collecting email id and storing them in the database and to support POP and SMTP service. For that I want to know how to send email from VB to SSL supported site?
Subham Rakshit
Software Developer
|
|
|
|
|
I have a form with a variable that is a custom object. I would like to bind certain controls (text and combo boxes) on my form to properties of the object. Thus if the user changes the value of a text box the corrisponding property of the object is updated. How might I bind a control to a property of the object?
Cheers
Visual Studio .Net 2005
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
