asked on
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
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
Microsoft ExcelMicrosoft OfficeVBADell
Last Comment
alisonthom
ASKER
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
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
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).
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).
ASKER
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.
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.
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
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
ASKER
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
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
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
Brad
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
Brad
ASKER
Hi Brad
Thanks for the follow-up. I have been away for 2 days but I will respond to you later today,
Thanks
Alison
Thanks for the follow-up. I have been away for 2 days but I will respond to you later today,
Thanks
Alison
ASKER
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
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
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
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
ASKER
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:
and 67 informational messages
Thanks
Alison
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
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
ASKER
Performance issue was resolved after refomatting HD and re-installing Win 10 + Office
Also you missed about two other methods for speeding things up: https://blogs.office.com/2009/03/12/excel-vba-performance-coding-best-practices/