Hiding the looping calculations

[Steve Finlayson]

I have an application where I am calculating the average of columns in
a form table. The calculation works fine but it is somewhat slow and
it causes the monitor to blink for about a minute as it loops through
all the cells. Is there a way to hide the calculation, to turn the
"echo" off so it went peacefully through the loop and then presented
the result? I am hoping that this may speed it up a little.

Here is the code I am currently using, based on help from Doug
Robbins. I am not sure it works on the multiple columns with the
numbered mysum and counter variables. I would welcome comments on that
also.
Thanks - Steve

Dim i As Integer, mysum As Long, Counter As Integer
ActiveDocument.Unprotect
mysum1 = 0
counter1 = 0
mysum2 = 0
counter2 = 0
mysum3 = 0
counter3 = 0
mysum4 = 0
counter4 = 0
mysum5 = 0
counter5 = 0
mysum6 = 0
counter6 = 0

' Carpet
For i = 155 To 191
mysum1 = mysum1 + Val(ActiveDocument.FormFields("dropdown" &
i).Result)
If Val(ActiveDocument.FormFields("dropdown" & i).Result) <> 0 Then
counter1 = counter1 + 1
End If
Next i
ActiveDocument.FormFields("Text374").Result = mysum1 / counter1

' Tile
For i = 192 To 228
mysum2 = mysum2 + Val(ActiveDocument.FormFields("dropdown" &
i).Result)
If Val(ActiveDocument.FormFields("dropdown" & i).Result) <> 0 Then
counter2 = counter2 + 1
End If
Next i
ActiveDocument.FormFields("Text380").Result = mysum2 / counter2


ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True
End Sub

 

[Perry]

all the cells. Is there a way to hide the calculation, to turn the

"echo" off so it went peacefully through the loop and then presented
the result? I am hoping that this may speed it up a little.

I don't know whether this will speed things up, but the below code
sequence will 'hide' the screen actions:

Application.ScreenUpdating = False
On Error Goto ReturnScreen
'<the rest of the actions goes here
ExitHere:
Application.ScreenRefresh
Application.ScreenUpdating = True
Exit Sub
ReturnScreen:
Resume ExitHere

Krgrds,
Perry

 

[Alex Ivanov]

Steve,
You may want to set
Application.ScreenUpdating=False
at the beginning of your procedure and back to true at the end.
If you assign Val(ActiveDocument.FormFields("dropdown" & i).Result) to a
temp variable,
it may significantly speed up the entire process. I would rearrange your
code like this:

tmp=Val(ActiveDocument.FormFields("dropdown" & i).Result)
If tmp <> 0 Then
counter1 = counter1 + 1
mysum1 = mysum1 + tmp
End If

In this case you have to call Formfields().Result only once per iteration
and it is the most time consuming statement in your code sample.

HTH,
Alex.