VBA print Variant Array to immediate window

[Tim_Excel_]

I have an Array that I use to populate rows in a Worksheet

If rng1.Offset(0, 4) <> "" Then PValue = "DAYS" Else PValue = "ADHOC"            If rng1.Offset(0, 5) = "PREVENTIVE" Or rng1.Offset(0, 5) = "REACTIVE" Then AGValue = "PART" Else AGValue = "CUSTOMER"
            If rng1.Offset(0, 5) = "PREVENTIVE" Or rng1.Offset(0, 5) = "REACTIVE" Then AHValue = "EU-SERV-ENG-M" Else AHValue = "CUST-MECH"
            
            Dim RArray As Variant
                RArray = Array("M", Cells(cell.Row, 2) & "_" & rng1.Offset(0, 1), "", Cells(cell.Row, 9) & rng1.Offset(0, 2), Cells(cell.Row, 9) & rng1.Offset(0, 2), _
                "", "0", rng1.Offset(0, 3), "1", "?", "?", "0", rng1.Offset(0, 4), "", "NORM", PValue, "0", "0", "0", "", "0", "0", "0", "0", "1", "1", "1", "0", "0", "", "", rng1.Offset(0, 5), AGValue, AHValue, _
                "UNKNOWN", "", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN")

I want to pull the nth value from RArray and print it to the immediate window or use it for other lines of code. However, when I use

Debug.Print RArray(0)

the immediate window doesn't show "M", but whatever value was last mentioned from that array. For example, if I were to do

RArray(0) = "othervalue"
Debug.Print RArray(0)

run this, remove the top line and run it again, the debug window still shows "othervalue" even though this line no longer exists.

 

[RoryA]

Unless you re-run the RArray = Array("M"... line, the array will contain whatever you last put in it.

 

[Tim_Excel_]

As it should, but my Debug.Print line does not behave this way.

In fact, it behaves very oddly. It retained the "othervalue" value even after I closed excel and reopened it. I then cleared the Array before the line that populates it, and then it seemed to give the right value for RArray(0). However, when I use anything other than RArray(0) such as RArray(8), it completely skips the line if I put a break point on the line, but DOES break on the next line where there is no break point. It then still returns the value for (0) in the immediate screen.
Still I could tell RArray(8) was returning the correct value by stopping the code and hovering my mouse over the variable.

Is this Excel acting up or is there something wrong with my code?

Public Sub m_wb_SheetChange(ByVal Sh As Object, ByVal Target As Range)

 Application.EnableEvents = False


'Dynamische referentie
If Not Sh.Name = "Componenten" Then Exit Sub
If Intersect(Target, Worksheets("Componenten").Range("O2:BG500")) Is Nothing Then Exit Sub


For Each cell In Target


    Header = Cells(1, cell.Column).Value
    Dim rng1 As Range
    Set rng1 = ThisWorkbook.Sheets(1).Range("A:A").Find(Header, , xlValues, xlWhole)
    LRow = m_wb.Sheets("AssetTypeTask").Cells(m_wb.Sheets("AssetTypeTask").Rows.Count, "B").End(xlUp).Row + 1
    
    'Als er een getal weg wordt gehaald
    If cell.Value = "" And Not rng1 Is Nothing Then


            Dim FindString As String
            Dim Rng As Range
            FindString = Cells(cell.Row, 2) & "_" & rng1.Offset(0, 1)
            With m_wb.Sheets("AssetTypeTask").Range("B:B")
            Set searchtodelete = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            End With
                 If searchtodelete Is Nothing Then GoTo ncell


        m_wb.Sheets("AssetTypeTask").Rows(searchtodelete.Row).EntireRow.Delete
        
    'Als er een getal bij wordt gezet
    ElseIf Not rng1 Is Nothing Then
    
        'aanpassen van waarde ingevuld met de waarde uit kolom O
        cell.Value = Range("N" & cell.Row).Value
        
        'vullen van alle cellen in rij met correcte data op basis van de kolom header waar een nummer is ingevuld
            If rng1.Offset(0, 4) <> "" Then PValue = "DAYS" Else PValue = "ADHOC"
            If rng1.Offset(0, 5) = "PREVENTIVE" Or rng1.Offset(0, 5) = "REACTIVE" Then AGValue = "MEYN-OOST" Else AGValue = "CUSTOMER"
            If rng1.Offset(0, 5) = "PREVENTIVE" Or rng1.Offset(0, 5) = "REACTIVE" Then AHValue = "EU-SERV-ENG-M" Else AHValue = "CUST-MECH"
            
            Dim RArray As Variant
 
                RArray = Array("M", Cells(cell.Row, 2) & "_" & rng1.Offset(0, 1), "", Cells(cell.Row, 9) & rng1.Offset(0, 2), Cells(cell.Row, 9) & rng1.Offset(0, 2), _
                "", "0", rng1.Offset(0, 3), "1", "?", "?", "0", rng1.Offset(0, 4), "", "NORM", PValue, "0", "0", "0", "", "0", "0", "0", "0", "1", "1", "1", "0", "0", "", "", rng1.Offset(0, 5), AGValue, AHValue, _
                "UNKNOWN", "", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN")
Debug.Print RArray(0)




            
            m_wb.Sheets("AssetTypeTask").Range("A" & LRow & ":AR" & LRow) = RArray
            


    End If
    
ncell:
Next cell


Application.EnableEvents = True
End Sub

 

[RoryA]

Well there is something wrong with the code - namely that you disable events and then have two lines that can exit without re-enabling them.

What exactly does Debug.Print RArray(8) produce?

 

[Tim_Excel_]

you disable events and then have two lines that can exit without re-enabling them.

Thanks for the sharp eye, I have edited the lines.

What exactly does Debug.Print RArray(8) produce?

It produces "M" in the immediate window since that was the value of when it was (0) instead of (8) when I ran it a previous time. However, when I break the code and hover my mouse over it, the correct value is shown but still produces the wrong value in the immediate window.

 

[RoryA]

I can't see how that could be from the code you've posted. I assume you cleared the immediate window between runs?

 

[Tim_Excel_]

I am using the exact same code as what I posted. I think you get my confusion now. I have indeed cleared the window.

The screenshot I've attached shows how the code also breaks a couple of lines under the actual break point, and how the value in the immediate window does not match with the value I get when I hover over it with my mouse.

https://ibb.co/j7XJkU <-- Full sized image

 

[RoryA]

I can't read that image so I don't know what the index into the array is.

 

[Tim_Excel_]

The full sized image is linked in my previous reply.

 

[RoryA]

Yeah, I can't read that either.