Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #4
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •