Best PC Specs to Speed up Excel/VBA Calculation

    Hello,


    Apologies if this is not the right place to post this question, but I couldn't find a better suited sub-forum. I deal with fairly large (sometimes up to 150 Mb) and complex Excel spreadsheets on a daily basis. This data is manipulated using both built-in formulas and VBA routines. Excel is my company's default tool because that's what the clients prefer to see and use, but we also use Access and occasionally SQL Server if necessary to do the work.


    Unfortunately, some of our VBA routines are starting to take forever to run in the new Windows 8/Excel 2013 environment, and with the source Excel files constantly growing in size it was decided to purchase a new desktop machine that we can use to tackle especially large files or complex models. Would anyone have suggestions as to the PC I should be looking into, which processor/memory are the best? The company is ready to make a pretty significant investment into this, so I am natually interested in what's best on the market.


    Thanks in advance!

    Re: Best PC Specs to Speed up Excel/VBA Calculation


    Have you isolated what parts of the VBA code are taking excessive time to run? There are many things that can be done to speed up most VBA code. It my be sufficient improvement can be made there. I leave it to others to suggest PC specs.

    Re: Best PC Specs to Speed up Excel/VBA Calculation


    Derk is absolutely right, I've seen code on this forum alone that has improved from about 1.5h runtime to just under 5 mins, using the HIRE HELP forum will be cheaper still than purchasing a new machine.


    Only advice I've ever had in terms of spec for Excel is to get an intel processor - apparently they're better at "number crunching" and more suited to spreadsheets and/or code that is running complex calculations.


    Hope that helps :)

    Re: Best PC Specs to Speed up Excel/VBA Calculation


    Quote from S O;728294

    Derk is absolutely right, I've seen code on this forum alone that has improved from about 1.5h runtime to just under 5 mins, using the HIRE HELP forum will be cheaper still than purchasing a new machine.


    Only advice I've ever had in terms of spec for Excel is to get an intel processor - apparently they're better at "number crunching" and more suited to spreadsheets and/or code that is running complex calculations.


    Hope that helps :)


    Thank you both for a speedy reply! Unfortunately, I believe the code is as efficient as it can get - it's mostly the size of the files and the nature of the work. For instance, some of the code needs to make a simple update, but it affects a lot of files, or needs to consolidate a lot of workbooks into one or push the data back to multiple files, or just deals with a massive amounts of lookup formulas etc. I am not sure there's anything that we can improve in that department - unless there's an option to pull or write data in Excel without opening the file that I'm not aware of? And thanks for the suggestion, I will look into intel processors for sure!

    Re: Best PC Specs to Speed up Excel/VBA Calculation


    Quote

    unless there's an option to pull or write data in Excel without opening the file that I'm not aware of?


    As an example.. you could create an array of all your Workbooks and 'open' them in the background using GetObject..


    I wouldn't suggest spending a cent on hardware until you optimise your code (or at least get others opinion on its efficiency)..


    Even if you invested some cash via the Hire Help Forum as SO has suggested.. you would undoubtedly get some improvements on your code that would warrant the investment..

    Re: Best PC Specs to Speed up Excel/VBA Calculation


    It is possible to read and write data using ADO without opening the workbook per se, though it may not actually be any faster. IMO, GetObject isn't likely to be any more efficient than Workbooks.Open since it still opens the file as a workbook in Excel.


    You mention lookup formulas - these are often the cause of performance bottlenecks but can be massively optimised if you sort your data.


    Beyond that, you may want to look at 64bit Office - unless you use ActiveX controls that are not from the MSForms library, or incompatible add-ins - so that you can throw RAM at the problem as well as getting the fastest Xeon processor(s) you can.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login