Iterate over a possibly empty array in VBA (Excel)

When you try to iterate over an empty array in VBA like this, you will get a runtime error (index out of bounds):

  1. Sub ArrayTest()
  2.  
  3. Dim testarray() As Integer
  4. Dim i As Integer
  5.  
  6. For i = LBound(testarray) To UBound(testarray) ‚ <– error
  7.    Debug.Print testarray(i)
  8. Next i
  9.  
  10. End Sub

Even if you check the return value of LBounds like this, it results in the same error:

  1. Sub ArrayTest()
  2.  
  3. Dim testarray() As Integer
  4. Dim i As Integer
  5.  
  6. If LBound(testarray) > 1 Then  ‚ <– error
  7.    For i = LBound(testarray) To UBound(testarray)
  8.         Debug.Print testarray(i)
  9.     Next i
  10. End If
  11.  
  12. End Sub

However, to get rid of the error and to iterate over a possibly empty array, the following code works:

  1. Sub ArrayTest()
  2.  
  3. Dim testarray() As Integer
  4. Dim i As Integer
  5.  
  6. On Error Resume Next
  7. If LBound(testarray) > 1 Then
  8.     If Err.Number = 0 Then
  9.         For i = LBound(testarray) To UBound(testarray)
  10.             Debug.Print testarray(i)
  11.         Next i
  12.     End If
  13. End If
  14.  
  15. End Sub

Visual Basic magic 😉

Über uns Stefan

Polyglot Clean Code Developer

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax