How can I make my code run faster?

[Worsty]

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

 

[Jonathan West]

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]

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]

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]

There is a checkbox in each row of the table with the following:
Ineffective (cb) Capable (cb) Superior (cb) N/A (cb)

So what I want to do is if the user leaves the row blank or checks more than
one checkbox when you click the "Calculate" button it would throw a message
like: "You have either made multiple choices or have left a competency blank.
Please review your input and then select calculate again.