Speeding up VBA execution

[SamFares]

Hello,

In the attached excel sheet, i added the "Composite" and "FloorData" tabs to my excel sheet.  Those two tabs are not linked to other tabs in the sheet. When the tabs are in a separate excel sheet, the macro       " Click to Calculate" runs in  a second, but when it is within this excel sheet it runs for nearly two minutes even though it is not linked to other tabs.

"Composite" tab password: chickenhawk

VBA password: csirocks

in the "Composite" Tab:

key in in cell H27=  2

key in in cell H28= 150

key in in cell H29=  8

"Click to Calculate" macro which is placed near cell G50. before you run it it is in light red then it turns green when it is done.

I don't know how to make the macro much faster. for some reason it slows down big time. Like i said earlier these two tabs are not linked to the

Thank you,

Sam

[#2]

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%20r....

[#3]

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

[#4]

@SamFares 

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.

[#5]

@SamFares 

This could help against this:

Application.Calculation = xlCalculationManual
before the start of the operation and

Application.Calculation = xlCalculationAutomatic
after the operation

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

[#6]

Hi @NikolinoDE 

I placed the code as shown below at the beginning and end. the values in other tabs will depend on the values in the "composite" tab where the macro is.  if i did what you suggested correctly, I didn't see a noticeable difference. 

Thanks,

Sam

[#7]

@SamFares 

Excuse me in advance, but at the moment I can't take the time to look at the whole workbook with the VB code (there are a lot of modules so far).
In spite of the test, here is a way to speed up VBA code.

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

'Dein Code

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

However, you have to weigh up which of these conditions may be necessary for the correct execution of your macro.

But I am almost certain that there will be a few more people who will / can offer you an even better solution.
My knowledge compared to some here is very modest (to put it mildly).

Sorry that I took your time and couldn't give you a quick solution.

Wish you a nice day.

Nikolino

I know I don't know anything (Socrates)

[#8]

Thank you @NikolinoDE