VBA speed issues
-
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'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'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
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.
-
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
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
-
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... X|
"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
-
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... X|
"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:
X|
My thoughts exactly :laugh:
-
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
-
The Apocalyptic Teacup wrote:
X|
My thoughts exactly :laugh:
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
-
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
-
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...
:-D
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
-
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
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
-
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.
-
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
-
Tim Carmichael wrote:
I feel your pain...
:-D
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.
-
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. -
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
-
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.
-
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
-
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.
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