VBA - Excel Class Function - Logic

Question

Using VBA to sort through employee course records. I have an Employee class and am trying to identify people individuals who satisfy the condition "has this but not that." I have stepped through the code and all works just fine, except for my condition testing in BlsHasIssue(). Pertinent code:

'''''''''''''
'clsEmployee'
'''''''''''''
Option Explicit

Private empID As String
Private name As String
Private hasBLSComp As Boolean
Private hasBLSAssignment As Boolean

Public Property Let id(value As String)
    empID = value
End Property

Property Get id() As String
    id = empID
End Property

Public Property Let setName(value As String)
    name = value
End Property

Public Property Get getName() As String
    getName = name
End Property

Public Sub addResusRecord(value As String)  'accepts name of course
    Select Case value
        Case "BLS Assignment":
            hasBLSAssignment = True
        Case "BLS Competency":
            hasBLSComp = True
    End Select
End Sub

Public Function BlsHasIssue() As Boolean
    If hasBLSComp And Not hasBLSAssignment Then
        BlsHasIssue = True
    Else
        BlsHasIssue = False
    End If
End Function

''''''''
'Module'
''''''''
Option Explicit

Const STUDENT_NAME_COL As Integer = 3
Const USER_ID_COL As Integer = 4
Const COURSE_NAME_COL As Integer = 5

Public Function Contains(col As Collection, key As Variant) As Boolean
    Dim obj As Variant
    On Error GoTo err
    Contains = True
    IsObject (col(key))
    Exit Function
err:
    Contains = False
End Function

    Sub ResusCardAudit()

    Dim row As Long
    Dim currEmpID As String
    Dim employees As New Collection
    Dim emp As Object

    For row = 2 To 5 'change to be last row of data
        'Check to see if user exists in collection
        currEmpID = Cells(row, USER_ID_COL).value

        If Not Contains(employees, currEmpID) Then
            'create User and add it to collection
            Set emp = New clsEmployee
            emp.id = currEmpID
            emp.setName = Cells(row, STUDENT_NAME_COL)
            emp.addResusRecord Cells(row, COURSE_NAME_COL)
            employees.Add emp, currEmpID
        Else
            employees.Item(currEmpID).addResusRecord Cells(row, COURSE_NAME_COL)
        End If

    Next row

    'cycle through all employees and determine if any have a comp but not an assignment
    Set emp = New clsEmployee
    row = 2
    For Each emp In employees
        If emp.BlsHasIssue Then

            row = row + 1
        End If
    Next emp

    'if they do, add their details to a new sheet named "Results"


End Sub

I have left out the code which properly sets hasBLSComp and hasBLSAssignment. The issue seems to be a logic error when testing emp.BlsHasIssue. The test which has brought me here is when hasBLSComp is True and hasBLSAssignment is False -> BlsHasIssue() results in False.

Note: It is acceptable for hasBLSAssignment to be True and hasBLSComp to be False.

Thoughts?

Are you sure the error is in your BlsHasIssue function? Your ResusCardAudit subroutine isn't doing anything at the moment (iterating through an empty collection is a bit pointless) so perhaps you only think your function isn't working because it has never been called. If you do have something in that collection, it might be an idea to show us how it is being set up. (Not necessarily all of the code, but enough that we can replicate the issue.) — YowE3K
The logic you show here is good - it worked for me when I tried it. So the issue must be someplace else. — Brian M Stafford
Unrelated, but BlsHasIssue could be implemented as a simple BlsHasissue = hasBLSComp And Not hasBLSAssignment one-liner. — Mathieu Guindon
@SJR I was wondering the same thing - I ended up coding Function Contains(c As Collection, k As String) As Boolean On Error Resume Next Contains = Not c(k) Is Nothing On Error GoTo 0 End Function which seemed to do what I assume it is trying to do. — YowE3K

JG7 JG7 · Accepted Answer · 2017-11-17T12:48:31

Thanks for the all of the help. It was an error in my test case--I set the test as the inverse of what I thought I had. This has helped me realize the importance of (valid) multiple test cases before determining something is a problem.

Based on the responses to my question: I will stop hiding the simple logic that tests for an issue in the records and bring it to the module level. I believe this will help me avoid issues moving forward as the code expands.

VBA - Excel Class Function - Logic

1 Answers