Slower performance with Excel

Hi

I have an Excel workbook that has a lot of VBA.  On first use of the workbook a macro is run that populates worksheets with data and formulae, formats worksheets, hides columns/rows.  Since many of the worksheets are pw protected, there are also unprotect and protect steps.  Use is made of screenupdating = FALSE; Calculation = xlCalculationManual and EnableEvents = False.  It takes about 2 minutes to run the macro.

Out of curiosity I used the same workbook on a lower specification PC (a Dell laptop) and I really surprised to see it only took 45 seconds to run the macro.

This is not what I was expecting, particularly given the difference in specification of the PC's.
Main PC - Windows 10 64-bit; Intel i7-6700K @ 4Ghz;  32 GB RAM;  Office 2010 32-bit;

Laptop - Windows 10 64-bit; Intel Celeron 900 @ 2Ghz;  2 GB RAM;  Office 2007 32-bit;

I would really like to know why the VBA takes over twice to run on the much higher specification PC, and if there is anything I can do to improve the performance.  Unfortunately, I cannot provide a copy of the workbook.

The only other application I have running is MS Edge.

Many thanks in anticipation
Alison
alisonthomAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KimputerIT ManagerCommented:
The only fair comparison would be to have Office 2007 32-bit installed on the Main PC. If it now wins with for instance 20 seconds, it's an Office issue, not a hardware issue.

Also you missed about two other methods for speeding things up: https://blogs.office.com/2009/03/12/excel-vba-performance-coding-best-practices/
alisonthomAuthor Commented:
Hi and thanks for the response.

Office 2007 came installed on the laptop and I do not have an installation CD.  

If I were to install Office 2010 on the laptop I presume this would remove the Office 2007 installation, and I would remove the Office 2010 installation after running a test.  So I am concerned I would not have a working Office suite on my laptop,

I have now used the 2 other methods mentioned, but the timing is almost the same as before.

Thanks
Alison
KimputerIT ManagerCommented:
You can still install Office 2010 and make sure you don't press the Uninstall 2007 function. However, I would only do this if you're sure your Office 2007 is functioning properly (you can test it with the repair function, if it doesn't ask for disks, it's okay).
After testing, remove Office 2010, and use the Office 2007 repair function IF you have problems (usually not). In this case, also install only Excel 2010, instead of the whole Office suite (use the custom options).
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

alisonthomAuthor Commented:
Hi

I encountered a problem when attempting to install 2010.  The error message referred to missing files.

However.. I remembered I had another laptop that had Office 2010.  Its specification is
Windows 10 32-bit; Intel Core(TM) 2 Duo T7100 @ 1.8Ghz;  2 GB RAM;  Office 2010

This takes  47 seconds to run the macro.  So 2 laptops with Office 2007 and Office 2010 return almost the same timing, yet their hardware specification is inferior to my main PC which takes more than twice as long.
byundtMechanical EngineerCommented:
Alison,
Even 47 seconds seems like a long time for a macro to run.

I apologize in advance if the next discussion doesn't apply, but was some of your code originally a recorded macro? I ask because recorded macros are notoriously inefficient. As you move around a worksheet, the macro recorder records unnecessary steps like .Select, .Activate, and SmallScroll. If you manipulate cell borders, the macro recorder needs13 statements to do what a single statement can accomplish. PageSetup manipulation is even worse, with 53 steps recorded to do the job of a single statement. It is not unusual for properly edited recorded macros to be 20% of the size of the original, and to run 10 times faster.

Brad
alisonthomAuthor Commented:
Hi Brad
Thanks for the suggestion. The code is virtually all written by hand and when I did use the macro recorder I edited the code.  I also took a lot of care to avoid .Select and .Activate.

I have now downloaded and run the benchmarking software, PerformanceTest from passmark.com.  I ran the software on  my main PC and also on the laptop that also has Office 2010.  The results are as follows:

Main PC - Overall score 3477.9
Laptop - Overall score 209.1

Thanks
Alison
byundtMechanical EngineerCommented:
Alison,
The PerformanceTest score definitely confirms that your main PC is more muscular than the laptop.

Does your workbook need to establish links to other workbooks on a network? If so, your main PC may be slowed down by mapped drives that have moved or no longer exists. It may also be slowed down by differences in anti-malware & anti-virus software compared to the laptop.

Do you have several macros running in succession when the workbook is first used? If so, put all of the application settings at the top and their restoration at the bottom of the master sub rather than in each child sub that gets called.

If you set Application.ScreenUpdating = False at the beginning of each sub and restore it to True at the end, then the bigger the monitor your computer has, the longer the code is going to take for those flickering screen refreshes. If you set Calculation to xlCalculationManual at top of each sub, then restore it to xlCalculationAutomatic at the bottom, that will force a workbook recalculation at the end of each sub.

Brad
alisonthomAuthor Commented:
Hi Brad
Thanks for the follow-up.  I have been away for 2 days but I will respond to you later today,

Thanks
Alison
alisonthomAuthor Commented:
Hi Brad, sorry for the delay in responding.

Does your workbook need to establish links to other workbooks on a network? - It has no links to any other workbook.

Anti-malware & anti-virus software - Microsoft Defender is used on both machines.

Do you have several macros running in succession when the workbook is first used? - There are many procedures.  Application settings are initially set at the beginning of the main procedure, but there are certain places within the code where a screenupdate and a recalc are required.  After changing these application settings to True they are changed back to False as soon as possible.

Thanks
Alison
byundtMechanical EngineerCommented:
Alison,
It certainly sounds like you have already done everything I might advise to improve the speed of your macro.

I am still bothered by the fact that your desktop machine is so much slower in running the macro, even though it has far more computer power than your laptop. Have you ever run Microsoft's Configuration Analysis tool OffCAT? It's a tool that Microsoft uses to analyze for missing files, conflicts between files, improper registry settings, etc. It is restricted to Microsoft Office installations, and the database for it is updated monthly. The tool is free, and may be downloaded from https://www.microsoft.com/en-us/download/details.aspx?id=36852 

Be aware that the analysis is quite thorough, and if you ask for the entire Office suite to be studied, the program may run for an hour or more. Outlook is the biggest part of that delay. If your concern is just Excel, then restrict the tool to looking just at Excel related issues when you run it the first time.

Brad
alisonthomAuthor Commented:
Hi Brad

Many thanks for this.  I was not aware of this tool so I have now downloaded and run it.

There are 2 warnings:

1.

"Office 2010 is out of mainstream support"

2.

"The minimum required version of Office is not installed" - "You have Office version 2010 installed and Office Add-ins require Office 2013 or later. So, you have not met the system requirements for the version of Office needed to use Office Add-ins."


and 67 informational messages

Thanks
Alison
alisonthomAuthor Commented:
Hi Brad

This has made me think again about upgrading Office.  The version I have is 6 years old and the cost of Office 365 is very reasonable, so I am inclined to upgrade.  Maybe, and just maybe, the performance gap between my main PC and the laptop will be reversed, or at least reduced.

Thanks
Alison
alisonthomAuthor Commented:
Hi Brad

I upgraded to Office 365 and I still had a performance problem.  That left me with the option of setting up my PC again.  After reformatting the HD and re-installing Win 10 + Office 365, the performance has improved significantly. The benchmark score is now 5130 (previously it was 3477.9) and the VBA in the Excel workbook is executed a lot faster.  It is quicker than running on the laptop, but some parts of the VBA in 2016 execute slower than 2010 and therefore need to be improved.

So there was something wrong with my PC setup before which will remain a mystery.  

Many thanks for your help and assistance.
Alison

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtMechanical EngineerCommented:
Alison,
Office 365 installs the BI tools along with Excel. As a result, you have a little less RAM available for your workbooks. Some people have complained that 32-bit Excel 2016 gives them trouble opening larger workbooks (50 MB to 150 MB in the complaints I have seen). 64-bit Excel does not have this problem because it can use more RAM than you or I can afford to buy (up to 8 terabytes).

I think you should close this question by accepting your most recent Comment as the Answer.

Brad
alisonthomAuthor Commented:
Performance issue was resolved after refomatting HD and re-installing Win 10 + Office
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.