Personal website of Gregory K. Maxey, Commander USN (Retired)
Do you have ad-blocking software enabled? While I respect your right to do so, your donations and the minimal advertisements on this site help to defray internet and other costs of providing this content. Please consider excluding this website from blocking or turning off the blocker while browsing this site.
The information, illustrations and code contained in my "Microsoft Word Tips" are provided free and without risk or obligation.
However, the work is mine. If you use it for commercial purposes or benefit from my efforts through income earned or time saved then a donation, however small, will help to ensure the continued availability of this resource.
If you would like to donate, please use the appropriate donate button to access PayPal. Thank you!
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.
The following provides a few examples of what I have chosen to call repeating item lists:
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.
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.
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:
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.
Note: This tips page, illustrations and examples were developed using Word 2003. It is wholly functional with Word 2007 and 2010.
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:
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:
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.
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:
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
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.
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:
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:
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.
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.
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:
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.
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.
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.
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.
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.
If you would like to donate, please use the appropriate donate button to access PayPal. Thank you!