VBA Excel : Odd subscript out of range error (9)

Question

I'm encountering an odd VBA subscript out of range error in this simple sub :

Sub writeTypes(ByVal rowNb As Long, ByVal colNb, ws As Worksheet)

    On Error GoTo ErrorHandler_ObjMethod

    Const METHOD_NAME = "writeTypes (CCase)"

    With ws

        If Not isArrayEmpty(pTypes) Then

            For i = LBound(pTypes) To UBound(pTypes)

                If pTypes(i) <> "" Then

                    .Cells(rowNb, colNb).Value = .Cells(rowNb, colNb).Value & pTypes(i) & ";"

                ElseIf i = UBound(pTypes) Then

                    .Cells(rowNb, colNb).Value = Left(.Cells(rowNb, colNb).Value, Len(.Cells(rowNb, colNb).Value) - 1)

                End If

            Next i

        Else: .Cells(rowNb, colNb).Value = "N/A"

        End If

    End With

ErrorHandler_ObjMethod:
    If err.Number <> 0 Then
        Workbooks(AA_RESOURCES_WB).Close SaveChanges:=True
        MsgBox (METHOD_NAME & vbNewLine & err.Number & vbNewLine & err.description)
        End
    End If

End Sub

The calling line of this procedure is : pUnassignedCases(i).writeTypes j, 7, ws (The variables passed as params are correct I made sure of that several times)

Here is what I already tried to do :

-To remove the "ByVal"s in the parameters

-To remove the first "If Not"

-To remove the "Elseif" block

The removals were done properly without any syntax/logic errors.

I also checked any used variables (including the array of string that is "pTypes") in any way possible. All things seem perfectly fine.

I also tried to incorporate this code directly into my other sub (that goes through an array of CCase objects with a For loop) rather than calling it through a CCase object procedure, and somehow it works for the firsts CCases objects and then forces the loop to go beyond the CCase array upper bound... I can't make any sense of this.

When I go through the code line by line, the error occurs at the "End sub" line. But when I remove the error handling it goes fine but somehow the error gets passed on somewhere else in the code that isn't in anyway related to this sub and was working perfectly before... Then if I simply remove any calling of this sub everything is working as it already was.

Plus even when the error occurs, my worksheet line is still well updated with "N/A" (as it's supposed too because none of my cases objects have types for now). It's like the sub is cursed. This is driving me crazy.

Answer 1

A few things I noticed: I don't know if these will fix the issue you are experiencing but they may be of use:

1. Parameter ByVal colNb has no type.

2. I suspect the line ElseIf i = UBound(pTypes) Then is intended to strip off the trailing ";" If this is the case, similar code, might be best placed outside the for loop. Consider: Can the array pTypes have a value that is <> "" in the UBound index position. If so you may have a logic error.

3. If the cell has no value and Len(.Cells(rowNb, colNb).Value) - 1) is used it will raise an error when used as an argument to the left function. I think this will occur when the array contains only empty strings.

4. isArrayEmpty: I'm wondering what this function does. I assume it does what it says on the tin.

The above don't explain your issue, "err" being lower case is weird as stated in another answer. If Exit sub is before the error handler it would stop the if statement being evaluated when no error has occurred.

Harvey

Code demonstrating poor error handling.

Sub a3(optional RaiseAnError As Boolean = true)

On Error GoTo errhand

If RaiseAnError Then
    Err.Raise 1, "", "Simulating code that might raise and error "
End If

MsgBox "Got to the end of your code"

errhand:
If Err.Number <> 0 Then
    MsgBox "Err.Number = " & Err.Number
    Err.Raise 2, "", "Simulating code ie workbook close that might raise and error "
    MsgBox "ok"
    End
End If
End Sub