Basic Array definition and application

[Steve Finlayson]

I am creating an application that goes through 6 columns of data and
generates an average. I have got the code working for each single
column and am trying to create the best method of going to each
succesive column. first I am showing the code now used and then the
code that I think I should use. I am not sure that it is correct
though. Could someone look over the second set of code and make
suggestion or corrections. I am still learning. Thanks
Steve

Here is the code that I have started with for two columns:

Dim i As Integer, mysum As Long, Counter As Integer

Application.ScreenUpdating = False
On Error GoTo ReturnScreen

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

' Col1
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
ActiveDocument.FormFields("Text374").Result = mysum1 / counter1
Next i

' Col2
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
ActiveDocument.FormFields("Text380").Result = mysum2 / counter2
Next i
==========
I think that it would be better to use an array, if I inderstand what
they are and how they work correctly. I am proposing to use this code
to replace the previous:

Dim astrMysum(0 To 6) as Long
Dim astrCounter(0 To 6) as Integer
Dim intIndex as Integer

' Col1
For i = 155 To 191
Mysum(Index) = mysum(Index) + Val(ActiveDocument.FormFields("dropdown"
& i).Result)
If Val(ActiveDocument.FormFields("dropdown" & i).Result) <> 0 Then
Counter(Index) = counter(Index) + 1

End If
ActiveDocument.FormFields("Text374").Result = mysum(Index) /
counter(Index)
Next i

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

 

[Jezebel]

The array concept is good; but better still would be just a single block of
summation code rather than one for each column:

Dim pColumn as long
Dim pMin as long
Dim pMax as long
Dim pCounter as long
Dim pSum as long
Dim pTarget as string
Dim pResult as long

For pColumn = 1 to 6

pCounter = 0
pSum = 0
pMin = Choose(pColumn, 155, 192, .......)
pMax = Choose(pColumn, 191, ......)
pTarget = Choose(pColumn, "Text374", "Text380", ...)

For i = pMin To pMax
pResult = Val(ActiveDocument.FormFields("dropdown" & i).Result)
If pResult <> 0 Then
pCounter = pCounter + 1
pSum = pSum + pResult
end if
Next

If pCounter > 0 then
ActiveDocument.FormFields(pTarget).Result = pSum / pCounter
end if

Next


Don't use Integers -- since Windows is a 32-bit system, there's no space
saving, just extra work for the operating system.

 

[Steve Finlayson]

Jezebel,
At first I doubted that this would work but it is perfect. Thank you. I think I
even understand it!
I have added the code to prevent screen updating:
Application.ScreenUpdating = False
Should I also add the code to Return Screen in case of an error? And if so, does
the matching statement go at the very bottom of the macro so it is only accessed
when an error occurs? I am not sure how to finish this feature. Is it like this?
What prevents the macro from executing the return screen each time the macro
runs? I suppose it is not a problem if it does.

On Error GoTo ReturnScreen
entire procedure is here
ReturnScreen:
Resume ExitHere
End Sub

Thanks for the help and tutoring.
Steve

 

[Jezebel]

There are several ways to structure the error-handling code. This is my
preferred method:

Module level --
Private Const mModule as string = "MyModule"

Sub MySub()

Const pFunction as string = "MySub"
Dim pErrorResult as long

On Error Goto MySub_Error

..... Main code here ....

MySub_Exit:
Exit Sub

MySub_Error:
Select Case Err.Number
Case ....

Case else
pErrorResult = MsgBox(Err.Description, _
vbAbortRetryIgnore, _
mModule & ": " & pFunction)
End Select

if pErrorResult = vbRetry then
Resume
end if
if pErrorResult = vbIgnore then
Resume Next
end if
Resume MySub_Exit

End Sub