excel - I am getting an error in my VBScript code "Subscript out of range"

Question

Can you tell me why I am getting the error as Subscript out of range- ArrParent? When I used MsgBox(Lbound(ArrParent)) - it is giving me as 1 and when I used MsgBox(Ubound(ArrParent)) - it is giving me as 960. So in the line below I am getting such out of range error. Dic(ArrParent(Count))=Count+2 Find the full code below:

Code

    Sub ParentPIDNumber(ArrParent,ob3,ob2,ob4)

        Dim Dic,DicItems,Dickeys
        Dim Count

        Set Dic = CreateObject("Scripting.Dictionary")

        Count=LBound(ArrParent)
        'MsgBox(ArrParent(Count))
        Do Until Count > UBound(ArrParent) - 1

           Dic(ArrParent(Count))=Count+2 'here Dictionary keys are holding the row numbers as their Items

        Count=Count+1
        Loop

        ParentChildBinding Dic,ob3,ob2,ob4

    End Sub
   Sub FileredOpenProcessToDel(ob3,ob2,ob4)

        Dim ColumnToFilter,TotalRows
        Dim rngFilter,cel,str,rangesToRemove,x 
        Dim strToRemove : strToRemove = ""
        Dim ArrParent

        objExcel1.ScreenUpdating = False
        objExcel1.Calculation = -4135  'xlCalculationManual
        ColumnToFilter=objExcel1.Application.WorksheetFunction.CountA(ob4.Rows(1)) - 1
        ob4.Range(ob4.Cells(1,ColumnToFilter),ob4.Cells(1,ColumnToFilter)).AutoFilter ColumnToFilter, "Open",,,True 

        'Dim rngFilter as Range
        Set rngFilter = objExcel1.Application.Intersect(ob4.UsedRange,ob4.UsedRange.Offset(1),ob4.Columns(1)).SpecialCells(12)'xlCellTypeVisible
           'MsgBox(rngFilter.Rows.Count)
           REM Do While 1=1
            REM 'Msgbox
           REM Loop
        'msgbox "Filtered range has " & rngFilter.Rows.Count & " rows."
            str=""
            For each cel in rngFilter

              str = str & (cel.row) & ":" & (cel.row) & "," 

            Next

                    rangesToRemove = Split(str,",")

                    For x = UBOUND(rangesToRemove)-1 To LBOUND(rangesToRemove) Step -1

                         strToRemove = strToRemove & rangesToRemove(x)

                            If Len(strToRemove) > 200 then

                                ob4.Range(strToRemove).delete'str & rangesToRemove(x) & ":" & rangesToRemove(x) & ","
                                strToRemove = ""

                            Else

                                strToRemove = strToRemove & ","

                            End If

                    Next
                    If len(strToRemove) > 0 then

                        strToRemove = Mid(strToRemove, 1, Len(strToRemove) - 1)
                        'strToRemove = Left(strToRemove, Len(strToRemove) -1)
                        ob4.Range(strToRemove).delete

                    End If

        ob4.AutoFilterMode = False
        objExcel1.ScreenUpdating = True
        objExcel1.Calculation = -4105   'xlCalculationAutomatic

        TotalRows=objExcel1.Application.WorksheetFunction.CountA(ob4.Columns(1))
        'MsgBox(TotalRows)
        ReDim ArrParent(TotalRows - 2)
        ArrParent=ob4.Range("A2:" & "A" & TotalRows).Value
        'Call to the subroutine
        ParentPIDNumber ArrParent,ob3,ob2,ob4

    End Sub

Please help me here!

Answer 1

The best thing you can do in such a situation is extract the error giving code in a separate script and experiment there until you find the error.

In your case i can't find an error, the following script gives no error but it skips the last element of your array

ArrParent = Array(10, 20, 30)
Count=LBound(ArrParent)
Set Dic = CreateObject("Scripting.Dictionary")
Do Until Count > UBound(ArrParent) - 1
  Dic(ArrParent(Count))=Count+2
  Count=Count+1
Loop
for each key in Dic 
  wscript.echo key & ":" & Dic(key)
next

'10:2
'20:3

I don't have your sourcearray to experiment with but you could try it like this, in order to not have an index out of range error you can use "for each", if you don't need the count variable it would even be the best way to iterate the array.

Count = 1
Set Dic = CreateObject("Scripting.Dictionary")
for each element in ArrParent
  Dic(element)=Count+2
  Count = Count+1
next

for each key in Dic 
  wscript.echo key & ":" & Dic(key)
next
'10:3
'20:4
'30:5