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.

 

[Tim_Excel_]

That is strange, the image is 1920 by 1040 pixels. That's as big as the average modern monitor.

Anyway, the value in the immediate screen is "1", while hovering my mouse over it is a longer string that was constructed from the second array value (which is correct, given that the line is Debug.Print RArray(1) )

 

[RoryA]

It's 640x347 when I download it.

Anyway, the second array value is collected from cells and that is inside a loop. So the debug value would not necessarily be the same as the cell value(s) once the for...next loop is over.

 

[Tim_Excel_]

Are you sure you're clicking the weblink (https://ibb.co/j7XJkU) and not downloading the image I pasted into the reply? Because that one is indeed 640x347, the link directly opens the image so there isn't any need to download. I have confirmed on two devices.

I had not thought of the loop, but would this matter? I am putting a break before the Next cell line, and I am additionally only testing with a single loop (range of just 1 cell). I've now tried to confirm again by putting 3 breakpoints (on RArray = Array("M"... , Debug.Print... and on m_wb.Sheets...) and all of these breakpoints are being skipped while the code does get executed.

More and more I start to think Excel is just having one of it quirks here. Seems very unusual behaviour.

 

[Fluff]

If you step through the code using F8, do the lines with a breakpoint actually get processed?

 

[RoryA]

Yes, I'm clicking the link. The picture in the web page is too small and downloading from there is the same small image (perhaps because I don't have an account). I can see that in the image code execution is past the Next line and on the line to enable events again.

 

[Tim_Excel_]

If you step through the code using F8, do the lines with a breakpoint actually get processed?

It gets more interesting, I can't step through. Pressing F8 or the button in the debug toolbar give me that error bleep sound, and won't do anything.

I can see that in the image code execution is past the Next line and on the line to enable events again.

That's true, the line where the code breaks is past my actual break point. Wizardry!

 

[Fluff]

Put the word Stop at the top of the code & call it the same way you normally do.
The you can use F8 to step through the code.

 

[Tim_Excel_]

That works perfectly. Stepping through the code gives no issues, the correct values are being displayed. As soon as I remove stop, the code starts skipping break points again and giving wrong values.

 

[RoryA]

I suspect that this is going to be impossible to diagnose without a workbook, as I think there are other factors at play here (it sounds a little like you have recursion going on somehow).

 

[Tim_Excel_]

I will attempt a workaround for this problem. If that fails, I will come back to you with the workbook for you to look into. I don't want to waste anyone's time (including mine) if I can achieve what I want avoiding this method.