Repeating Item List

The hardest part of preparing this Microsoft Word Tips & Microsoft Word Help page was choosing a title. I'm still not sure that I have it right. However, if you are looking for a VBA technique to input, store, and return a user defined list of similar items with variable properties then hopefully the title I choose has assisted you in your search.

Repeating Item List

The following provides a few examples of what I have chosen to call repeating item lists:

List of dependent children - Name, age, gender
List of "for sale" properties - Area, square footage, school district, taxes, asking price
List of books - Title, Author, Publisher

In each of the examples, the thing is the same (child, property, book) with one or more variable attributes (e.g., name, asking price, publisher).

Repeating item lists are usually presented in the form of a table in a finished document. The challenge for the document designer is not knowing how many things the document user may need to list. For example, if you are preparing an insurance application and you need the applicant to list all dependent children, do you provide a table with two rows or ten? Not enough and the applicant may finish the list anywhere. Too many is a waste space and looks unprofessional.

Methods and Techniques

Using the techniques presented here, you can have a finished document which just the right amount of table rows for the items entered.

In this discussion, I going to present five techniques ranging from basic to advanced. For the sake of brevity, in the first four methods I'm only demonstrating input, storage and a basic output technique. I'll also add progressive enhancements to the code which each subsequent example that can streamline data entry and to handle situations within the code such as users canceling a process.

In the final, most advanced, method I'll provide a full working practical example.

All the methods could employ the output method that I will demonstrate in the last example.

The methods used demonstrate a fairly broad range of VBA techniques including input and msgbox functions, single and multi-dimensional arrays, user defined types (udt), collections, public and private variable classes, basic error handling, and userforms.

So even if you don't need a list as described above, if you read on, you still might learn something new.

Two Dimensional Array

The first method employs a basic multi-dimensional array. Don't let the technical jargon scare you off. Whether you realize it or not, you were probably using a two dimensional array in grammar school. An array is just data structure consisting of a group of elements that are accessed by indexing. Remember the multiplication table? Using it, you found the element "81" by indexing down "9" and across "9."

The VBA for the first method is shown below:

VBA Script:

Copy to clipboard

Sub BuildListWithAnArray()
'Collect multiple attributes (e.g., name, age, gender, etc.) about an unknown _
'number of similar things (e.g. children) using a multi-dimensional array.
Dim lngIndex As Integer
Dim arrData() As String
Dim bRepeat As Boolean
Dim strOutput As String

  lngIndex = 0
  'Dimension the array.  Our "thing," (children) has three attributes (0 - name, 1 - age, 2 - gender)
  'So our first dimension is fixed at 0 - 2 or simply 2.
  ReDim arrData(2, lngIndex)
  'Our second dimension is variable (depending on the final number of children) and will start _
  'at 0 to 0 or simply 0 as represented by the starting value of lngIndex.

  'Initialize variable.
  bRepeat = True
  Do While bRepeat
    'Get and store the name property using an inputbox.
    arrData(0, lngIndex) = InputBox("Enter your child's name.", "Name")
    'Get and store the age property.
    arrData(1, lngIndex) = InputBox("Enter your child's age.", "Age")
    'Get and store the gender property.
    arrData(2, lngIndex) = InputBox("Enter your child's gender.", "Gender")
    
    'Does the user need to enter an additional child?
    If MsgBox("Do you need to list another child??", vbQuestion + vbYesNo, "Add Child") = vbYes Then
      'Uptick the index number.
      lngIndex = lngIndex + 1
      'Redimension the array and preserve the existing elements.
      ReDim Preserve arrData(2, lngIndex)
    Else
      bRepeat = False
    End If
  Loop
  
  'Output the data.  Note:  Array dimensions are 1 based so "arrData, 2" means
  'get the count (the number of children) of the second dimension.
  For lngIndex = LBound(arrData, 2) To UBound(arrData, 2)
    strOutput = strOutput & arrData(0, lngIndex) & ", " & arrData(1, lngIndex) & ", " & arrData(2, lngIndex) & vbCr
  Next
  strOutput = Left(strOutput, Len(strOutput) - 1)
  MsgBox strOutput, vbInformation + vbOKOnly, "List of Children"
lbl_Exit:
  Exit Sub
End Sub

See: Installing Macros for instructions on how to set up and use the macros provided in this Microsoft Word Help & Microsoft Word Tips page.

If you review the above code; or better yet, if install and step through the code in the Visual Basic editor (VBE) one line at a time using the F8 key, you will quickly see the simplicity of the method.

Initialize the variables and dimension the array.
Initiate a loop.
Prompt user to input each attribute and store the input in an array element.
Stay in or exit the loop based on user input.
Output the result.

Note: This tips page, illustrations and examples were developed using Word 2003. It is wholly functional with Word 2007 and 2010.

User Defined Type (UDT)

Most things that we would want to list or describe have descriptive properties or attributes. In the first example, we used the descriptive properties name, age, and gender to define each child.

User Defined Types (UDTs) are a convenient way to store related data or properties in one variable. Using the Type statement in VBA, you can create and define properties for things like children, "for sale" properties, or books.

In a new standard project module, insert the following code:

VBA Script:

Copy to clipboard

Option Explicit
'Declare the type variable
Private Type udtChild
  'Define the type variable attributes
  Name As String
  Age As Long
  Gender As String
End Type

Here we have created a type variable udtChild that has three defined properties: Name, Age and Gender.

In the same standard module, insert the following code after the code shown above:

VBA Script:

Copy to clipboard

Sub BuildLIst_WithUDT_and_Array()

'Creat a udtChild object variable.  Think - Dim oDoc as Document, _
'oBM as Bookmark, oTmp as Template, etc.
Dim oChild As udtChild
Dim lngIndex As Integer
'A single dimension array to store individual oChild objects.
Dim aryUDTs() As udtChild
Dim bFinished As Boolean
  
  lngIndex = 0
  'Dimension the array.
  ReDim aryUDTs(lngIndex)
  With oChild
    bFinished = False
    'In this example we'll use Do ... Loop Until
    Do
      'Get the oChild object property values.
      oChild.Name = InputBox("Enter your child's name.")
      On Error GoTo Err_Input
      'Accepts only the long variable type.
      oChild.Age = InputBox("Enter your child's age.")
      On Error GoTo 0
      oChild.Gender = InputBox("Enter your child's gender.")
      'Add the oChild object to the array.
      aryUDTs(lngIndex) = oChild
      If MsgBox("Do you need to list another child??", vbQuestion + vbYesNo, _
                "Add Child") = vbYes Then
        lngIndex = lngIndex + 1
        ReDim Preserve aryUDTs(lngIndex)
      Else
        bFinished = True
      End If
    Loop Until bFinished
  End With
  'Since our array is a 0 base array and we know the starting value was 0,
  'we can omit the LBound function.
  For lngIndex = 0 To UBound(aryUDTs)
    'Here we are outputting to the VBE immediate window.
    Debug.Print aryUDTs(lngIndex).Name & ", " & aryUDTs(lngIndex).Age _
                & ", " & aryUDTs(lngIndex).Gender
  Next
  Exit Sub
Err_Input:
  MsgBox "You must enter a numerical value.", vbInformation + vbOKOnly, "Invalid Entry"
  Resume
End Sub

If you review or execute and step through the code, you should see that unlike with the multi-dimension array example; by creating and employing a user defined type variable, whole descriptive objects, be they a child object or any other defined object, can be defined and stored as a single entity in a single dimension array.

Simple Class Object and a Collection

I've been dabbling in VBA for over ten years and for most of that time the mere mentioning of a "Class object" filled me with dread. They were the great unknown, and most of my initial attempts to get my head around them and employ them in my projects were met with frustration and failure.

Then I learned about using a simple class object with publically declared properties. Almost immediately, the fog cleared and I was able to begin to comprehend their purpose and usefulness.

The initial setup is a little more complicated, but just follow these simple steps and you'll have a class up and running:

Using the VBE Insert menu, insert a new class object module in your project.
Rename the new "Class1" object "clsChild."
Copy the following code into the clsChild code pane.

VBA Script:

Copy to clipboard

Option Explicit
'The public properties of the class.
Public Name As String
Public Gender As String
Public Age As Long

Public Property Get Description() As String
Dim strTemp As String

  strTemp = Trim(Me.Name) & ", "
  strTemp = strTemp & Trim(Me.Age) &", "
  strTemp = strTemp & Trim(Me.Gender)
  strTemp = Trim(strTemp)
  Description = strTemp
End Property

Using the VBE Insert menu, create a new standard code module in your project.
Copy the following code into the new standard module.

VBA Script:

Copy to clipboard

Sub SimpleClass_With_Collection()
'Create a clsChild object variable.
Dim oChild As clsChild
Dim lngIndex As Integer
Dim oCol As Collection
Dim bRepeat As Boolean

  'Create a new instance of the collection object.
  Set oCol = New Collection
  bRepeat = True
  Do While bRepeat
    Set oChild = New clsChild
    oChild.Name = InputBox("Enter your child's name.")
    On Error GoTo Err_Input
    oChild.Age = InputBox("Enter your child's age.")
    On Error GoTo 0
    oChild.Gender = InputBox("Enter your child's gender.")
    'Add the oChild class object to the collection.
    oCol.Add oChild
    If MsgBox("Do you need to list another child??", vbQuestion + vbYesNo, _
              "Add Child") = vbNo Then
      bRepeat = False
    End If
  Loop
  
  'Output as plain text appended to the existing document text.
  For Each oChild In oCol
   'Output as plain text appended to the existing document text.
   ActiveDocument.Range.InsertAfter vbCr & oChild.Description
  Next
  Exit Sub
Err_Input:
  MsgBox "You must enter a numerical value.", vbInformation + vbOKOnly, "Invalid Entry"
  Resume
End Sub

Notice that by defining a new "description" property of the class, the output process is greatly simplified in the standard code module.

Hopefully this helps illustrate the usefulness of using class objects in your projects. The clsChild module in this project is self contained and serves this standard code module. The same clsChild module can easily be exported and used by any standard code module that needed access to a child object and its properties.

Complex Class Object and a Collection

To be complete, I thought that I should also demonstrate the class object method using a class with its properties defined privately.

Notes: Both a simple and complex class (my terms) may have public properties. The difference is a "simple" class simply uses a public variables as the main holders of data. The "complex" class uses private module level variables as the main holders of data.

I come up short in knowing enough about programming or VBA to explain all the reasons why a complex class is more advantageous than using a simple class. Or why some people insist on using a complex class when a simple class is fully up to a task. Two reasons for using a complex task that I aware of are:

Code incorporated in the LET/GET pairs offer and opportunity to perform additional data validation (using TRIM, etc.) before updating the private variable
The ability to make a property Read-Only or Write-Only

I do know that when using a class with privately defined properties, you must write Property Let and Property Get statement pairs for each property. This is the process that has caused me so much angst and frustration in the past. However, like anything else, with experience it gets easier.

In this example, I've also moved from a child object to a pet object with four properties. Instead of bothering the user with four input boxes each time they need to enter information on a single pet, this method uses one input box to collect all four properties at the same time.

To set up the demonstration, follow these steps:

Using the VBE Insert menu, insert a new class object module in your project.
Rename the new "Class1" object "clsPet."
Copy the following code into the class clsPet code pane.

VBA Script:

Copy to clipboard

Option Explicit
'Declare the private property variables.
Private m_strName As String
Private m_strGender As String
Private m_lngAge As Long
Private m_strSpecies As String

'Create Let/Get pairs for each property.
Property Let Name(strNamePassed As String)
  m_strName = strNamePassed
End Property
Property Get Name() As String
  Name = m_strName
End Property

Property Let Age(lngAgePassed As Long)
  m_lngAge = lngAgePassed
End Property
Property Get Age() As Long
  Age = m_lngAge
End Property

Property Let Gender(strGenderPassed As String)
  m_strGender = strGenderPassed
End Property
Property Get Gender() As String
  Gender = m_strGender
End Property

Property Let Species(strSpeciesPassed As String)
  m_strSpecies = strSpeciesPassed
End Property
Property Get Species() As String
  Species = m_strSpecies
End Property

'Define the object "Description" property.
Property Get Description() As String
Dim strTemp As String
  strTemp = Trim(Me.Name) & ", "
  strTemp = strTemp & Trim(Me.Age) & ", "
  strTemp = strTemp & Trim(Me.Gender) & ", "
  strTemp = strTemp & Trim(Me.Species) & ", "
  strTemp = Trim(strTemp)
  Description = strTemp
End Property

Like clsChild, the clsPet object while significantly more difficult to create is also self contained entity. It can be used in a procedure similar to the one we just used with clsChild or it can be used with any procedure that needs to interact with a pet object and its defined properties.

Using the VBE Insert menu, create a new standard code module in your project.
Copy the following code into the new standard module:

VBA Script:

Copy to clipboard

Sub ComplexClass_With_Collection()
Dim oPet As clsPet
Dim arrData() As String
Dim lngIndex As Integer
Dim oCol As Collection
Dim bRepeat As Boolean
Dim strInput As String

  Set oCol = New Collection
  bRepeat = True
  Do While bRepeat
    Set oPet = New clsPet
    'Rather than presenting a new input box for each property, _
    'the user can enter all of the properties in one input box.
Err_Resume_Input:
    Do
      strInput = InputBox("Enter your pet's name, age, gender," _
                      & " and species separated using a comma.", _
                      "List Pet", "e.g., Fido, 4, female, dog")
      'The StrPtr function only returns 0 if the user cancels an inputbox.
      If StrPtr(strInput) = 0 Then
        'User canceled. Get out!
        GoTo Cancel_Out
      Else
        strInput = Replace(strInput, " ", "")
        arrData() = Split(strInput, ",")
        If UBound(arrData) <> 3 Then
          MsgBox "You did not provide all the required data", _
                 vbInformation + vbOKOnly, "Incomplete Data Entry"
        End If
      End If
    Loop Until UBound(arrData) = 3
    
    'Send (LET) the property values to the oPet object.
    oPet.Name = arrData(0)
    On Error GoTo Err_Input
    oPet.Age = arrData(1)
    On Error GoTo 0
    oPet.Gender = arrData(2)
    oPet.Species = arrData(3)
    
    'Add oPet object to the collection.
    oCol.Add oPet
    If MsgBox("Do you need to list another pet?", vbQuestion + vbYesNo, _
              "Add Pet") = vbNo Then
      bRepeat = False
    End If
  Loop
Cancel_Out:
  'Output the results. Retrieve (Get) the oPet property values.
  For Each oPet In oCol
    ActiveDocument.Range.InsertAfter vbCr & oPet.Description
  Next
  Exit Sub
Err_Input:
  MsgBox "Your pets age must be entered using a numerical value", vbInformation + vbOKOnly, "Invalid Input"
  Resume Err_Resume_Input
End Sub

Functionally, there really isn't much difference in using the class with public or private properties. If you step through the code above, you will notice a lot more interactions going on between the code in the standard module and the class module. This is because all the communication between the two takes place through the LET/GET statements.

You may also have noticed the use of the StrPtr function to detect a user cancel action in the inputbox.

Let's face it, presenting an empty inputbox to the user with complicated instructions how to fill it out could become annoying and it is not very professional.

In the final example, I'll show you how to display and employ a userform to serve as the data input interface. With a userform it is much easier to describe the data input required and validate the user input.

Userform with Collection

A userform is really just a specialized class object, so we might as well take advantage of all the great features associated with them. Presenting a userform to our document user makes it easy for us to layout the data input requirement, makes it easier for the user to enter the correct data, and makes it easier for us to validate the data entered before processing.

Consider the following userform interface:

Ok, not a masterpiece by any standard, but the userform displayed does provide the following enhancements:

Clearly defined text and listbox fields indicating the data requested.
Limited options for gender. Despite the user's personal feelings, the child is either a male or female.
Specific input validation. Code in the form restricts the age text field input to a numerical value.
Incomplete data validation. The user cannot proceed and add a child until all required data is completed.

The following code is employed in the userform. If you are creating the examples yourself as you are reviewing this tips page then after creating the form shown above, copy this code into the form.

VBA Script:

Copy to clipboard

Option Explicit
Private Sub UserForm_Initialize()
  'Set up the initial form display.
  cmdAdd_Done.Enabled = False
  cmdAdd_Cont.Enabled = False
  With Me.lstGender
    .AddItem "Male"
    .AddItem "Female"
  End With
End Sub

Private Sub cmdCancel_Click()
  Me.Tag = "USER CANCEL"
  Me.Hide
End Sub

Private Sub txtAge_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  'Restrict enter to numerical values only.
  If Not (KeyAscii > 47 And KeyAscii < 59) Then
   Beep
   KeyAscii = 0
  End If
End Sub

Private Sub cmdAdd_Cont_Click()
  With Me
    If .lstGender.ListIndex = -1 Then
      .lstGender.SetFocus
      Beep
      Application.StatusBar = "Please click to select gender."
      Exit Sub
    End If
    .Tag = "True"
    .Hide
  End With
End Sub

Private Sub cmdAdd_Done_Click()
  With Me
    If .lstGender.ListIndex = -1 Then
      .lstGender.SetFocus
      Beep
      Application.StatusBar = "Please click to select gender."
      Exit Sub
    End If
    .Tag = "False"
    .Hide
  End With
End Sub

Private Sub txtName_Change()
  Me.cmdAdd_Done.Enabled = AddEnabled
  Me.cmdAdd_Cont.Enabled = AddEnabled
End Sub

Private Sub txtAge_Change()
  Me.cmdAdd_Done.Enabled = AddEnabled
  Me.cmdAdd_Cont.Enabled = AddEnabled
End Sub

Private Sub lstGender_Change()
  Me.cmdAdd_Done.Enabled = AddEnabled
  Me.cmdAdd_Cont.Enabled = AddEnabled
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  'Intercept the form killer "X"
  If CloseMode = vbFormControlMenu Then
    Cancel = 1
    cmdCancel.Value = True
  End If
End Sub

Function AddEnabled() As Boolean
  'Evaluates if all required data requirements are met.
  AddEnabled = False
  If Len(Me.txtName) > 0 And Len(txtAge) > 0 And Me.lstGender.ListIndex <> -1 Then
    AddEnabled = True
  End If
End Function

Notice that the form is not doing anything with the data. It just provides the template for collecting the data and processes to ensure the data input meets the data requirement of our output.

Like in the previous examples, the data output will be processed in a standard code module.

The output will be in the form a table inserted at a targeted location in the document. The table will list each child defined by the user.

Add the following code to a new standard code module in the project.

VBA Script:

Copy to clipboard

Sub UserForm_with_Collection()
Dim oFrmInput As frmChild, oObjForm As Object
Dim bRepeat As Boolean
Dim oCol As Collection
Dim oTbl As Word.Table
Dim lngIndex As Long
  
  'Initialize the collection.
  Set oCol = New Collection
  
  'Set up loop to collect repeating section information from document user.
  bRepeat = True
  Do While bRepeat
    Set oFrmInput = New frmChild
    oFrmInput.Show
    If oFrmInput.Tag <> "USER CANCEL" Then
      'Add the userform objects to the collection.
      oCol.Add oFrmInput
    Else
      Exit Do
    End If
    bRepeat = oFrmInput.Tag
  Loop

  'Creat a document table and output information.
  If oCol.Count > 0 Then
    Set oTbl = ActiveDocument.Tables.Add(ActiveDocument.Bookmarks _
              ("bmChildTable").Range, oCol.Count + 2, 3)
  Else
    Set oTbl = ActiveDocument.Tables.Add(ActiveDocument.Bookmarks _
              ("bmChildTable").Range, 3, 3)
  End If
  With oTbl
    .Style = "Table Grid"
    With .Rows(1)
      .Shading.BackgroundPatternColor = RGB(122, 122, 122)
      .Cells.Merge
      .Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
    End With
    With .Rows(2)
      .Shading.BackgroundPatternColor = RGB(222, 222, 222)
      .Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
    End With
    .Cell(1, 1).Range.Text = "Dependent Children"
    .Cell(2, 1).Range.Text = "Name"
    .Cell(2, 2).Range.Text = "Age"
    .Cell(2, 3).Range.Text = "Gender"
    
    'Add data to table.
    On Error GoTo Err_NoRecords
    For lngIndex = 1 To oCol.Count
      Set oObjForm = oCol.Item(lngIndex)
      .Cell(lngIndex + 2, 1).Range.Text = oObjForm.txtName.Text
      .Cell(lngIndex + 2, 2).Range.Text = oObjForm.txtAge.Text
      .Cell(lngIndex + 2, 3).Range.Text = oObjForm.lstGender.Value
      Set oObjForm = Nothing
    Next lngIndex
  End With
  
  'Report.
  MsgBox "Data you entered has been tranfered to the table." & vbCr + vbCr _
       & "You can edit, add or delete information to the defined table as required", _
          vbInformation + vbOKOnly, "DATA TRANSFER COMPLETE"

  'Redined the bookmark.
  ActiveDocument.Bookmarks.Add "bmChildTable", oTbl.Range

CleanUp:
  Set oTbl = Nothing
  Set oCol = Nothing
  Exit Sub
Err_NoRecords:
  MsgBox "You didn't provide any data on children." & vbCr + vbCr _
       & "You can edit and add information to the basic table if required", _
       vbInformation + vbOKOnly, "NO DATA PROVIDED"
  Resume CleanUp
End Sub

After running the code, the user defined children are listed in the document in a formatted table created at the document target location. The target location is a bookmarked named bmChildTable.

Practical Example

In actual practice, the user should be in full control of the data entry and have the option to edit or remove data before it is processed and added to the document.

The following two userforms are presented as a suggested technique.

The first (or main form) lets the users select from a series of command buttons so they can add, edit or remove items from the repeated list.
The second form presents a simplified version of the data entry form demonstrated earlier.

The main userform. User has full control of adding, editing or removing repeating list items.

repeating_list_items_4

Simplified data entry userform

That's it! I hope you have found this tips page useful and informative.

If you have suggestions for a better title or other suggestions on the methods or code demonstrated then please let me know.

You can download files containing all of the modules, code, forms and examples here:
Repeating Item List Demo Pack.

	Enter your search terms Submit search form
	Web gregmaxey.com

Repeating Item List(A Microsoft Word Help & Tip page by Gregory K. Maxey)