how to speed up displaying a massive number of cell updates?

[miless2111s]

I have some code which outputs many values to many cells (around 16 columns and 1000 rows) which takes around a minute to run.

I have been thinking that it was the calculations which were making it slow and have been investigating other options with this forum's help but writing a calculation free script to put out the same sort of volume of information produced a long run time so I suspect I also need to find a way to more quickly output information.

This is the test code:

Sub massive_print()
 Dim wb As Workbook
 Dim ws As Worksheet
 Dim trial As String
 Dim t As Single
 Application.ScreenUpdating = False
 t = timer
 Set wb = ActiveWorkbook
 Set ws = wb.Sheets("trial_print")
 trial = "test entry"
 For i = 1 To 1031
    ws.Range("a" & i).Value = trial
    ws.Range("b" & i).Value = trial
    ws.Range("c" & i).Value = trial
    ws.Range("d" & i).Value = trial
    ws.Range("g" & i).Value = trial
    ws.Range("h" & i).Value = trial
    ws.Range("i" & i).Value = trial
    ws.Range("j" & i).Value = trial
    ws.Range("l" & i).Value = trial
    ws.Range("m" & i).Value = trial
    ws.Range("n" & i).Value = trial
    ws.Range("t" & i).Value = trial
    ws.Range("u" & i).Value = trial
    ws.Range("v" & i).Value = trial
    ws.Range("w" & i).Value = trial
    ws.Range("ae" & i).Value = trial
Next i
ActiveWorkbook.Sheets("Test_scores").Range("h10").Value = timer - t
ActiveWorkbook.Sheets("Test_scores").Range("h09").Value = "time to print 1000 lines:"
Application.ScreenUpdating = True
End Sub

As I am a beginner at all this I wonder if I am going about this in a slow way? Anyone got any ideas? Note that after the first run this output will not all change every time it is run so I don't know if that something that might help in some way???

Many thanks

Miles

 

[Chihiro]

What downsides does this have - what do I need to look out for?

If and when code throws error, you'd need to remember to set it back to "xlCalculationAutomatic". Otherwise it will remain in manual mode.
Generally, I'd set up error trap.

Something like..., stick your error handle at end of code.

On Error Goto ErrHandle:
'Your Code here
ErrHandle:
    If Err.Number > 0 Then
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
    End If

 

[miless2111s]

If and when code throws error, you'd need to remember to set it back to "xlCalculationAutomatic". Otherwise it will remain in manual mode.
Generally, I'd set up error trap.

Something like..., stick your error handle at end of code.

On Error Goto ErrHandle:
'Your Code here
ErrHandle:
    If Err.Number > 0 Then
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
    End If

Reading around on the error handling I found this. Am I reading this right that I should have the On Error Goto ErrHandle: at the top of my code and then the ErrHandle: stuff below the exit Sub statement?

If I should want to have more than one error handler do I have to narrow this one down, include the code to switch calcs back on in the other error routines or do they build on each other?

 

[Chihiro]

"On Error Goto x:" is usually located just before where error is likely (or would occur). Not necessarily at very top of code.
Most of the time simple true/false operation will not require error handle in well written code.

Errors often occur as result of...
1. User error (ex. value entered in a cell does not conform to format expected)
2. Where sheet/workbook does not exist
3. When Excel cannot execute programmed code for one reason or another

Notice how the example in link you provided is missing "If Err.Number > 0 Then" line.
That's the reason why "Exit Sub" is needed. In my sample, if there is no Error, then Err.Number would be 0 and the ErrHandle portion does nothing.

Depending on your code structure, you may want to add ErrHandle (or other label) before end of code. In those cases, you'd either tell it to "Exit Sub" when the Error is encountered, or do some other error handling operation.

If I should want to have more than one error handler do I have to narrow this one down, include the code to switch calcs back on in the other error routines or do they build on each other?

If Error trap resides at line above loop, you can "Resume Next" in ErrHandle routine to return to loop. Otherwise, my example would terminate the entire process when error is encountered.

For more detail on Error handling, see Chip Pearson's site.
Error Handling In VBA