Best PC Setup for running heavy VBA Programs

From my experience, you have two major variables... RAM and speed. If you are using Excel 2010 then you should make sure that it was installed as 64-bit instead of the standard 32-bit. This will allow you to go over hte 32-bit limit of using 2 GB of RAM per application. This is of great importance if you are using large Exel file sizes or writing large ammounts of data into arrays.

Usually for most of us, its the second issue that is more important... SPEED. If you are running a million calculations, and calculations on top of calculations, etc. then this should be a big priority. Number of cores, speed of the cores, speed of the hard-drive containing your windows and excel application are all factors in how fast the code will be compiled and implemented. More often than not (from code that I have seen), there are better ways to write efficient code, which in turn will run faster and take less time to complete.

On a side-note, I would definitely advise you to get at least 2 LCD monitors (size doesn't matter as much) but I prefer at least 22 - 24 inches. A video card with independent memory is a big plus here because shared memory only means that it will take away memory from your other applications.

For $3,500 you should be able to get a pretty good system. I was taking a workshop on high speed computing and they were talking about that much for a computer to generate 3D graphics which is much more intense RAM- and CPU-wise than anything I have ever run with vba. I have heard of people coming with hard-drives full of data that need to be analyzed and they run these applications on the big guns (servers with 30 cores and something like 200 GB of RAM each), but I have never heard of anyone using Excel for this massive workload.

Hope this helps.

abousetta

Personally I would definately go with the Intel® Quad Core™ i7-2720QM (2.20GHz, 6M cache). The more cores and additional cache would be expected to be a plus.

As for using 64-bit Office, I have it on my 'muscle computer' and I do agree that its sometimes a pain in the **** because some of the add-ins don't work properly, but with super large files, its a must have. You can test both ways, start with the 64-bit installation and see time to complete the vba run and compare against an installation with the 32-bit version. Unfortunately (to the best of my knowledge) you can't install both version at the same time on the same computer (unless you have multiple OSs installed).

FWIW, loading the data into memory and processing it there, rather than on the worksheets, and only writing the finalised results back to the workbook can be far faster than processing the worksheets directly.

Agree completely.