[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Error: subscript out of range

Posted on 2012-03-27
2
Medium Priority
?
367 Views
Last Modified: 2012-03-27
Hi,

I am getting a frustrating error that I can't seem to fix.

I have the following code that i am trying to build a treeview structure with (i am trying a different approach)...

Sub trial_ranges_dictionary()

Dim a(), c()
Dim i, k, l As Long
Dim TaskRships As Range
Dim taskItems As Range
Dim parents As Object
Dim children As Object
Dim intParentCol As Integer
Dim intChildCol As Integer
Dim arrRel1()
Dim arrRel2()


' Define the column numbers for the parents and children in the task relationships range.
intParentCol = 1
intChildCol = 2

Set parents = CreateObject("scripting.dictionary")
Set children = CreateObject("scripting.dictionary")

' Use the dynamically named ranges as the base range of data.
Set taskItems = Range("TaskIDNames")
Set TaskRships = Range("ParentTaskIDs")

'Build a list of parents at level 0
k = 1
l = 0
For Each rw In TaskRships.Rows
    If k = 1 Then
        parents.Add rw.Columns(intParentCol).Value, 0
        k = k + 1
    Else
        If parents.exists(rw.Columns(intParentCol).Value) Then
            GoTo nextloop
        Else
            k = k + 1
            parents.Add rw.Columns(intParentCol).Value, 0
        End If
    End If
nextloop:
Next rw
    
    a = parents.keys
    
'   Build a list of children

'Build a list of unique children parts - level 1
k = 1
l = 0
For Each rw In TaskRships.Rows
    If k = 1 Then
        children.Add rw.Columns(intChildCol).Value, 1
        k = k + 1
    Else
        If children.exists(rw.Columns(intChildCol).Value) Then
            GoTo nextloop1
        Else
            children.Add rw.Columns(intChildCol).Value, 1
            k = k + 1
        End If
    End If
nextloop1:
Next rw
    
b = children.keys

'   Build a list of parents that are also children. Those that are not children are top level parent items.
'   for each parent test to see if it exists in the children list.
'Remove any parent item that is also in the child list, leaving on top level parent items.
For i = 0 To UBound(a)
    If children.exists(a(i)) Then
        parents.Remove a(i)
    End If
Next i

' List the top level parent items.
a = parents.keys

' Now - build a list of children with their related parents - recursively build lists and add nodes to the treeview. Note :not working yet !!
' perhaps call another sub / function to be called no matter how many levels deep it goes.
' Figure out a way to sort the entries based on a sequence number. If no sequence number given, then just sort on the order they arrive.

i = 1
For i = 0 To UBound(b)
    children.Remove b(i)
Next i

'Build a full (non-unique) list of children parts with parents - level 1
k = 1
i = 0
' Use an array to capture all of the parent and child relationships.
For Each rw In TaskRships.Rows
    arrRel1(i) = (rw.Columns(intParentCol).Value)
    arrRel2(i) = (rw.Columns(intChildCol).Value)
    Debug.Print arrRel1(i), arrRel2(i)
    i = i + 1
Next rw


' Match the parents list to the first level children items.
' In the following code, replace debug.print with node.add to build the treeview
For i = LBound(a) To UBound(a)
        For j = 0 To lngChildUbound - 1 ' UBound(arrRel1)
            If arrRel1(j) = a(i) Then
                Debug.Print , , arrRel2(j)
                'Insert function that checks for the existence of the child in the parent side
                'If the child exists as a parent, then iterate and add nodes - have an add_child sub / function that calls itself.
                If checkforparent(arrRel1, arrRel2(j)) Then
                
                Debug.Print "Child exists as a parent"
                End If
            End If
        Next j
Next i

End Sub

Open in new window

(Please note that I know the code is not attached to a treeview - yet ! )

I am getting a
'Subscript out of range'
error on line 94 of the above code.

If I change the dim statement to

dim arrRel1(10000), arrRel2(10000)

Open in new window


then it works fine.....


I don't want to have to specify the size of the dimension in the 'dim' statement as I don't know how big it will get. Also, I don't understand why I don't need to specify the size of the array for variables a(), b() and c() - it assigns the dictionary.keys values to these variables without having to specify a dimension size.

Hope this makes sense - any help appreciated.


Regards,
Craig
BillsOfMaterials-help-r1.xlsm
0
Comment
Question by:slobber72
2 Comments
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 1500 total points
ID: 37775034
You need to Redim the array like :

 Use an array to capture all of the parent and child relationships.
For Each rw In TaskRships.Rows
Redim Preserve arrRel1(i + 1)
Redim Preserve arrRel2(i + 1)
    arrRel1(i) = (rw.Columns(intParentCol).Value)
    arrRel2(i) = (rw.Columns(intChildCol).Value)
    Debug.Print arrRel1(i), arrRel2(i)
    i = i + 1
Next rw

Open in new window

0
 

Author Comment

by:slobber72
ID: 37775057
Thanks jacko72,

I ended up with this

'Build a full (non-unique) list of children parts with parents - level 1
k = 1
i = 0
' Use an array to capture all of the parent and child relationships.
For Each rw In TaskRships.Rows

    ReDim Preserve arrRel1(i + 1)
    ReDim Preserve arrRel2(i + 1)
    j = i + 1
    arrRel1(j) = (rw.Columns(intParentCol).Value)
    arrRel2(j) = (rw.Columns(intChildCol).Value)
    Debug.Print arrRel1(j), arrRel2(j)
    i = i + 1
Next rw

Open in new window


Works great !! Thanks very much !
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question