Solved

Error: subscript out of range

Posted on 2012-03-27
2
354 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 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This article will show you how to use shortcut menus in the Access run-time environment.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now