Excel Workbook - Too Slow

[vikrampnz]

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

 

[mumps]

Your link takes us to the Google Drive Sign-in page. We need a direct link to your file.

 

[Joe4]

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]

Hello mumps .....hopefully this link works Interest Calculator - Trial Sheet .

Thanks

 

[vikrampnz]

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]

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]

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]

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]

BTW, this post is a step by step instruction on how to downgrade to working versions of Excel

Revert to a previous version of Office 365

Split from this thread. I'm with Tiffany on this. The new look is awful. It happened to my computer overnight and I just spent 10 minutes swearing at it. Yellow highlights on flagged messages - yuk,

answers.microsoft.com