0


I am programming a kind of parser which reads an Excel table and then creates a List of processes with some properties like Name, StartTime, EndTime etc. For this I have a class Process and in the main file, I have a processList (Scripting.Dictionary), where I put the processes as I read the lines... For this assignment, the key is a String called MSID.

Now the problem is that for some reason, I am only able to access the Object from the Dictionary and alter its parameters inside one part of my If-ElseIf statement. In the other case, it throws 424-object required error and I have no idea why.

Here is the code

Sub ParseMessages()
' ITERATOR VARIABLES
Dim wb As Workbook, ws As Worksheet
Dim rowIter As Long, row As Variant
Dim A As Variant, B As Variant, C As Variant, D As Variant, E As Variant, F As Variant  ' A,B,C,D,E,F variables for the cells of each row

' PROCESS PARAMETERS
Dim MSID As Variant
Dim StartTime As Variant
Dim EndTime As Variant

' OBJECTS
Dim process As process
Dim processList As Scripting.Dictionary     ' DICTIONARY where the error happens
Set processList = New Scripting.Dictionary

Worksheets(1).Activate

'####### MAIN LOOP ######################################################
For rowIter = 1 To 11
    row = Rows(rowIter)
    A = row(1, 1)
    B = row(1, 2)
    C = row(1, 3)
    D = row(1, 4)
    E = row(1, 5)
    F = row(1, 6)
    Dim startIndex As Long, endIndex As Long, count As Long

    ' ------ PROCESSSTART -> MSID, processName, startTime
    If (.....) Then
        Debug.Print (vbNewLine & "Process start")
        If (...) Then   ' --MSID
            startIndex = InStr(F, "Nr") + 3             '3 to skip "Nr "
            endIndex = InStr(startIndex, F, "(")
            count = endIndex - startIndex
            MSID = Mid(F, startIndex, count)
            StartTime = B
            Debug.Print (StartTime & " -> " & MSID)

            ' **** MAKE new Process object, add to collection
            Set process = New process
            process.StartTime = StartTime
            process.MSID = MSID
            processList.Add MSID, process    ' Add to the dictionary, KEY, VALUE

        ElseIf (...) Then      ' --ProcessName
            startIndex = InStr(F, "=") + 2
            endIndex = InStr(F, "<") - 1
            count = endIndex - startIndex
            processName = Mid(F, startIndex, count)
            Debug.Print (processName)
            ' **** Add Name to the last element of the dictionary
            processList(processList.Keys(processList.count - 1)).Name = processName 'get last Process Object
            processList(MSID).Name = "Just Testing" ' !!!! here it works
        Else
        End If

    ' ------ END OF PROCESS ->
    ElseIf (......) Then
        startIndex = InStr(D, "MSID") + 5
        endIndex = InStr(startIndex, D, "]")
        count = endIndex - startIndex
        MSID = Mid(D, startIndex, count)

        EndTime = B
        Debug.Print (EndTime & " End of process " & MSID)

        ' **** Add End time for the process from the collection, specified by MSID
        Debug.Print ("Test of " & processList(MSID).Name)  ' !!!!! Doesn't work
        processList(MSID).Name = "Just Prooooooving"       ' !!!!! Here doesn't work
        processList(MSID).EndTime = EndTime                ' !!!!! Does not work
    End If
Next
End Sub

So to specify the question - why is it that this works:

processList(MSID).Name = "Just Testing" ' !!!! here it works

And this doesn't:

processList(MSID).Name = "Just Prooooooving"       ' !!!!! Here doesn't work

If I first prove if the Object with the MSID key exists in the dictionary, it's not found. 

If processList.Exists(MSID) Then
            Debug.Print ("Process exists, altering it...")
            processList(MSID).Name = "Just Prooooooving"   ' !!!!! Here doesn't work
            processList(MSID).EndTime = EndTime
End If

But on the very first line where the condition is evaluated, I get something different by debug. It's THERE! See picture below

Debugging - MSID there but not able to access Dictionary entry with this as a key

Can you suggest how to solve this issue?

Thank you very much in advance for your help!

Rionick
  • 11
  • 2
  • 1
    Best guess would be that you never added that MSID, since it's in the other part of the (unspecified) `If` clause. – Rory Nov 07 '18 at 13:15
  • Wrap the access in `If processList.Exists(MSID) Then`. You're assuming that you get a result from `processList(MSID)`. – Comintern Nov 07 '18 at 13:26
  • I updated the question. This if statement evaluates as false (not found in the dictionary). However, when I debug, I see it there with the exact same MSID. It just absolutely doesn't make sense to me... Even if I access it with `If processList.Exists("124") Then ` it doesn't work – Rionick Nov 07 '18 at 13:55

1 Answers1

0

So... It's a bit shameful but after some hours of trying to solve this problem I found out, that I added the Object to the list with MSID="124 " as Key.

When I tried to access, I of course used MSID with value "124". Notice the difference? Yes, that space at the end.

The tricky part is - VBA debugger trims spaces at the end of Strings, so it's actually impossible to see it. The same situation is if you print this out - impossible to see...

So in the end, I spent many hours looking for the answer, which is so simple :/ All I can do is to laugh about this.

Rionick
  • 11
  • 2