Excel Workbook - Too Slow

vikrampnz

Board Regular
Joined
Jun 20, 2006
Messages
92
Office Version
  1. 2007
Platform
  1. Windows
Hello

I have a Workbook with 100+ worksheets with quite a few codes and links to the Summary sheet. It works well in terms of what its expected to do, but the Codes and various processes have made the sheet extremely slow. For example, it can take between 10-30 seconds to do any alterations/data entry or to Save/open the workbook.

I have created a sample version of my actual workbook with just 10-15 worksheets and uploaded the same on the attached link. Interest Calculator - Trial Sheet . So, that you can see the codes I am using and the way data is used. (My actual workbook has 100+ worksheet, so you can imagine a multiplied problem).

I am just wondering if anyone can help improvise the codes or the way I am manipulating the data in order to increase the speed. Currently, it is getting very frustrating to use the sheet just because of the response time taken on the smallest edit or action. Otherwise, the functionality of the sheet is excellent.

Would appreciate any help anyone can offer.

Cheers
V
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,628
Your link takes us to the Google Drive Sign-in page. We need a direct link to your file.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,397
Office Version
  1. 365
Platform
  1. Windows
Here's my two cents...
I have a Workbook with 100+ worksheets with quite a few codes and links to the Summary sheet.
We I see something like that, and a complaint of slow performance, I am not surprised, because Excel is being used in a way it really wasn't intended for. If you have 100 worksheets, with tons of links, what it really sounds like you are describing is a "relational database". While people can use brute force methods (i.e. lots of VLOOKUP or INDEX/MATCH formulas) to use Excel as a relational database, that really is not what it was designed for, and as it grows larger in size, this becomes more evident/pronounced.

It may be time to graduate to an actual relational database program like Microsoft Access, SQL, MySQL, or Oracle. Using a program designed for relational databases will perform much better.
 

vikrampnz

Board Regular
Joined
Jun 20, 2006
Messages
92
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Hello Joe4, even when I created this trial sheet with just 10-12 worksheets, the speed is just as slow and frustrating. Therefore, I am beginning to wonder if the slow speed is caused just due to the sheer volume of worksheets or due to the in-efficient code I am using.

My process involves copy pasting data from all the client worksheets to one worksheet by the name "RDBMergesheet". I am wondering if there is way it can only copy-paste data from the sheets that have been changed. But, the RDBMergesheet is a dynamic sheet, so I am confused.

Just wondering if there is a smarter way to do what I am trying to do by copy-pasting the entire workbook.

Thanks
V
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,397
Office Version
  1. 365
Platform
  1. Windows
There may be minor tweaks you can make to your code to speed up things a little, but the sheer size you are talking about is going to have performance issues in Excel.
A fellow MVP use to have a saying that goes something like "You could use a wrench to drive nails, but why? It is inefficient. Use the right tool for the job.".
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,628

ADVERTISEMENT

I had a look at your file. You have a very large number of formulas which slow things down and you even have formulas in ranges that return no data. For example, in sheet Summary you have data to row 16 but formulas to row 244. Also, you have a Worksheet_Activate event for sheet Summary so every time you activate it, it has a lot of code to process. In sheet RDBMergeSheet, you have a Worksheet_SelectionChange event that is activated and processes a lot of code every time you click on any cell in the sheet. All these things, among others, slow things down. I don't think I could do much to speed things up and I have to agree with the suggestions that @Joe4 made.
 

rpbenz

New Member
Joined
Jan 25, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I have found that Microsoft broke the Worksheet calculation in 64bit versions later than 1902. Stay away from xlCalculationAutomatic in newer versions of Excel you have large spreadsheets or spreadsheets in which cells are periodically changed. You must turn off calculation (Application.Calculation = xlCalculationManual) an fire the calculation of sheet only when you need to otherwise risk the total crash of Excel. I have spent a considerable amount of time and headache, including a ticket with Microsoft until I finally found that Microsoft updates specifically 2012 and 2101 have been totally screwed up!

I have about 15 spreadsheet VBA applications running at customer facilities that poll and populate sheets with operational data through a Modbus communication active X (MBAXP), all running anywhere between versions 1708 to 1902, these applications running flawlessly for weeks on end no problem whatsoever. Then the last two applications I ran into a huge problem. The same application would hang as soon as it started, I couldn't select any cell it would run for about 10 minutes then the darn Excel would crash and restart with recovered versions of the same application, the recovered application having no difference. I originally thought it was a computer problem until I started experimenting with other versions Excel! I downgraded to earlier versions and found that all was good, polling selecting was normal. Then I experimented with the newer version, specifically 2012, thinking it could be the MBAXP. After replacing the MBAXP with a timer and a random number generated sheet populating macro I found that the hangup was the sheet calculation, timing the difference between 2012 (2101 was a little better) and the version 1902 at sometimes over 50 times as long in the new versions!! I had to limit the timer for - again - after about 10 minutes the darn Excel would totally crash and restart. No one at Microsoft (or anywhere else) seems to know why there is such a huge loss in calculation speed. I can only guess the programmers at Microsoft don't know what they are doing. While I finally solve the mystery, I am left to wonder whether Microsoft is intent on screwing up the product, leaving me to consider alternatives. Its quite a bummer.
 

rpbenz

New Member
Joined
Jan 25, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
BTW, this post is a step by step instruction on how to downgrade to working versions of Excel
 

Forum statistics

Threads
1,143,688
Messages
5,720,305
Members
422,275
Latest member
Maria95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top