Excel Limitations for Large Macros

[Orion19]

Hi All,

I'll try to make this as brief as possible, but please let me know if I haven't supplied enough info.

I have a project with approximately 19,000 lines of code (I'm assuming that's large but have no frame of reference). It has multiple buttons that launch subroutines to find specific data and display it in a way that allows it to be graphed easily. It has grown over time and worked fine until the last three subroutines I added. Those last three subroutines were only 30 lines of code each and only copy/pasted data to a new tab. However, after adding those EVERYTHING in the workbook slowed down considerably. What used to take 3 seconds now takes several minutes or more.

I fixed the subroutines by adding the following code to the beginning of each subroutine:

    Application.EnableEvents = False    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

And the adding the following to the end of the subroutine:

    Application.EnableEvents = True    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

That speeds up all of my subroutines, but here is the problem: Changing any cell in the workbook takes a ridiculous amount of time. If I turn of automatic calculations everything is fine, but my users won't know how to turn that on and off manually without causing problems.

How do I manage a workbook this size without sacrificing the speed of the calculations? I don't need the whole workbook to update every time a cell is changed but I do need to get the user accurate data. Does anyone have tips on how I can approach this? Am I even thinking about it in the right way? Any tips/advice are greatly appreciated!!!

 

[Fazza]

Hello

There are many reasons for slow files.

Suggest you google. You might find site like below, in no particular order,

https://trumpexcel.com/suffering-from-slow-excel-spreadsheets/
https://www.pcworld.com/article/322...ft-excel-why-your-spreadsheet-is-so-slow.html
https://it.toolbox.com/blogs/itmana...ealing-with-slow-or-unstable-workbooks-021116
https://www.cogniview.com/articles-resources/speed-up-your-slow-spreadsheets
https://www.pcworld.com/article/229504/five_excel_nightmares_and_how_to_fix_them.html
https://www.xelplus.com/how-to-fix-slow-excel-spreadsheets/

IMO your familiarity with the file makes you best place to identify the issue/s. Google search results might be a good tool to help.

regards, Fazza

PS. If you think the code might be an issue be sure to do a code clean.

 

[Orion19]

Than you so much for the reply! I will definitely use the links you provided. Typically, Google has been my go-to but in this case I'm just not sure what to search for. The results are so broad I'm not sure where to start. I'm so new that I don't know what a code clean is so I'll start by googling that first. Thanks!

 

[My Aswer Is This]

If your new to Vba and you have 19,000 lines of code.
Maybe you would like to show us about 100 lines of that code and maybe we can see how you can do things with shorter amount of code.

 

[Fazza]

code clean

http://www.appspro.com/Utilities/CodeCleaner.htm

As explained at the top of that page,
During the process of creating VBA programs a lot of junk code builds up in your files. If you don't clean your files periodically you will begin to experience strange problems caused by this extra baggage. Cleaning a project involves exporting the contents of all its VBComponents to text files, deleting the components and then importing the components back from the text files.

If you can't use the code cleaner, then just do it manually. Steps as above.