Re: Basic Array definition and application by Jezebel
Jezebel
Tue Jan 06 05:46:10 CST 2004
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
"Steve Finlayson" <steve@cdonsite.com> wrote in message
news:gj9kvvspc5cak5pbqdd7nufiqhisj2qkfs@4ax.com...
> 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
>
> On Tue, 6 Jan 2004 09:23:42 +1100, "Jezebel" <grocer@play.net> wrote:
>
> >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" <steve@cdonsite.com> wrote in message
> >news:mc8jvvsh4rfi6j2lumvs1hmpqfa07ef4bm@4ax.com...
> >> 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
> >>
> >
>