Please Help Run-time error ‘9’

TBC · Feb 16, 2011

I just started getting this error, can anyone help me debug it?

Code:

 Public Function getSTDev(ParamArray varVals() As Variant) As Variant[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]  Dim varVal As Variant[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  Dim intcount As Integer[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  Dim Arr() As Variant[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  For Each varVal In varVals[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    If IsNumeric(varVal) Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      Arr = AddElement(Arr, varVal)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    End If[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  Next varVal[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  getSTDev = StdDev(Arr)[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]End Function[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Function StdDev(Arr() As Variant) As Variant[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]     Dim i As Integer[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     Dim avg As Single, SumSq As Single[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     Dim k As Integer[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     If Not HasDimension(Arr) Then Exit Function[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     If Not UBound(Arr) = LBound(Arr) Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]       avg = Mean(Arr)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]       For i = LBound(Arr) To UBound(Arr)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          SumSq = SumSq + (Arr(i) - avg) ^ 2[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          k = k + 1[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        Next i[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        If k > 1 Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          StdDev = Sqr(SumSq / (k - 1))[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        End If[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     End If[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]End Function[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Public Function AddElement(ByVal vArray As Variant, ByVal vElem As Variant) As Variant[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]      ' This function adds an element to a Variant array[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      ' and returns an array with the element added to it.[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      Dim vRet As Variant ' To be returned[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      If IsEmpty(vArray) Or Not IsDimensioned(vArray) Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          ' First time through, create an array of size 1.[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          vRet = Array(vElem)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      Else[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          vRet = vArray[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          ' From then on, ReDim Preserve will work.[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          ReDim Preserve vRet(UBound(vArray) + 1)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]          vRet(UBound(vRet)) = vElem[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      End If[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      AddElement = vRet[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  End Function[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]Public Function IsDimensioned(ByRef TheArray) As Boolean[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]      If IsArray(TheArray) Then ' we need to test it! otherwise will return false if not an array![/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                      ' If you put extensive use to this function then you might modify[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                      ' it a lil' bit so it "takes in" specific array type & you can skip IsArray[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                      ' (currently you can pass any variable).[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        On Error Resume Next[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]            [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]            IsDimensioned = ((UBound(TheArray) - LBound(TheArray)) >= 0)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        On Error GoTo 0[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Else[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        'IsDimensioned = False ' is already false by default[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        Call Err.Raise(5, "IsDimensioned", "Invalid procedure call or argument. Argument is not an array!")[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    End If[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]End Function[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Public Function HasDimension(ByRef TheArray, Optional ByRef Dimension As Long = 1) As Boolean[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    Dim isDim As Boolean[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim ErrNumb As Long[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim LB As Long[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim errDesc As String[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    'HasDimension = False[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]  [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    If (Dimension > 60) Or (Dimension < 1) Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        Call Err.Raise(9, "HasDimension", "Subscript out of range. ""Dimension"" parameter is not in its legal borders (1 to 60)! Passed dimension value is: " & Dimension)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        Exit Function[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    End If[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]  [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    On Error Resume Next[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        isDim = IsDimensioned(TheArray) 'IsArray & IsDimensioned in one call. If Err 5 will be generated if not Array[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        ErrNumb = Err.Number[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        If ErrNumb <> 0 Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            errDesc = Err.Description[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        End If[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    On Error GoTo 0[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]  [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    Select Case ErrNumb[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        Case 0[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            If isDim Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                On Error Resume Next[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                    LB = LBound(TheArray, Dimension) 'just try to retrive Lbound[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                    HasDimension = (Err.Number = 0)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                On Error GoTo 0[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            End If[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        Case 5[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            Call Err.Raise(5, "HasDimension", "Invalid procedure call or argument. Argument is not an array!")[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        Case Else[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            Call Err.Raise(vbObjectError + 1, "HasDimension", _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                "This is unexpected error, caused when calling ""IsDimensioned"" function!" & vbCrLf & _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                "Original error: " & ErrNumb & vbCrLf & _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                "Description:" & errDesc)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    End Select[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]End Function[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Function Mean(Arr() As Variant)[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]     Dim Sum As Single[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     Dim i As Integer[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     Dim k As Integer[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     Sum = 0[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     For i = LBound(Arr) To UBound(Arr)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         k = k + 1[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         Sum = Sum + Arr(i)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     Next i[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     Mean = Sum / k[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     'MsgBox Mean[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]End Function[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]  [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Public Sub testSDEV()[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]  MsgBox getSTDev(Null, 3, 2, Null)[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]End Sub

MarkK · Feb 16, 2011

Your 'subscript out of range' error happens at what line? You posted six sub-routines. You need to narrow down where the error occurs, and commonly the error description is more useful than the number.
Cheers,

TBC · Feb 16, 2011

Thansk lagboltThe error message Im getting is
Run-time error '9'
Subscript out of rang

Then when I hit the Debug button I get this row highlighted

IsDimensioned = ((UBound(TheArray) - LBound(TheArray)) >= 0)

Let me know if I can provide any further help to help me

MarkK · Feb 16, 2011

An error should not be raised here since there is an active error handler. What I would check is your error trapping settings in the IDE. In a code window make sure the Menu-->Tools-->Options-->General Tab-->Error Trapping Box-->Break On Unhandled Errors radio button is selected.
See if that makes a difference.

TBC · Feb 16, 2011

Thanks lagbolt - once again you were right

Please Help Run-time error ‘9’

TBC

Registered User.

Attachments

MarkK

bit cruncher

TBC

Registered User.

MarkK

bit cruncher

TBC

Registered User.

Similar threads

Users who are viewing this thread