Strange Calculation Slowdown Depending on How Data Was Changed

twenck

New Member
Joined
Nov 9, 2020
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
Summary up front: If I change the number value in a cell manually (can be integer 1 to10) the workbook recalculates near instantaneously. If I use a combobox to select the value (1 to 10) and simple one line VBA code to replace the value in that same cell, the workbook takes 3 - 4 seconds to recalculate.

VBA Code:

Private Sub ETO_Temp_Roll_Change()
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

turn_column = Range("I5") + 2

If Activate_Die_Rollers.Value Then Sheets("Zones").Cells(5, turn_column).Value = ETO_Temp_Roll.Value Else ETO_Temp_Roll.Value = Sheets("Zones").Cells(5, turn_column).Value

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

End Sub

ETO_Temp_Roll is a combobox allowing user to choose values 1 - 10. The simple code above replaces cell (5, turn_column) in worksheet "Zones" with the value selected by the user with the ETO_Temp_Roll combo box.

If I manually enter a new value (1 to 10) in any cell in Sheets("Zones"), the entire workbook (10,000s simple formulas and some conditional formatting) recalculates near instantaneously.

If I use the ETO_Temp_Roll combobox dropdown to enter the same value in the same cell, via the above code, the workbook takes 3 - 4 seconds to recalculate.

How could the recalculation time depend on the method used to change the data in the cell? I would think that once the cell value was changed, the Excel recalculation process would the same, no matter what method was used to change the data. I have watched the cell while executing the VBA code via the combobox -- the value in the cell changes instantly upon clicking the combobox.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
Office Version
  1. 365
Platform
  1. Windows
I dont think you need to change the calculation mode there. Try removing those lines.
 

twenck

New Member
Joined
Nov 9, 2020
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
I dont think you need to change the calculation mode there. Try removing those lines.
Thank you for reviewing/responding.

While I can see why you would say that (only one change is being made), I cannot see how that could cause the issue I described (manual cell value change recalculates lightening fast - combo box VBA cell value change recalculates very slow).

In any case, I removed the calculation mode changes -- made no difference.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
Office Version
  1. 365
Platform
  1. Windows
Turning on calculation has to force a full recalc of the workbook. Changing one cell doesnt.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If you put a break point on this line turn_column = Range("I5") + 2, then select a value from the combo & step through the code with F8, does the code get triggered again?
 

twenck

New Member
Joined
Nov 9, 2020
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
If you put a break point on this line turn_column = Range("I5") + 2, then select a value from the combo & step through the code with F8, does the code get triggered again?
Thank you for looking at this.

No the code does not repeat.

Three new data points that make this truly baffling:

1. Putting in the break points to check as you suggested, eliminated the original issue -- recalculation is always instant -- ONLY AS LONG AS THE BREAKPOINTS ARE PRESENT!! Remove the breakpoints, trigger the combobox by selecting a value, and the slowdown reappears.

2. I have a command button in the sheet that replaces values in the "Zones" sheet data table in hundreds of cells all at once. This would obviously require a great deal more recalculation than my combobox code that changes the value of one cell. Yet, using that command button results in near-instantaneous recalculation.

3. Clicking on the combobox and typing a value (1-10), rather than using the dropbutton to pick a value from the list, RESULTS IN INSTANT RECALCULATION.

Bottom line: changing the value of a cell in the sheet "Zones" by any method OTHER than using the combobox selection list, results in instant recalculation; changing the value of a cell in the sheet "Zones" by selecting a value from the combobox list, results in a 3-4 second recalculation.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Is the combo on the sheet, or in a userform?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
Do any of the cells in the listfillrange of the combo change & do you use the LinkedCell property?
 

twenck

New Member
Joined
Nov 9, 2020
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
Do any of the cells in the listfillrange of the combo change & do you use the LinkedCell property?
The listfillrange never changes.

Yes, a LinkedCell is defined for the combobox, and there is code elsewhere that can change the combobox.value by writing to the LinkedCell. That code is called separately for completely different functions. It is never called and is unrelated to using the combobox by selecting a value from the list. As shown in my original post, selecting a value from the combobox writes that value to a cell in the "Zones" sheet, and does nothing else.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,096
Members
415,876
Latest member
csibonga2k17

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