How can I make my code run faster?

Tutorials Win: Microsoft Windows Forum

I have a MS Word 2003 form in which I have 40 checkboxes.

This code below works great but it is r-e-a-l-l-y slow. The reason being
that it is checking each checkbox every time. What I'd like to do is to
speed it up.

Can someone help? I aprpeciate it greatly.....

Function Calculate()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim intWait As Integer

StatusBar = "Please wait..."

For intWait = 1 To 1000
System.Cursor = wdCursorWait
Next intWait

Dim mI As Integer
Dim mColRate As Integer
Dim mCol1Tot As Integer, mCol2Tot As Integer, mCol3Tot As Integer
'look at each item that is a formfield on the active document
For mI = 1 To ActiveDocument.FormFields.Count
'this is checking if this formfield is a checkbox vs a textbox ...
If ActiveDocument.FormFields(mI).Type = wdFieldFormCheckBox Then
'this is looking for something in the name of the checkbox which
keys the loop
'that I want to do something with this particular checkbox
If InStr(1, ActiveDocument.FormFields(mI).Name, "Ineffective") >
0 Or _
InStr(1, ActiveDocument.FormFields(mI).Name, "Capable") > 0
Or _
InStr(1, ActiveDocument.FormFields(mI).Name, "NA") > 0 Or _
InStr(1, ActiveDocument.FormFields(mI).Name, "Superior") > 0
Then
'this counts all boxes that need to be counted in the above
list
mColRate = mColRate + 1
'determines which column needs to be counted and keep a total
If InStr(1, ActiveDocument.FormFields(mI).Name,
"Ineffective1") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol1Tot = mCol1Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Capable1") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol2Tot = mCol2Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Superior1") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol3Tot = mCol3Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Ineffective2") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol1Tot = mCol1Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Capable2") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol2Tot = mCol2Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Superior2") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol3Tot = mCol3Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Ineffective3") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol1Tot = mCol1Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Capable3") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol2Tot = mCol2Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Superior3") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol3Tot = mCol3Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Ineffective4") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol1Tot = mCol1Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Capable4") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol2Tot = mCol2Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Superior4") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol3Tot = mCol3Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Ineffective5") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol1Tot = mCol1Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Capable5") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol2Tot = mCol2Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Superior5") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol3Tot = mCol3Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Ineffective6") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol1Tot = mCol1Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Capable6") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol2Tot = mCol2Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Superior6") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol3Tot = mCol3Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Ineffective7") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol1Tot = mCol1Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Capable7") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol2Tot = mCol2Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Superior7") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol3Tot = mCol3Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Ineffective8") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol1Tot = mCol1Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Capable8") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol2Tot = mCol2Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Superior8") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol3Tot = mCol3Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Ineffective9") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol1Tot = mCol1Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Capable9") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol2Tot = mCol2Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Superior9") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol3Tot = mCol3Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Ineffective10") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol1Tot = mCol1Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Capable10") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol2Tot = mCol2Tot + 1
ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
"Superior10") > 0 And _
ActiveDocument.FormFields(mI).CheckBox.Value = True Then
mCol3Tot = mCol3Tot + 1


End If
End If
End If

Next
ActiveDocument.FormFields("Col1Tot").Result = mCol1Tot
ActiveDocument.FormFields("Col2Tot").Result = mCol2Tot * 2
ActiveDocument.FormFields("Col3Tot").Result = mCol3Tot * 3
ActiveDocument.FormFields("Total").Result = ((mCol3Tot * 3) + (mCol2Tot
* 2) + mCol1Tot) / 40

MsgBox ("Calculation Completed")

StatusBar = "Task completed"
System.Cursor = wdCursorNormal
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Function
Top

Re: How can I make my code run faster? by Jonathan

Jonathan
Thu May 25 08:35:50 CDT 2006

Hi worsty

The code below should run a lot faster. I've made the following changes

1. I've used a For Each-Next loop instead of a For-Next loop. For large
collections, this is often much quicker than referencing each item in the
collection by its index number.

2. At the start of the loop, I've put the name & value of the field into a
couple of variables, where they can be accessed much more quickly than
repeated references to the formfield itself.

3. Since none of the additions to the totals occur if the checkbox value is
False, I've made that an initial comparison and skipped the whole of the
If-Then-ElseIf-EndIf block if it is False.

Function Calculate()
Dim oField As FormField
Dim bValue As Boolean
Dim sName As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim intWait As Integer

StatusBar = "Please wait..."

For intWait = 1 To 1000
System.Cursor = wdCursorWait
Next intWait

Dim mI As Integer
Dim mColRate As Integer
Dim mCol1Tot As Integer, mCol2Tot As Integer, mCol3Tot As Integer
'look at each item that is a formfield on the active document
For Each oField In ActiveDocument.FormFields
'this is checking if this formfield is a checkbox vs a textbox ...
If oField.Type = wdFieldFormCheckBox Then
'this is looking for something in the name of the checkbox which keys
the loop
'that I want to do something with this particular checkbox

'Cache the name and checkbox value of the field
sName = oField.Name
bValue = oField.CheckBox.Value

If InStr(1, sName, "Ineffective") > 0 Or _
InStr(1, sName, "Capable") > 0 Or _
InStr(1, sName, "NA") > 0 Or _
InStr(1, sName, "Superior") > 0 Then
'this counts all boxes that need to be counted in the above List
mColRate = mColRate + 1
'determines which column needs to be counted and keep a total
If bValue Then
If InStr(1, sName, "Ineffective1") > 0 Then
mCol1Tot = mCol1Tot + 1
ElseIf InStr(1, sName, "Capable1") > 0 Then
mCol2Tot = mCol2Tot + 1
ElseIf InStr(1, sName, "Superior1") > 0 Then
mCol3Tot = mCol3Tot + 1
ElseIf InStr(1, sName, "Ineffective2") > 0 Then
mCol1Tot = mCol1Tot + 1
ElseIf InStr(1, sName, "Capable2") > 0 Then
mCol2Tot = mCol2Tot + 1
ElseIf InStr(1, sName, "Superior2") > 0 Then
mCol3Tot = mCol3Tot + 1
ElseIf InStr(1, sName, "Ineffective3") > 0 Then
mCol1Tot = mCol1Tot + 1
ElseIf InStr(1, sName, "Capable3") > 0 Then
mCol2Tot = mCol2Tot + 1
ElseIf InStr(1, sName, "Superior3") > 0 Then
mCol3Tot = mCol3Tot + 1
ElseIf InStr(1, sName, "Ineffective4") > 0 Then
mCol1Tot = mCol1Tot + 1
ElseIf InStr(1, sName, "Capable4") > 0 Then
mCol2Tot = mCol2Tot + 1
ElseIf InStr(1, sName, "Superior4") > 0 Then
mCol3Tot = mCol3Tot + 1
ElseIf InStr(1, sName, "Ineffective5") > 0 Then
mCol1Tot = mCol1Tot + 1
ElseIf InStr(1, sName, "Capable5") > 0 Then
mCol2Tot = mCol2Tot + 1
ElseIf InStr(1, sName, "Superior5") > 0 Then
mCol3Tot = mCol3Tot + 1
ElseIf InStr(1, sName, "Ineffective6") > 0 Then
mCol1Tot = mCol1Tot + 1
ElseIf InStr(1, sName, "Capable6") > 0 Then
mCol2Tot = mCol2Tot + 1
ElseIf InStr(1, sName, "Superior6") > 0 Then
mCol3Tot = mCol3Tot + 1
ElseIf InStr(1, sName, "Ineffective7") > 0 Then
mCol1Tot = mCol1Tot + 1
ElseIf InStr(1, sName, "Capable7") > 0 Then
mCol2Tot = mCol2Tot + 1
ElseIf InStr(1, sName, "Superior7") > 0 Then
mCol3Tot = mCol3Tot + 1
ElseIf InStr(1, sName, "Ineffective8") > 0 Then
mCol1Tot = mCol1Tot + 1
ElseIf InStr(1, sName, "Capable8") > 0 Then
mCol2Tot = mCol2Tot + 1
ElseIf InStr(1, sName, "Superior8") > 0 Then
mCol3Tot = mCol3Tot + 1
ElseIf InStr(1, sName, "Ineffective9") > 0 Then
mCol1Tot = mCol1Tot + 1
ElseIf InStr(1, sName, "Capable9") > 0 Then
mCol2Tot = mCol2Tot + 1
ElseIf InStr(1, sName, "Superior9") > 0 Then
mCol3Tot = mCol3Tot + 1
ElseIf InStr(1, sName, "Ineffective10") > 0 Then
mCol1Tot = mCol1Tot + 1
ElseIf InStr(1, sName, "Capable10") > 0 Then
mCol2Tot = mCol2Tot + 1
ElseIf InStr(1, sName, "Superior10") > 0 Then
mCol3Tot = mCol3Tot + 1
End If
End If
End If
End If

Next
ActiveDocument.FormFields("Col1Tot").Result = mCol1Tot
ActiveDocument.FormFields("Col2Tot").Result = mCol2Tot * 2
ActiveDocument.FormFields("Col3Tot").Result = mCol3Tot * 3
ActiveDocument.FormFields("Total").Result = ((mCol3Tot * 3) + (mCol2Tot *
2) + mCol1Tot) / 40

MsgBox ("Calculation Completed")

StatusBar = "Task completed"
System.Cursor = wdCursorNormal
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Function


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org


"Worsty" <Worsty@discussions.microsoft.com> wrote in message
news:2141B90E-FEFF-425D-8F00-877B7896CCDD@microsoft.com...
>I have a MS Word 2003 form in which I have 40 checkboxes.
>
> This code below works great but it is r-e-a-l-l-y slow. The reason being
> that it is checking each checkbox every time. What I'd like to do is to
> speed it up.
>
> Can someone help? I aprpeciate it greatly.....
>
> Function Calculate()
> Application.DisplayAlerts = False
> Application.ScreenUpdating = False
> Dim intWait As Integer
>
> StatusBar = "Please wait..."
>
> For intWait = 1 To 1000
> System.Cursor = wdCursorWait
> Next intWait
>
> Dim mI As Integer
> Dim mColRate As Integer
> Dim mCol1Tot As Integer, mCol2Tot As Integer, mCol3Tot As Integer
> 'look at each item that is a formfield on the active document
> For mI = 1 To ActiveDocument.FormFields.Count
> 'this is checking if this formfield is a checkbox vs a textbox ...
> If ActiveDocument.FormFields(mI).Type = wdFieldFormCheckBox Then
> 'this is looking for something in the name of the checkbox
> which
> keys the loop
> 'that I want to do something with this particular checkbox
> If InStr(1, ActiveDocument.FormFields(mI).Name, "Ineffective")
> >
> 0 Or _
> InStr(1, ActiveDocument.FormFields(mI).Name, "Capable") > 0
> Or _
> InStr(1, ActiveDocument.FormFields(mI).Name, "NA") > 0 Or _
> InStr(1, ActiveDocument.FormFields(mI).Name, "Superior") >
> 0
> Then
> 'this counts all boxes that need to be counted in the above
> list
> mColRate = mColRate + 1
> 'determines which column needs to be counted and keep a
> total
> If InStr(1, ActiveDocument.FormFields(mI).Name,
> "Ineffective1") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol1Tot = mCol1Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Capable1") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol2Tot = mCol2Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Superior1") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol3Tot = mCol3Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Ineffective2") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol1Tot = mCol1Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Capable2") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol2Tot = mCol2Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Superior2") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol3Tot = mCol3Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Ineffective3") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol1Tot = mCol1Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Capable3") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol2Tot = mCol2Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Superior3") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol3Tot = mCol3Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Ineffective4") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol1Tot = mCol1Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Capable4") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol2Tot = mCol2Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Superior4") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol3Tot = mCol3Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Ineffective5") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol1Tot = mCol1Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Capable5") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol2Tot = mCol2Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Superior5") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol3Tot = mCol3Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Ineffective6") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol1Tot = mCol1Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Capable6") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol2Tot = mCol2Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Superior6") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol3Tot = mCol3Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Ineffective7") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol1Tot = mCol1Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Capable7") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol2Tot = mCol2Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Superior7") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol3Tot = mCol3Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Ineffective8") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol1Tot = mCol1Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Capable8") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol2Tot = mCol2Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Superior8") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol3Tot = mCol3Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Ineffective9") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol1Tot = mCol1Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Capable9") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol2Tot = mCol2Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Superior9") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol3Tot = mCol3Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Ineffective10") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol1Tot = mCol1Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Capable10") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol2Tot = mCol2Tot + 1
> ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> "Superior10") > 0 And _
> ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> mCol3Tot = mCol3Tot + 1
>
>
> End If
> End If
> End If
>
> Next
> ActiveDocument.FormFields("Col1Tot").Result = mCol1Tot
> ActiveDocument.FormFields("Col2Tot").Result = mCol2Tot * 2
> ActiveDocument.FormFields("Col3Tot").Result = mCol3Tot * 3
> ActiveDocument.FormFields("Total").Result = ((mCol3Tot * 3) + (mCol2Tot
> * 2) + mCol1Tot) / 40
>
> MsgBox ("Calculation Completed")
>
> StatusBar = "Task completed"
> System.Cursor = wdCursorNormal
> Application.DisplayAlerts = True
> Application.ScreenUpdating = True
> End Function
>

Top

Re: How can I make my code run faster? by Worsty

Worsty
Thu May 25 08:50:02 CDT 2006

Jonathan:

Thank you....you are correct the code runs much quicker.

I have another question. Since they are checkboxes is there a way to
validate that only one checkbox is selected in each row?

"Jonathan West" wrote:

> Hi worsty
>
> The code below should run a lot faster. I've made the following changes
>
> 1. I've used a For Each-Next loop instead of a For-Next loop. For large
> collections, this is often much quicker than referencing each item in the
> collection by its index number.
>
> 2. At the start of the loop, I've put the name & value of the field into a
> couple of variables, where they can be accessed much more quickly than
> repeated references to the formfield itself.
>
> 3. Since none of the additions to the totals occur if the checkbox value is
> False, I've made that an initial comparison and skipped the whole of the
> If-Then-ElseIf-EndIf block if it is False.
>
> Function Calculate()
> Dim oField As FormField
> Dim bValue As Boolean
> Dim sName As String
> Application.DisplayAlerts = False
> Application.ScreenUpdating = False
> Dim intWait As Integer
>
> StatusBar = "Please wait..."
>
> For intWait = 1 To 1000
> System.Cursor = wdCursorWait
> Next intWait
>
> Dim mI As Integer
> Dim mColRate As Integer
> Dim mCol1Tot As Integer, mCol2Tot As Integer, mCol3Tot As Integer
> 'look at each item that is a formfield on the active document
> For Each oField In ActiveDocument.FormFields
> 'this is checking if this formfield is a checkbox vs a textbox ...
> If oField.Type = wdFieldFormCheckBox Then
> 'this is looking for something in the name of the checkbox which keys
> the loop
> 'that I want to do something with this particular checkbox
>
> 'Cache the name and checkbox value of the field
> sName = oField.Name
> bValue = oField.CheckBox.Value
>
> If InStr(1, sName, "Ineffective") > 0 Or _
> InStr(1, sName, "Capable") > 0 Or _
> InStr(1, sName, "NA") > 0 Or _
> InStr(1, sName, "Superior") > 0 Then
> 'this counts all boxes that need to be counted in the above List
> mColRate = mColRate + 1
> 'determines which column needs to be counted and keep a total
> If bValue Then
> If InStr(1, sName, "Ineffective1") > 0 Then
> mCol1Tot = mCol1Tot + 1
> ElseIf InStr(1, sName, "Capable1") > 0 Then
> mCol2Tot = mCol2Tot + 1
> ElseIf InStr(1, sName, "Superior1") > 0 Then
> mCol3Tot = mCol3Tot + 1
> ElseIf InStr(1, sName, "Ineffective2") > 0 Then
> mCol1Tot = mCol1Tot + 1
> ElseIf InStr(1, sName, "Capable2") > 0 Then
> mCol2Tot = mCol2Tot + 1
> ElseIf InStr(1, sName, "Superior2") > 0 Then
> mCol3Tot = mCol3Tot + 1
> ElseIf InStr(1, sName, "Ineffective3") > 0 Then
> mCol1Tot = mCol1Tot + 1
> ElseIf InStr(1, sName, "Capable3") > 0 Then
> mCol2Tot = mCol2Tot + 1
> ElseIf InStr(1, sName, "Superior3") > 0 Then
> mCol3Tot = mCol3Tot + 1
> ElseIf InStr(1, sName, "Ineffective4") > 0 Then
> mCol1Tot = mCol1Tot + 1
> ElseIf InStr(1, sName, "Capable4") > 0 Then
> mCol2Tot = mCol2Tot + 1
> ElseIf InStr(1, sName, "Superior4") > 0 Then
> mCol3Tot = mCol3Tot + 1
> ElseIf InStr(1, sName, "Ineffective5") > 0 Then
> mCol1Tot = mCol1Tot + 1
> ElseIf InStr(1, sName, "Capable5") > 0 Then
> mCol2Tot = mCol2Tot + 1
> ElseIf InStr(1, sName, "Superior5") > 0 Then
> mCol3Tot = mCol3Tot + 1
> ElseIf InStr(1, sName, "Ineffective6") > 0 Then
> mCol1Tot = mCol1Tot + 1
> ElseIf InStr(1, sName, "Capable6") > 0 Then
> mCol2Tot = mCol2Tot + 1
> ElseIf InStr(1, sName, "Superior6") > 0 Then
> mCol3Tot = mCol3Tot + 1
> ElseIf InStr(1, sName, "Ineffective7") > 0 Then
> mCol1Tot = mCol1Tot + 1
> ElseIf InStr(1, sName, "Capable7") > 0 Then
> mCol2Tot = mCol2Tot + 1
> ElseIf InStr(1, sName, "Superior7") > 0 Then
> mCol3Tot = mCol3Tot + 1
> ElseIf InStr(1, sName, "Ineffective8") > 0 Then
> mCol1Tot = mCol1Tot + 1
> ElseIf InStr(1, sName, "Capable8") > 0 Then
> mCol2Tot = mCol2Tot + 1
> ElseIf InStr(1, sName, "Superior8") > 0 Then
> mCol3Tot = mCol3Tot + 1
> ElseIf InStr(1, sName, "Ineffective9") > 0 Then
> mCol1Tot = mCol1Tot + 1
> ElseIf InStr(1, sName, "Capable9") > 0 Then
> mCol2Tot = mCol2Tot + 1
> ElseIf InStr(1, sName, "Superior9") > 0 Then
> mCol3Tot = mCol3Tot + 1
> ElseIf InStr(1, sName, "Ineffective10") > 0 Then
> mCol1Tot = mCol1Tot + 1
> ElseIf InStr(1, sName, "Capable10") > 0 Then
> mCol2Tot = mCol2Tot + 1
> ElseIf InStr(1, sName, "Superior10") > 0 Then
> mCol3Tot = mCol3Tot + 1
> End If
> End If
> End If
> End If
>
> Next
> ActiveDocument.FormFields("Col1Tot").Result = mCol1Tot
> ActiveDocument.FormFields("Col2Tot").Result = mCol2Tot * 2
> ActiveDocument.FormFields("Col3Tot").Result = mCol3Tot * 3
> ActiveDocument.FormFields("Total").Result = ((mCol3Tot * 3) + (mCol2Tot *
> 2) + mCol1Tot) / 40
>
> MsgBox ("Calculation Completed")
>
> StatusBar = "Task completed"
> System.Cursor = wdCursorNormal
> Application.DisplayAlerts = True
> Application.ScreenUpdating = True
> End Function
>
>
> --
> Regards
> Jonathan West - Word MVP
> www.intelligentdocuments.co.uk
> Please reply to the newsgroup
> Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org
>
>
> "Worsty" <Worsty@discussions.microsoft.com> wrote in message
> news:2141B90E-FEFF-425D-8F00-877B7896CCDD@microsoft.com...
> >I have a MS Word 2003 form in which I have 40 checkboxes.
> >
> > This code below works great but it is r-e-a-l-l-y slow. The reason being
> > that it is checking each checkbox every time. What I'd like to do is to
> > speed it up.
> >
> > Can someone help? I aprpeciate it greatly.....
> >
> > Function Calculate()
> > Application.DisplayAlerts = False
> > Application.ScreenUpdating = False
> > Dim intWait As Integer
> >
> > StatusBar = "Please wait..."
> >
> > For intWait = 1 To 1000
> > System.Cursor = wdCursorWait
> > Next intWait
> >
> > Dim mI As Integer
> > Dim mColRate As Integer
> > Dim mCol1Tot As Integer, mCol2Tot As Integer, mCol3Tot As Integer
> > 'look at each item that is a formfield on the active document
> > For mI = 1 To ActiveDocument.FormFields.Count
> > 'this is checking if this formfield is a checkbox vs a textbox ...
> > If ActiveDocument.FormFields(mI).Type = wdFieldFormCheckBox Then
> > 'this is looking for something in the name of the checkbox
> > which
> > keys the loop
> > 'that I want to do something with this particular checkbox
> > If InStr(1, ActiveDocument.FormFields(mI).Name, "Ineffective")
> > >
> > 0 Or _
> > InStr(1, ActiveDocument.FormFields(mI).Name, "Capable") > 0
> > Or _
> > InStr(1, ActiveDocument.FormFields(mI).Name, "NA") > 0 Or _
> > InStr(1, ActiveDocument.FormFields(mI).Name, "Superior") >
> > 0
> > Then
> > 'this counts all boxes that need to be counted in the above
> > list
> > mColRate = mColRate + 1
> > 'determines which column needs to be counted and keep a
> > total
> > If InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Ineffective1") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol1Tot = mCol1Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Capable1") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol2Tot = mCol2Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Superior1") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol3Tot = mCol3Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Ineffective2") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol1Tot = mCol1Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Capable2") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol2Tot = mCol2Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Superior2") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol3Tot = mCol3Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Ineffective3") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol1Tot = mCol1Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Capable3") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol2Tot = mCol2Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Superior3") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol3Tot = mCol3Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Ineffective4") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol1Tot = mCol1Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Capable4") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol2Tot = mCol2Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Superior4") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol3Tot = mCol3Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Ineffective5") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol1Tot = mCol1Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Capable5") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol2Tot = mCol2Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Superior5") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol3Tot = mCol3Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Ineffective6") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol1Tot = mCol1Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Capable6") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol2Tot = mCol2Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Superior6") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol3Tot = mCol3Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Ineffective7") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol1Tot = mCol1Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Capable7") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol2Tot = mCol2Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Superior7") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol3Tot = mCol3Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Ineffective8") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol1Tot = mCol1Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Capable8") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol2Tot = mCol2Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Superior8") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol3Tot = mCol3Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Ineffective9") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol1Tot = mCol1Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Capable9") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol2Tot = mCol2Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Superior9") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol3Tot = mCol3Tot + 1
> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
> > "Ineffective10") > 0 And _
> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
> > mCol1Tot = mCol1Tot + 1
Top

Re: How can I make my code run faster? by Jonathan

Jonathan
Thu May 25 09:12:13 CDT 2006

I can't see your document, so I can't see which checkboxes are in which
column.

--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

"Worsty" <Worsty@discussions.microsoft.com> wrote in message
news:10C5452C-39CB-4706-ACBC-7F23DBBB2472@microsoft.com...
> Jonathan:
>
> Thank you....you are correct the code runs much quicker.
>
> I have another question. Since they are checkboxes is there a way to
> validate that only one checkbox is selected in each row?
>
> "Jonathan West" wrote:
>
>> Hi worsty
>>
>> The code below should run a lot faster. I've made the following changes
>>
>> 1. I've used a For Each-Next loop instead of a For-Next loop. For large
>> collections, this is often much quicker than referencing each item in the
>> collection by its index number.
>>
>> 2. At the start of the loop, I've put the name & value of the field into
>> a
>> couple of variables, where they can be accessed much more quickly than
>> repeated references to the formfield itself.
>>
>> 3. Since none of the additions to the totals occur if the checkbox value
>> is
>> False, I've made that an initial comparison and skipped the whole of the
>> If-Then-ElseIf-EndIf block if it is False.
>>
>> Function Calculate()
>> Dim oField As FormField
>> Dim bValue As Boolean
>> Dim sName As String
>> Application.DisplayAlerts = False
>> Application.ScreenUpdating = False
>> Dim intWait As Integer
>>
>> StatusBar = "Please wait..."
>>
>> For intWait = 1 To 1000
>> System.Cursor = wdCursorWait
>> Next intWait
>>
>> Dim mI As Integer
>> Dim mColRate As Integer
>> Dim mCol1Tot As Integer, mCol2Tot As Integer, mCol3Tot As Integer
>> 'look at each item that is a formfield on the active document
>> For Each oField In ActiveDocument.FormFields
>> 'this is checking if this formfield is a checkbox vs a textbox ...
>> If oField.Type = wdFieldFormCheckBox Then
>> 'this is looking for something in the name of the checkbox which
>> keys
>> the loop
>> 'that I want to do something with this particular checkbox
>>
>> 'Cache the name and checkbox value of the field
>> sName = oField.Name
>> bValue = oField.CheckBox.Value
>>
>> If InStr(1, sName, "Ineffective") > 0 Or _
>> InStr(1, sName, "Capable") > 0 Or _
>> InStr(1, sName, "NA") > 0 Or _
>> InStr(1, sName, "Superior") > 0 Then
>> 'this counts all boxes that need to be counted in the above List
>> mColRate = mColRate + 1
>> 'determines which column needs to be counted and keep a total
>> If bValue Then
>> If InStr(1, sName, "Ineffective1") > 0 Then
>> mCol1Tot = mCol1Tot + 1
>> ElseIf InStr(1, sName, "Capable1") > 0 Then
>> mCol2Tot = mCol2Tot + 1
>> ElseIf InStr(1, sName, "Superior1") > 0 Then
>> mCol3Tot = mCol3Tot + 1
>> ElseIf InStr(1, sName, "Ineffective2") > 0 Then
>> mCol1Tot = mCol1Tot + 1
>> ElseIf InStr(1, sName, "Capable2") > 0 Then
>> mCol2Tot = mCol2Tot + 1
>> ElseIf InStr(1, sName, "Superior2") > 0 Then
>> mCol3Tot = mCol3Tot + 1
>> ElseIf InStr(1, sName, "Ineffective3") > 0 Then
>> mCol1Tot = mCol1Tot + 1
>> ElseIf InStr(1, sName, "Capable3") > 0 Then
>> mCol2Tot = mCol2Tot + 1
>> ElseIf InStr(1, sName, "Superior3") > 0 Then
>> mCol3Tot = mCol3Tot + 1
>> ElseIf InStr(1, sName, "Ineffective4") > 0 Then
>> mCol1Tot = mCol1Tot + 1
>> ElseIf InStr(1, sName, "Capable4") > 0 Then
>> mCol2Tot = mCol2Tot + 1
>> ElseIf InStr(1, sName, "Superior4") > 0 Then
>> mCol3Tot = mCol3Tot + 1
>> ElseIf InStr(1, sName, "Ineffective5") > 0 Then
>> mCol1Tot = mCol1Tot + 1
>> ElseIf InStr(1, sName, "Capable5") > 0 Then
>> mCol2Tot = mCol2Tot + 1
>> ElseIf InStr(1, sName, "Superior5") > 0 Then
>> mCol3Tot = mCol3Tot + 1
>> ElseIf InStr(1, sName, "Ineffective6") > 0 Then
>> mCol1Tot = mCol1Tot + 1
>> ElseIf InStr(1, sName, "Capable6") > 0 Then
>> mCol2Tot = mCol2Tot + 1
>> ElseIf InStr(1, sName, "Superior6") > 0 Then
>> mCol3Tot = mCol3Tot + 1
>> ElseIf InStr(1, sName, "Ineffective7") > 0 Then
>> mCol1Tot = mCol1Tot + 1
>> ElseIf InStr(1, sName, "Capable7") > 0 Then
>> mCol2Tot = mCol2Tot + 1
>> ElseIf InStr(1, sName, "Superior7") > 0 Then
>> mCol3Tot = mCol3Tot + 1
>> ElseIf InStr(1, sName, "Ineffective8") > 0 Then
>> mCol1Tot = mCol1Tot + 1
>> ElseIf InStr(1, sName, "Capable8") > 0 Then
>> mCol2Tot = mCol2Tot + 1
>> ElseIf InStr(1, sName, "Superior8") > 0 Then
>> mCol3Tot = mCol3Tot + 1
>> ElseIf InStr(1, sName, "Ineffective9") > 0 Then
>> mCol1Tot = mCol1Tot + 1
>> ElseIf InStr(1, sName, "Capable9") > 0 Then
>> mCol2Tot = mCol2Tot + 1
>> ElseIf InStr(1, sName, "Superior9") > 0 Then
>> mCol3Tot = mCol3Tot + 1
>> ElseIf InStr(1, sName, "Ineffective10") > 0 Then
>> mCol1Tot = mCol1Tot + 1
>> ElseIf InStr(1, sName, "Capable10") > 0 Then
>> mCol2Tot = mCol2Tot + 1
>> ElseIf InStr(1, sName, "Superior10") > 0 Then
>> mCol3Tot = mCol3Tot + 1
>> End If
>> End If
>> End If
>> End If
>>
>> Next
>> ActiveDocument.FormFields("Col1Tot").Result = mCol1Tot
>> ActiveDocument.FormFields("Col2Tot").Result = mCol2Tot * 2
>> ActiveDocument.FormFields("Col3Tot").Result = mCol3Tot * 3
>> ActiveDocument.FormFields("Total").Result = ((mCol3Tot * 3) + (mCol2Tot
>> *
>> 2) + mCol1Tot) / 40
>>
>> MsgBox ("Calculation Completed")
>>
>> StatusBar = "Task completed"
>> System.Cursor = wdCursorNormal
>> Application.DisplayAlerts = True
>> Application.ScreenUpdating = True
>> End Function
>>
>>
>> --
>> Regards
>> Jonathan West - Word MVP
>> www.intelligentdocuments.co.uk
>> Please reply to the newsgroup
>> Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org
>>
>>
>> "Worsty" <Worsty@discussions.microsoft.com> wrote in message
>> news:2141B90E-FEFF-425D-8F00-877B7896CCDD@microsoft.com...
>> >I have a MS Word 2003 form in which I have 40 checkboxes.
>> >
>> > This code below works great but it is r-e-a-l-l-y slow. The reason
>> > being
>> > that it is checking each checkbox every time. What I'd like to do is
>> > to
>> > speed it up.
>> >
>> > Can someone help? I aprpeciate it greatly.....
>> >
>> > Function Calculate()
>> > Application.DisplayAlerts = False
>> > Application.ScreenUpdating = False
>> > Dim intWait As Integer
>> >
>> > StatusBar = "Please wait..."
>> >
>> > For intWait = 1 To 1000
>> > System.Cursor = wdCursorWait
>> > Next intWait
>> >
>> > Dim mI As Integer
>> > Dim mColRate As Integer
>> > Dim mCol1Tot As Integer, mCol2Tot As Integer, mCol3Tot As Integer
>> > 'look at each item that is a formfield on the active document
>> > For mI = 1 To ActiveDocument.FormFields.Count
>> > 'this is checking if this formfield is a checkbox vs a textbox
>> > ...
>> > If ActiveDocument.FormFields(mI).Type = wdFieldFormCheckBox Then
>> > 'this is looking for something in the name of the checkbox
>> > which
>> > keys the loop
>> > 'that I want to do something with this particular checkbox
>> > If InStr(1, ActiveDocument.FormFields(mI).Name,
>> > "Ineffective")
>> > >
>> > 0 Or _
>> > InStr(1, ActiveDocument.FormFields(mI).Name, "Capable")
>> > > 0
>> > Or _
>> > InStr(1, ActiveDocument.FormFields(mI).Name, "NA") > 0
>> > Or _
>> > InStr(1, ActiveDocument.FormFields(mI).Name, "Superior")
>> > >
>> > 0
>> > Then
>> > 'this counts all boxes that need to be counted in the
>> > above
>> > list
>> > mColRate = mColRate + 1
>> > 'determines which column needs to be counted and keep a
>> > total
>> > If InStr(1, ActiveDocument.FormFields(mI).Name,
>> > "Ineffective1") > 0 And _
>> > ActiveDocument.FormFields(mI).CheckBox.Value = True
>> > Then
>> > mCol1Tot = mCol1Tot + 1
>> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
>> > "Capable1") > 0 And _
>> > ActiveDocument.FormFields(mI).CheckBox.Value = True
>> > Then
>> > mCol2Tot = mCol2Tot + 1
>> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
>> > "Superior1") > 0 And _
>> > ActiveDocument.FormFields(mI).CheckBox.Value = True
>> > Then
>> > mCol3Tot = mCol3Tot + 1
>> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
>> > "Ineffective2") > 0 And _
>> > ActiveDocument.FormFields(mI).CheckBox.Value = True
>> > Then
>> > mCol1Tot = mCol1Tot + 1
>> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
>> > "Capable2") > 0 And _
>> > ActiveDocument.FormFields(mI).CheckBox.Value = True
>> > Then
>> > mCol2Tot = mCol2Tot + 1
>> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
>> > "Superior2") > 0 And _
>> > ActiveDocument.FormFields(mI).CheckBox.Value = True Then
>> > mCol3Tot = mCol3Tot + 1
>> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
>> > "Ineffective3") > 0 And _
>> > ActiveDocument.FormFields(mI).CheckBox.Value = True
>> > Then
>> > mCol1Tot = mCol1Tot + 1
>> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
>> > "Capable3") > 0 And _
>> > ActiveDocument.FormFields(mI).CheckBox.Value = True
>> > Then
>> > mCol2Tot = mCol2Tot + 1
>> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
>> > "Superior3") > 0 And _
>> > ActiveDocument.FormFields(mI).CheckBox.Value = True
>> > Then
>> > mCol3Tot = mCol3Tot + 1
>> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
>> > "Ineffective4") > 0 And _
>> > ActiveDocument.FormFields(mI).CheckBox.Value = True
>> > Then
>> > mCol1Tot = mCol1Tot + 1
>> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
>> > "Capable4") > 0 And _
>> > ActiveDocument.FormFields(mI).CheckBox.Value = True
>> > Then
>> > mCol2Tot = mCol2Tot + 1
>> > ElseIf InStr(1, ActiveDocument.FormFields(mI).Name,
>> > "Superior4") > 0 And _
>> > ActiveDocument.FormFields(mI).CheckBox.Value = True
>> > Then
&