Speed of calculations Depends on ScrollRow

[jdsouza]

Good day

Will others be able to ratify this observation? I've been writing Excel based software for years and have ignored speed of computations (brushed it under the carpet) until recently when even the 2 or 3 seconds the algorithms took seemed too long.

I've discovered that if I set the activewindow.scrollrow to 1, complete the calculations and bring the scrollrow back to what it was after the calculations, I can save a second out of the two seconds it took if I did not change the scrollrow.

For good measure, I also set the scrollcolumn to 1.

This is tested numerous times to make me certain that setting the scrollrow (and perhaps the scrollcolumn, too) to 1 speeds up the computations. BTW, all calculations are done in VBA and not as cells' formulae. That is, I take the cell values in about 50 rows into memory, process in VBA and show the results as values (with required formatting) in the corresponding cells.

So, has anyone else been troubled by slow computations and has changing the activecell and/or scrollrow help?

Regards

 

[MrKowz]

In your VBA code, are you toggling screenupdating? I'd be curious to see your code, as I've never seen an instance where the scrollrow of Excel has had an effect on computations.

 

[jdsouza]

In your VBA code, are you toggling screenupdating? I'd be curious to see your code, as I've never seen an instance where the scrollrow of Excel has had an effect on computations.

Yes, of course, I shut the screen updating as well as set the calculations to manual. There are no formulae in the cells. The computations are intensive. The results of those computations are used to populate 4 worksheets with values and with fonts as meaningful.

The input data is for approx 1.5 months (around 45 rows). As mentioned before, the whole of the input data (45 or so cells) is taken into memory, processed and the results are exhibited. The odd thing was the time taken depending on which cell was active or rather which the scrollrow was, which are somewhat interrelated, particularly when the user is entering data for the latter dates - scrollrow will increase as the activecell row increases, depending on the size of the window.

In the beginning, when the amount of taken for each computation started to bother me, I tried with removing the freezepanes and setting it back after the computation - but that did not help. What did was my setting the SR and SC to 1.

Thought I'd (1) get confirmation from others about this and (2) save my fellow programmers some heartache if time of computations was an issue.

Wish I could show you the code, however, it is secret The tickcount for a 2.5 second routine is reduced by a second by changing SR and SC to 1 and while this 2.5 seconds may not seem dramatic, when it comes to 5 seconds brought down to 2.5~3, the time saved is palpable and appreciated.

 

[MARK858]

while this 2.5 seconds may not seem dramatic

2.5 seconds seems pretty dramatic for processing only 45 or so cells in memory but then we don't know what processing you are doing or how you are you are using the code to nteract with the sheet.

 

[jdsouza]

2.5 seconds seems pretty dramatic for processing only 45 or so cells in memory but then we don't know what processing you are doing or how you are you are using the code to nteract with the sheet.

That's correct, the time taken is related to the amount of processing (other things like hardware, being constant) and it would be difficult for others to comment on whether 2~5 seconds is too long or not unless the code was examined. The 45 or so cells do not have simple numbers, they have long strings that have to be broken down and subjected to cascading, interrelated and iterative computations and the results are sent to 4 worksheets. Take it from me that the code is fine-tuned and even if it were not, the crux of the matter is that the active scrollrow seems to have a direct effect on the speed of the calculations - which is the reason for the post.

 

[MARK858]

the active scrollrow seems to have a direct effect on the speed of the calculations - which is the reason for the post

How does the active scrollrow affect it if you are sending the results to 4 worksheets. I don't see how that could have any effect unless you were activating each sheet?

 

[jdsouza]

How does the active scrollrow affect it if you are sending the results to 4 worksheets. I don't see how that could have any effect unless you were activating each sheet?

Mark, what the code is doing is irrelevant to the discovery that if ... as in two examples below (Imagine the data entry worksheet has rows 1 to 45, column B for user input) the remaining cells all around this area on the active worksheet and 3 others, are computed / formatted results).

Scenario 1. The active cell is in row 40 - Scrollrow is, say 20. The user edits and hits the Enter key -> time taken 2.8 seconds
Scenario 2. The active cell is in row 5 - Scrollrow is 1. The user edits and hits the Enter key -> time taken 0.85 seconds

• (To be sure, no change in data is made, the user double-clicks inside the cell to 'Edit' and presses the Enter key.
• The process is the same no matter which cell is edited. Cells info in rows 1 to 45 is taken into an array in the same order, processed and presented).

Nothing is changed in the two scenario except the activecell and in turn the activewindow.scrollrow.

My solution is to store the SR, set the SR and SC to 1 and at the end of the computations, bring the SR back to what it was so that the user is not flustered by a change in the screen.

Due to the common denominator, the other methods for speeding up calculations (screenupdating / manual calcs / enableevents=false / lockwindow / unprotect-protect only once are irrelevant to the issue which is that in my case, I am certain that the SR is directly affecting the time taken to compute.

To reiterate / summarise ...

1. Method used - set tickcount at start and end and print the difference, so I am not just perceiving the time but actually reading the measured times.
2. Tested hundreds of times
3. Consistent, palpable different times taken.

Conclusion - SR is directly related to the time for computation.
Question - Does anyone else concur?​

 

[MARK858]

You might state that what the code does is not irrelevant but I don't get this effect with any of the code I use that I have tested but maybe someone else will come in and give a situation where this occurs.

I will keep an eye on the thread to see if I get any enlightenment as obviously if there is a situation where this occurs then I would like to know more

 

[Peter_SSs]

.. the crux of the matter is that the active scrollrow seems to have a direct effect on the speed of the calculations

I also cannot see how this could be as a general rule.
If it is the case with your worksheet, then I suggest that there is something 'special' about the worksheet itself or your code. You say that you are unable to share your worksheet or your code so obviously we cannot investigate to see if we can discover the reasoning.

The best I could do was to try to create something that may be vaguely similar.

Scenario 1. The active cell is in row 40 - Scrollrow is, say 20. The user edits and hits the Enter key -> time taken 2.8 seconds
Scenario 2. The active cell is in row 5 - Scrollrow is 1. The user edits and hits the Enter key -> time taken 0.85 seconds

• (To be sure, no change in data is made, the user double-clicks inside the cell to 'Edit' and presses the Enter key.
• The process is the same no matter which cell is edited. Cells info in rows 1 to 45 is taken into an array in the same order, processed and presented).

Nothing is changed in the two scenario except the activecell and in turn the activewindow.scrollrow.

Since I don't have you data or complex string calculations, I set up Sheet1 with 1,000 rows of text each with a random number of characters (from 1 to 5,000 letters A-Z)
My test code then reads those 1,000 values into an array and performs a process on each character of each string, resulting in outputs to 4 different sheets.

Test 1
Scroll row somewhere between 700 & 900. Trigger the code with a worksheet change code by simply re-confirming the cell value as you described above
Time in for 3 tests
3.648 secs
3.633 secs
3.633 secs


Now scrolled to the top of the sheet (scroll row & column = 1)
Trigger the code with a worksheet change code by simply re-confirming a cell value near the top of the sheet.
Time in for 3 tests
3.641 secs
3.914 secs
3.656 secs

Definitely no quicker, which again leads me to believe, as I'm sure other respondents in the thread do, that there is something particular about your particular worksheet/data/code.

 

[jdsouza]

I also cannot see how this could be as a general rule.
You say that you are unable to share your worksheet or your code so obviously we cannot investigate to see if we can discover the reasoning.
The best I could do was to try to create something that may be vaguely similar.

Definitely no quicker, .

As your created worksheet and code is possibly not proprietary, could you please send it across and I will see if inserting certain routines is the cause. That will be a long term project as I will attempt by and by due to other pressing matters. Will update if the exercise reveals anything.