ms-accessvba

"Subscript out of range" error when calling LBound() or UBound() on a VBA array


The following code produces the error "Subscript out of range" and I do not know why. Can someone please explain?

    Dim errorc As Integer
    Dim myarray() As Variant


    errorc = 1

    If Len(Me.txt_Listnum) = 0 Then
    ReDim Preserve myarray(errorc)
    myarray(errorc) = "Numer Listy"
    errorc = errorc + 1
    End If

    If Len(Me.cbo_ByWho) = 0 Then
    ReDim Preserve myarray(errorc)
    myarray(errorc) = "Wystawione przez"
    errorc = errorc + 1
    End If

    If Len(Me.cbo_ForWho) = 0 Then
    ReDim Preserve myarray(errorc)
    myarray(errorc) = "Wystawione na"
    errorc = errorc + 1
    End If

    For i = LBound(myarray) To UBound(myarray)
        msg = msg & myarray(i) & vbNewLine
    Next i

    If errorc > 0 Then
       MsgBox "da" & msg

    End If

Solution

  • Your code will fail if all of the form controls are populated and therefore myarray never gets ReDim'd. For an unititialized dynamic array,

    Dim myarray() As Variant
    

    (i.e., one that has not been subsequently sized with ReDim), calling LBound() or UBound() on it will fail with "Subscript out of range."