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.
 
In that case, I'm afraid I'm out of ideas.
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
In that case, I'm afraid I'm out of ideas.
Your question inspired me to try a simple test:

LinkedCell = I22, wrote formula cell Q22 "=I22" (duplicating combobox.value), changed my VBA code so that the cell in sheet Zones was set to Range("I22").Value (instead of combobox.valu).

No improvement. It's very frustrating to KNOW the sheet could recalculate instantly (as it does with a manual change of the cell value), but I cannot make that happen using the combobox list selection.

Thank you for spending time on this.
 
Upvote 0
The only thing I can think of that would cause the problems, would be if the code is being triggered multiple times, although it doesn't sound like that is happening.

One other thing you could check would be to add the line in blue
Rich (BB code):
Application.Calculation = xlCalculationManual
Debug.Print "abc"
turn_column = Range("I5") + 2
then select a value from the combo. When its finished have a look at the immediate window in the editor (Ctrl G will bring it up if needed)& see how many times it shows abc
 
Upvote 0
The only thing I can think of that would cause the problems, would be if the code is being triggered multiple times, although it doesn't sound like that is happening.

One other thing you could check would be to add the line in blue
Rich (BB code):
Application.Calculation = xlCalculationManual
Debug.Print "abc"
turn_column = Range("I5") + 2
then select a value from the combo. When its finished have a look at the immediate window in the editor (Ctrl G will bring it up if needed)& see how many times it shows abc

Another "crazy" finding:

If I replace the combobox with a data validation dropdown list, and then use a Worksheet_Change routine to capture the fact that the value has changed and run my code, I get lightning fast recalculation every time.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

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
Back
Top