Making a VBA Automated Survey in Word

Recently I stumbled upon an issue of creating a quick survey for clients/users. Usually you would want surveys set up in a dedicated web application like Google Forms or Survey Monkey. However, in this case I was limited to MS Word – the doc / docx survey was to be sent via email to clients/users and consolidated. Seemed like a lot of manual work. But why bother when you can use VBA? I created the survey using MS Word ContentControls (text fields, checkboxes etc.) and ActiveX objects (for option / radiobuttons only). Collecting the results was quite easy using VBA. The code is reusable for virtually any survey created this way. Below I explain my approach of making a survey in Word and how to easily consolidate the results using VBA macros (next page of this post).

Designing the survey

Most people don’t put enough effort into designing their surveys – sloppy designs, messed up document structures or simply frequent typo’s and misspelled questions. All these will heavily deteriorate the quality of your survey. Skip this section if you want just the solution/code. If you are still developing your survey, however, I encourage you to at least review the steps for designing a good survey.

Survey in Word: Well structured question survey in Word question

When designing your survey always start with the following:

Draft your targets/objectives – what do you want to achieve with your survey? What information do you want to collect? Make a list of your objectives
Develop you questions and map them to your list of objectives for completeness. Remove questions that do not map to any objectives. Check if there are any duplicates – questions that provide results for the same objectives in a similar way. Are there any objectives not covered by a single question?
Review your list of questions – limit the survey to a reasonable amount of questions. Make sure your survey is balanced – not too many questions for a single objective
Decide each type of question – should they be open questions (text) and which ones should have a limited set of answers (multiple or single choice)
Draft answers to closed questions
Review all answers for completeness. Standardize questions with scales. Make sure to organize the answers and sort them appropriately
Make sure the survey looks clean and structured
Spelling and grammar – make sure there are no no typos/misspelled words etc.

Insert Word form controls

When you have completed drafting your survey and structuring it you can move on to adding the actual form controls to make you survey interactive and enable automatic results extraction.

Here is a list of different mapping types of question to the various controls you may use:

Type of question	Form control to use	Instructions
Open questions	Rich Text Content Control Plain Text Content Control	Set the TAG and TITLE to be able to extract answers automatically Use the Rich control only if you want the user to be able to add formatting. I would, however, advise going with the Plain control.
Open questions – Dates	Date Picker Content Control	Set the TAG and TITLE to be able to extract answers automatically
Singlechoice closed questions	Option Button (ActiveX Controls) / Radiobuttons Combo Box Content Control / ListBox Dropdown List Content Control / DropDown	Set the TAG and TITLE to be able to extract answers automatically Use the Option Buttons when you have a small set of answers e.g. max 5. Use the Combo Box or Dropdown controls for questions will a large set of questions When using Option Buttons (Radiobuttons) be sure to set the same GroupNameto all Option Buttons for all answers within a single question
Multichoice close question	Check Box Content Control	Set the TAG and TITLE to be able to extract answers automatically

Below a short example of how to correctly configure a Option Button (Radiobutton). Be sure to name the control appropriately and assign the same GroupName to all answers of a single set.

Option Button (ActiveX Control): Configuring

Be sure to test the survey and double-check for any typos and other editing errors twice before dispatching it to your respondents.

Automatically consolidating the results

Now that you have your survey ready it’s time for consolidating the results. If you used the form controls mentioned above you can quickly consolidate your results using the following VBA macro.

Sub ExtractResults()
    Dim folder As String, currFile As String, col As Long, _
        ws As Worksheet, row As Long, isHeader As Boolean, currDoc As Object, wApp As Object
    Set wApp = CreateObject("Word.Application")
    On Error GoTo Finally
    'Get folder with survey results
    folder = BrowseForFolder(ActiveWorkbook.Path)
    Set ws = ActiveSheet

    currFile = Dir(folder & "/")
    'Loop through survey results
    row = 2: col = 1: isHeader = False
    Do While currFile <> ""
        Set currDoc = wApp.Documents.Open(folder & "/" & currFile, ReadOnly:=True)
        LoadFile ws, currDoc, isHeader, col, row
        currDoc.Close SaveChanges:=False
        isHeader = True
        row = row + 1
        col = 1
        currFile = Dir()
    Loop
Finally:
    wApp.Quit
End Sub
Sub LoadFile(ws, currDoc As Object, isHeader As Boolean, col As Long, row As Long)
    Dim contCtrl
    'Section for content controls
    For Each contCtrl In currDoc.ContentControls
        If Not isHeader Then ws.Cells(1, col).Value = contCtrl.Title
        'Text content controls
        If contCtrl.Type = 0 Or contCtrl.Type = 1 Then ws.Cells(row, col).Value = contCtrl.Range.Text
        'Checkbox
        If contCtrl.Type = 8 Then ws.Cells(row, col).Value = contCtrl.Checked
        'Dropdown or Listbox
        If contCtrl.Type = 3 Or contCtrl.Type = 4 Then ws.Cells(row, col).Value = contCtrl.Range.Text
        'Date
        If contCtrl.Type = 6 Then ws.Cells(row, col).Value = contCtrl.Range.Text
        col = col + 1
    Next contCtrl

    'Section for ActiveX radiobuttons (add more if needed)
    For Each fItem In currDoc.Fields
        If fItem.OLEFormat.ClassType = "Forms.OptionButton.1" Then
            If Not isHeader Then ws.Cells(1, col).Value = fItem.OLEFormat.Object.Name
            ws.Cells(row, col).Value = fItem.OLEFormat.Object.Value
        End If
        col = col + 1
    Next fItem
End Sub
Function BrowseForFolder(Optional OpenAt As Variant) As Variant
    Dim ShellApp As Object
    Set ShellApp = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
    On Error Resume Next
    BrowseForFolder = ShellApp.self.Path
    On Error GoTo 0
    Set ShellApp = Nothing
    Select Case Mid(BrowseForFolder, 2, 1)
    Case Is = ":"
        If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
    Case Is = ""
        If Not Left(BrowseForFolder, 1) = "" Then GoTo Invalid
    Case Else
        GoTo Invalid
    End Select

    Exit Function
Invalid:
    BrowseForFolder = False
End Function

How to consolidate results

Make sure that all survey results are in a single folder and the consolidation Excel file is in a parent folder
Execute the ExtractResults VBA macro or click the Consolidate Survey Results in the Excel xlsm file above
Select the folder with the survey results
All results will be uploaded to the Excel file

Download example

Want an example of the above survey? Simply download the example below:

Conclusions for making a survey in Word

That’s it! Hopefully making a survey in Word will be much easier for you! I would especially like to stress on the design phase of your survey. As usually you will only get 1 hit for the survey, it is crucial to focus on you targets/objectives when drafting your questions.