Debug.Print won't print anything in VBA Immediate Window-CodePudding

This code creates an array off a range. However when I try to see if the array is actually saving the elements, by using Debug.Print, nothing is shown in the Immediate Window, but it displays just blank spaces. There are no errors, just doesn't display anything.

However this only happens in that part of the code, the first Debug.Print line works fine, it's only the second Debug.Print that doesn't work. I do have data in the columns.

Dim myArray() As Variant 
Dim iCountLI As Long 
Dim iElementLI As Long 

If IsEmpty(Range("B3").Value) = True Then
      ReDim myArray(0, 0)
Else
      iCountLI = Sheets("Sheet1").Range("B3").End(xlDown).Row
      iCountLI = (Range("B3").End(xlDown).Row) - 2 
      Debug.Print iCountLI
      ReDim myArray(iCountLI) 

      For iElementLI = 1 To iCountLI 
      myArray(iElementLI - 1) = Cells(iElementLI   2, 2).Value 
      Debug.Print myArray(iElementLI)
      Next iElementLI
      
End If

Any help would be appreciated, thank you in advance!

CodePudding user response：

You are valuing myArray(iElementLI -1) and printing myArray(iElementLI), which is still empty.

CodePudding user response：

Immediate problem: ReDim myArray(iCountLI) creates an array with empty values. In the For Loop, myArray(iElementLI - 1) = Cells(iElementLI 2, 2).Value overwrites the first, second, etc. values, yet your Debug.Print myArray(iElementLI) is printing the second, third, etc. values. These, of course, haven't yet been overwritten, so they are still empty. The easiest fix, then, is to use Debug.Print myArray(iElementLI - 1).

More generally, I think you might be misunderstanding the meaning of ReDim myArray(iCountLI). Let's assume we have values in B3:B7. This would lead to ReDim myArray(5) in your code, but this is an array with 6 empty values, at location 0,1,2,3,4,5. This means you will keep an empty value trailing in the array after your loop, which is probably not what you want.

Here's a suggested rewrite with some comments:

Sub FillArray()

Dim myArray() As Variant
Dim iCountLI As Long
Dim iElementLI As Long

'let's assume: B3:B7 with values: 1,2,3,4,5

If IsEmpty(Range("B3").Value) = True Then
    ReDim myArray(0, 0)
Else
    'iCountLI = Sheets("Sheet1").Range("B3").End(xlDown).Row
    'this line serves no purpose: you are immediately reassigning the value in the next line
    
    'iCountLI = (Range("B3").End(xlDown).Row) - 2   'This would be 5,
                                                    'but myArray(5) would have SIX elements
    iCountLI = (Range("B3").End(xlDown).Row) - 3
    
    Debug.Print iCountLI '4
    
    ReDim myArray(iCountLI)
    
    'For iElementLI = 1 To iCountLI
    For iElementLI = 0 To iCountLI '0 to 4
    
    myArray(iElementLI) = Cells(iElementLI   3, 2).Value    'starting at 0, so " 3", not " 2"
    'myArray(iElementLI - 1) = Cells(iElementLI   2, 2).Value
    
    Debug.Print myArray(iElementLI) 'in succession: 1,2,3,4,5
    Next iElementLI
      
End If

End Sub

Finally, it is worth pointing out that you don't actually need a For Loop to populate an array with values from a range. You could use something as follows:

Sub FillArrayAlt()

Dim myArray() As Variant
Dim iCountLI As Long
Dim iElementLI As Long

Dim myRange As Range

'let's assume: B3:B7 with values: 1,2,3,4,5

If IsEmpty(Range("B3").Value) = True Then
    ReDim myArray(0, 0)
Else
    
    Set myRange = Range("B3:" & Range("B3").End(xlDown).Address)
    myArray = Application.Transpose(myRange.Value)
    
    'N.B. Confusingly, used in this way, your array WILL start at 1!
    
    For i = LBound(myArray) To UBound(myArray)
        Debug.Print i; ":"; myArray(i)
        
'        1 : 1
'        2 : 2
'        3 : 3
'        4 : 4
'        5 : 5
        
    Next i
      
End If

End Sub