Solved

Error: subscript out of range

Posted on 2012-03-27
2
361 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 500 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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

This article will show you how to use shortcut menus in the Access run-time environment.
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…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

733 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