Calculate Average for Dropdown Fields (Word 2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

Hi mjairix,

An even simpler way, which requires no vba, is to use a formula field, coded as:
{=Average({Dropdown1},{Dropdown2},{Dropdown3},{Dropdown4}, {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

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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.

Reply | Quote