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
Solved

Error: subscript out of range

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

809 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