Speed of calculations Depends on ScrollRow - Page 2

Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 12 of 12

Thread: Speed of calculations Depends on ScrollRow

  1. #11
    Board Regular jdsouza's Avatar
    Join Date
    Jul 2012
    Posts
    100
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Speed of calculations Depends on ScrollRow

     
    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.

  2. #12
    Board Regular jdsouza's Avatar
    Join Date
    Jul 2012
    Posts
    100
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Speed of calculations Depends on ScrollRow

      
    Quote Originally Posted by jdsouza View Post
    .688 ~ 735
    Correction -
    Should read .688 ~ .735

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com