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):
Sub ArrayTest() Dim testarray() As Integer Dim i As Integer For i = LBound(testarray) To UBound(testarray) ' <-- error Debug.Print testarray(i) Next i End Sub

Even if you check the return value of LBounds like this, it results in the same error:
Sub ArrayTest() Dim testarray() As Integer Dim i As Integer If LBound(testarray) > 1 Then ' <-- error For i = LBound(testarray) To UBound(testarray) Debug.Print testarray(i) Next i End If End Sub

However, to get rid of the error and to iterate over a possibly empty array, the following code works:
Sub ArrayTest() Dim testarray() As Integer Dim i As Integer On Error Resume Next If LBound(testarray) > 1 Then If Err.Number = 0 Then For i = LBound(testarray) To UBound(testarray) Debug.Print testarray(i) Next i End If End If End Sub

Visual Basic magic 😉

Über 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