Improve company productivity with a Business Account.Sign Up

x
?
Solved

Error: subscript out of range

Posted on 2012-03-27
2
Medium Priority
?
373 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
You can use the network upload option and the Office 365 Import service to bulk-import PST files to user mailboxes. Network upload means that you upload the PST files a temporary storage area in the Microsoft cloud.
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…
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…

580 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