Thread: Calculate Average for Dropdown Fields (Word 2000)

1. Calculate Average for Dropdown Fields (Word 2000)

I have 10 dropdown fields that have numbers 1 to 10 listed for a user to choose a rating. I want to "average" the 10 fields after user selects a rating for each of the 10 fields. I found the code below that converts the field value to a number and "sums" the fields. Does anyone know what code I need to add to have the macro "Average" the value of the fields, rather than just sum them? Many thanks!

Sub MainDropdownCalc()
'Assigns DROPDOWN1 text to the variable A\$
A\$ = WordBasic.[GetFormResult\$]("DROPDOWN1")
'Assigns DROPDOWN2 text to the variable B\$
B\$ = WordBasic.[GetFormResult\$]("DROPDOWN2")
'Assigns DROPDOWN2 text to the variable B\$
C\$ = WordBasic.[GetFormResult\$]("DROPDOWN3")
'Converts string into a number
A = Val(A\$) + Val(B\$) + Val(C\$)
' Set the result to the "Text4" FormField and
' converts the value back into a string
WordBasic.[SetFormResult] "TEXT4", Str\$(A)
End Sub

2. Re: Calculate Average for Dropdown Fields (Word 2000)

Never mind...I just figured it out. Thanks anyway!...

Sub MainDropdownCalc()
'Assigns DROPDOWN1 text to the variable A\$
A\$ = WordBasic.[GetFormResult\$]("DROPDOWN1")
'Assigns DROPDOWN2 text to the variable B\$
B\$ = WordBasic.[GetFormResult\$]("DROPDOWN2")
'Converts string into a number

A = Val((A\$) + Val(B\$)) / 2

' Set the result to the "Text4" FormField and
' converts the value back into a string
WordBasic.[SetFormResult] "TEXT4", Str\$(A)
End Sub

3. Re: Calculate Average for Dropdown Fields (Word 2000)

Here is somewhat more contemporary code:

Dim i As Integer
Dim intSum As Integer
Dim sngAvg As Single

For i = 1 To 10
intSum = intSum + ActiveDocument.FormFields("DropDown" & i).DropDown.Value
Next i

sngAvg = intSum / 10
ActiveDocument.FormFields("Text1").Result = sngAvg

WordBasic still works, and it is sometimes unavoidable to use it, but the above is straight Word VBA code.

4. Re: Calculate Average for Dropdown Fields (Word 2000)

Many thanks, Hans....Indeed, your code is better, especially for the latest versions of Microsoft Word. I will keep the code for future use. As always, I appreciate your quick, very helpful response!

5. Re: Calculate Average for Dropdown Fields (Word 2000)

Hi mjairix,

An even simpler way, which requires no vba, is to use a formula field, coded as:
{=Average({Dropdown1},{Dropdown2},{Dropdown3},{Dro pdown4}, {Dropdown5},{Dropdown6}, {Dropdown7},{Dropdown8}, {Dropdown9},{Dropdown10})}
All you need for this to work is for each dropdown field to be set to 'calculate on exit'.

Cheers

6. Re: Calculate Average for Dropdown Fields (Word 2000)

Wow! I wish I had known how to do this years ago! I will save your tip and try it soon. I'm sure it will work, though, and it seems so simple that I can't understand why I could never find this information on how to do the calculation from the many resources I checked. Many thanks!...M.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•