Forum Discussion
Speeding up VBA execution
Hi SamFares,
Excel recalculates all your formulas (entire workbook) every time there's a change in a cell that affects a formula. You're workbook is fairly large so this probably explains why it takes so much time as opposed to when you separate your sheets to a different workbook. You can tweak this a bit by either disabling the automatic calculation or leaving it to automatic except for data tables. File>Options>Formulas>Calculation options. Or leave your two sheets separated if they don't need to be in that workbook.
Read this for more details: https://docs.microsoft.com/en-us/office/client-developer/excel/excel-recalculation#:~:text=Excel%20reevaluates%20cells%20that%20contain,Use%20them%20sparingly.
Hi Bennadeau
1. Currently the edits in "Composite" and running the macro does not affect the values in other tabs. so the results in this tab stay within this tab.
2. Ultimately, I'd like to connect this "composite" tab with others. What i don't get how calculation in other tabs affect the composite tab. No values are changed in other tabs.
3. I changed it to "automatic except for data tables" but it didn't make a difference.
4. Would it be better to have them in two separate books and link them? would linking two books slow the macro run?
Thank you!
Sam
I launched your workbook and you have a lot of formulas in there. Must have taken you a while to do this.
To your questions:
1. As I said, if you change 1 cell in 1 tab, the entire workbook is recalculated.
2. Same as point 1
3. Have you tried manual calculation? After you run your macro, hit F9 to recalculate the workbook if needed
4. Yes it would be better to keep them separately. At least in terms of performance. Linking them shouldn't affect the performance.
I hope this helps.