Speed of calculations Depends on ScrollRow

[jdsouza]

Tested further ... Here is the relevant code from the Workbook_SheetChange event

fWShChange = True ' Ensures Events are looped out, in case some part of the extensive code sets EnableEvents to True
CloseScreen ' Locks the screen and updating
fProtect = True
Z = ActiveWindow.ScrollRow
PagesProtect False, False ' unprotects relevant pages
ActiveWindow.ScrollColumn = 1 ' Tried changing this to various values -> no change in timing
ActiveWindow.ScrollRow = Y ' When this is changed to any value higher than last row there is no difference


DoCalc Target ' The main algorithm


fProtect = False
DoEvents
PagesProtect , False ' reprotects the relevant pages
fWShChange = False ' Allows events codes to function
ActiveWindow.ScrollRow = Z
ActiveWindow.ScrollColumn = 1 ' Irrelevant
oTM = Now + 0.0000002
Application.OnTime oTM, "openscreen", , True ' Allows screenupdating and opens the window
--------------------

Reduced the amount of input data to speed up the calculations (only a few rows of the 45 used)
With Y set to 1 to 36, Consistently get .641 ~ .656
With Y set to 37 to 57, Consistently get 1.515 ~ 1.563
With Y set to 58, 100, 200, Consistently get .688 ~ 735
With Y set to 1000, Consistently get .688 ~ 735
With Y set to 10000, No change from 1000
With Y set to 30000, No change from 1000

As mentioned above, changing the ScrollColumn does not seem to have any effect.
Rows 58 onwards are hidden and have no data that is part of the manual data-entry but have constants or True / False values in rows 100-160 in column C that are used in the DoCalc algorithm.

It is certain that ScrollRow of 37 to 57 causes a substantial slowdown in calculation speed.

While, it could be 'just with my workbook', it still remains an unexpected behaviour of Excel and this post may be helpful to others, I hope.

 

[jdsouza]

.688 ~ 735

Correction -
Should read .688 ~ .735