System optimization for lengthy Excel VBA iterations

[Dew itt right]

A large part of my design work as an engineer involves using a rather complicated Excel spreadsheet that I developed to run energy modeling scenarios. The spreadsheet does some calculations in real-time within the cell equations but the bulk of the work is done by a 2000+ line VBA code that max's out my CPU and renders Excel useless. The work PC I built specifically for these runs has an i7-4790 and takes 5-6 minutes to complete the run. For reference, my home PC with an i5-3570 does it in about 9 minutes which is on par with my i5/8GB Surface Pro 2.

I'm wondering if I could do something to upgrade/modify my work PC to get the run times down. For the large amount of simple, repetitive calculations, would I be better off using an AMD processor that has more physical cores and higher clock speeds? Would faster RAM make any noticeable difference?

System specs are as follows...

Intel Core i7-4790k
Asus Maximus VI Formula
16GB G.Skill Ripjaws X DDR3 1333 Memory
EVGA GTX 650 GPU
240GB Kingston HyperX Predator PCIe SSD
Antec 850W Power Supply

 

[StoleMyOwnCar]

Pointing out some things here:

1. You're running VBA for complex, repetitive calculations that take several minutes? Why do you not load the excel file into another language that will run faster? IIRC VBA is mainly a macro language. You don't make full scale analysis and modeling in it. You could try Matlab if you want something that may be a little easier to use. Matlab has some native excel importing stuff, and is specialized for calculations and operations like that. I have used it before to perform calculations on excel data sets. IIRC it also has multithreading support:
http://www.mathworks.com/matlabcent...ctions-benefit-from-multithreaded-computation
If you want more control than it provides, you could go down a few steps to Perl/Python, and then further from that, C/C++. Note that unless you have a good understanding of parallelism with regards to code design and computation, you should maybe go the Matlab route. It's not free, though I believe they have a demo of it.

I'm wondering if I could do something to upgrade/modify my work PC to get the run times down. For the large amount of simple, repetitive calculations, would I be better off using an AMD processor that has more physical cores and higher clock speeds? Would faster RAM make any noticeable difference?

2. No. VBA is single threaded. http://stackoverflow.com/questions/5721564/multi-threading-in-vba
More cores will not help you. With your program as it currently is, it is running about as fast as it possibly can. AMD hardware will actually slow it down significantly because each one of its cores is weak compared to your i7. You could do some workarounds that were linked in that Stackoverflow thread but frankly I think simply recoding the thing to work in a language that is designed for multithreading (and speed in general) is probably more worth your while than doing hack around procedures.

To me this sounds like a software problem, not a hardware problem.

 

[cyclone3d]

Matlab or Mathematica.

Either should cut the calculation time down to a few seconds.

 

[Dew itt right]

Thanks guys but I should have clarified that I have no knowledge whatsoever about programming or using any of the programs you mentioned. I'll the programming knowledge I have is tied up in that spreadsheet that took me 2 years to get to this point. I'll check the other programs out but I'm fairly certain that learning how to use them will consume more time than I have right now (swamped at work and new baby at home). Modifying the PC I can do in my sleep so I thought I'd limp along with the less-than-ideal spreadsheet as long I could make it a little faster.

Are you sure Excel VBA is single threaded? When I run it, the task manager shows all 8 cores (4 physical, 4 logical) at/near 100%. Plus I tried setting up a VM to run two instances simultaneously but it just wound up taking twice as long to complete both runs. That's what led me to believe it was actually maxing the CPU out.

 

[StoleMyOwnCar]

That depends on whether they are actually doing anything with the VBA program.
Excel itself is multithreaded:
https://support.microsoft.com/en-us/kb/2696967
https://fastexcel.wordpress.com/201...tion-speed-by-changing-the-number-of-threads/

But VBA code runs on a single thread (at least as far as I can see). You mentioned 2000 lines of VBA. Those VBA lines themselves are probably mostly run on the first core. If they make certain modifications and formula executions that get deferred to the program (excel itself) to run, then they might happen on other threads. How this all happens under the hood is a question best left to someone else, as I do not often use VBA (simply for sorting emails, and even for that it's horrendously slow...). It's highly dependent on the way you structure your Macro.

An i7 should be sufficient for pretty much any kind of code. Your code is very likely suboptimal (no offense), especially considering you mentioned you have no programming knowledge. Since it's likely proprietary, too, there's not much we can do to help you optimize it. You can upgrade your system to something like a hexa or octacore for an enormous fee, but it will at best provide a linear increase in compute times with amount of cores. You could also look into using your GPU for the calculations... but again not in VBA:
http://en.allexperts.com/q/Excel-1059/2012/3/using-gpu-excel-vba.htm

You should have spent those years gradually learning a better language to do this in, not struggling along with VBA...

 

[heatlesssun]

You should have spent those years gradually learning a better language to do this in, not struggling along with VBA...

Without seeing the code it's impossible to say but I've worked a lot with Excel "programs" written in VBA and often VBA isn't so much the problem as using Excel efficiently programmatically. Doing a lot of number crunching in VBA within Excel sounds self-defeating on the surface as that should left to Excel's capabilities.

It is relatively easy to create .NET or with more difficulty even true COM add-ins for that could be significantly faster than VBA. Of course Excel may not be a good tool for job.

 

[StoleMyOwnCar]

Matlab can easily import Excel files, and it's in general a very easy language to pick up.
They have a whole section dedicated to working with XLS reads:
http://www.mathworks.com/help/matlab/ref/xlsread.html

I literally had no programming experience going into college and I had no issues learning matlab first semester and I ended up better at it than most people. Of course then they moved me straight into C (not C++, just straight up C...), which was a bit of a hard one but I somehow got an A in that, too. So it's not a bad language to start with just to get introduced to basic programming constructs. But if he's been working with VBA anyway, he should be familiar enough to switch languages without too much issue. I'm not sure about Mathematica. My only experience with it is some professors using it. Our Differential Equations teacher for instance. He used it to show us some flow rates and such, graphed out.

I mean I don't know what's in those 2000 lines, so who can say for sure anyway? Any time I have a script that has anywhere near 500 lines, I ask myself wth I'm doing. That need to get separated into various function calls and maybe even classes.

 

[Dew itt right]

Thanks for the info! I wish I could have "spent those years gradually learning a better language" but this spreadsheet in it's most basic early form needed to be up and running within the first couple weeks of me being assigned to this new product development so putting that on pause and learning a new language was not an option. Plus the first version only had about 50 lines of code and nowhere near the functionality or accuracy that it has today so it has more or less outgrown Excel over those two years.

I'm still not sold on VBA running on a single thread though. I ran a different (much simpler) program that I wrote that just runs through thousands of rows and deletes the rows with incomplete data. It's obviously much quicker due to it's simplicity but I threw in 50,000 rows of data to be processed and for 20 seconds the resource monitor showed 4 cores at 100% and the other 4 fluctuating between 50% and 80%. That's with only a single Do Loop and no equations in the worksheet cells at all. No other programs were running either so I have to conclude that the VBA code was running on more than just the first core.

Thanks for the input on the hardware as well. It sounds like I'm not going to get any appreciable benefit without spending some significant money. I just wanted to make sure there wasn't an obvious hardware optimization I was missing.

 

[Chris_Lonardo]

As others have said, this is a problem that's more reasonably solved by code. Excel and VBA are generally not all that efficient when you start running large calculations (even when your CPU is at 100%, it can be busying itself with bullshit rather than using CPU time effectively), but without seeing your code and data (feel free to post it and I'll have a look if it's not proprietary), I would make a few general recommendations:

1) If you're using VLOOKUP anywhere, stop. Replace it with INDEX MATCH: http://www.exceluser.com/formulas/why-index-match-is-better-than-vlookup.htm . In fact, beware of volatile functions in general- http://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/

2) Multithreading is possible in VBA, with a few tricks. Maybe give this a shot? http://www.analystcave.com/excel-vba-multithreading-tool/

3) You may have better performance by linking your sheet into Access, as it's more performant for large datasets. You can port your VBA code without much adaptation.

4) Benchmark the various functions of your sheet and find out where the bottlenecks are. Look for things like blocking while waiting for the sheet to recalculate- for example, if it's recalculating something after every row when you only care about the result after it's populated all of the rows, you can stop volatile functions from recalculating by setting Application.Calculation appropriately, and programmatically triggering recalculation as needed.

 

[StoleMyOwnCar]

If it's working for you, it's fine for now (though it's not going to get any faster); you'll just have to put up with your several minute compute times. The thing is, you have nothing to compare to. If you wanted to actually reasonably know how well your program is performing comparatively, you would have to do a benchmark program or two written in various languages. So instead of porting your whole thing over, maybe take some smaller chunks that take a minute or two to run. Run them in Excel, run them in another language. After making sure the results are the same (ie code matches up in terms of outputs), compute run time differences. If Excel's is significant... well there you have it, it's holding you back. Right now, it's all just guesswork. But with an i7 4 cores and HT... yeah, it's probably software-side.

If this computation is mission-critical, and at some point you need to make some significant change within a day or two that requires debugging, your current model may bite you in the rear. Hard. But if you've been dealing with it for this long, then whatever. I'd personally learn something else on the side, though, in your situation. Versatility is valuable in the job market anyway, and programming ability in various languages even moreso. It's an investment, imo.