So, now let's take a look at the RecruitStats() sub and what do you know, it calls another sub to actually populate the lists.

Sub RecruitStats()
  Dim collection As NotesDocumentCollection
  Dim child As NotesDocument
  Dim intern As Boolean
  
  Set collection = recruit.Responses
  Set child = collection.GetFirstDocument
  intern = False
  
  Set child = collection.GetFirstDocument
  While Not(child Is Nothing)
    If child.form(0) = "Education" Then
      Call AddtoLists(recruit.offer_status(0),child.school(0))
      Call AddtoLists(recruit.offer_status(0),child.degree_type(0))
      Call AddtoLists(recruit.offer_status(0),child.degree(0))
      If child.degree_type(0) = "Engineering" Then Call AddtoLists(recruit.offer_status(0),child.engineering_type(0))
    End If
    
    If child.form(0) = "Internship" Then intern = True
    
    Set child = collection.GetNextDocument(child)
  Wend
  
  Call AddtoLists(recruit.offer_status(0),"Total")
  Call AddtoLists(recruit.offer_status(0),recruit.company(0))
  Call AddtoLists(recruit.offer_status(0),recruit.region(0))
  Call AddtoLists(recruit.offer_status(0),recruit.gender(0))
  Call AddtoLists(recruit.offer_status(0),recruit.ethnicity(0))
  Call AddtoLists(recruit.offer_status(0),recruit.contact_type(0))
  Call AddtoLists(recruit.offer_status(0),recruit.candidate_type(0))
  If recruit.International(0) = "Yes" Then Call AddtoLists(recruit.offer_status(0),"International")
  If intern = True Then Call AddtoLists(recruit.offer_status(0),"Intern")
End Sub

Again, because I didn't want to write more than I had to, I decided to put the actual population of this lists in a different sub called AddtoLists(). This sub is passed 2 variables, the current status of the recruit and the List identifier to use. This separate sub allowed me to consolidate all of my logic in one place and not have to copy and paste it all over the place. See, I told you I was lazy. So let's take a look at the AddtoLists() sub:

Sub AddtoLists(status As String, listname As String)
  Dim addint As Boolean
  
  If status = "Rejected Offer" Then
    addint = True
    Forall x In recruit.GetItemValue("reason")
      If x = "Prior to Interview" Then addint = False
    End Forall
    If addint = True Then
      If Iselement(statint(listname)) Then statint(listname) = statint(listname) + 1 Else statint(listname) = 1
    End If
  Else
    If Iselement(statint(listname)) Then statint(listname) = statint(listname) + 1 Else statint(listname) = 1
  End If
  
  Select Case status
  Case "Passed" :
    If Iselement(statpass(listname)) Then statpass(listname) = statpass(listname) + 1 Else statpass(listname) = 1
  Case "Offer Made" :
    If Iselement(statoffer(listname)) Then statoffer(listname) = statoffer(listname) + 1 Else statoffer(listname) = 1
  Case "Accepted Offer" :
    If Iselement(statoffer(listname)) Then statoffer(listname) = statoffer(listname) + 1 Else statoffer(listname) = 1
    If Iselement(stataccept(listname)) Then stataccept(listname) = stataccept(listname) + 1 Else stataccept(listname) = 1
  Case "Rejected Offer" :
    If addint = True Then
      If Iselement(statoffer(listname)) Then statoffer(listname) = statoffer(listname) + 1 Else statoffer(listname) = 1
    End If
    If Iselement(statreject(listname)) Then statreject(listname) = statreject(listname) + 1 Else statreject(listname) = 1
  End Select
End Sub

The first thing this code does every time we want to add sometime to the list is to check if the identifier in question already exists in the list. This is accomplished by the calling the Iselement function. If it doesn't exist, then we set the List(identifier) equal to 1, otherwise add 1 to the value stored in List(identifier). IMHO, this is so much better than looping through arrays to find where a given identifier exists.

So basically, I gather a list of recruits and, for each recruit, add 1 to each statistical category that it corresponds to. Once this is done, it's time to put the statistics onto the finished Excel spreadsheet.

Link to Part IV