Tree Data Structure from Excel Data

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Tree Data Structure from Excel Data

  1. #1
    New Member steve112's Avatar
    Join Date
    Dec 2008
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Tree Data Structure from Excel Data

     
    I am trying to write a recursive algorithm in VBA that can read data from a table in an Excel range, for example:

    NodeIDParentID
    n0
    n1n0
    n2n0
    n3n0
    n4n3
    n5n3
    n6n3
    n7n5
    n8n5
    n9n5


    As it reads the data, the "Tree" class (data structure) should recursively grow, adding the children to the appropriate parent. At the end of the routine, I should have a tree data structure in memory that corresponds to the parent-child relationships defined in the table.

    Any help would be greatly appreciated. Thank you in advance.

  2. #2
    Board Regular Oorang's Avatar
    Join Date
    Mar 2005
    Posts
    2,071
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Tree Data Structure from Excel Data

    Hi Steve,
    When you say "In memory" that a teeny bit vague. Do you mean in a nested series of arrays, collections, UDTs? How will the data be used once it's in memory? Also recursion, while using fewer lines, is generally discouraged as it is hard to maintain, can lead to stack overflow, etc, etc. It's generally best to avoid recursion when possible.

    Here is a general example of how to load tree data. To use it:
    1.) Create a userform in Excel.
    2.) Right click on your toolbox and select "Additional Controls"
    3.) Check mark "Microsoft Treeview Control 6.0"
    4.) Add a treeview to your userform.
    5.) Paste this code into the userform code.

    Code:
    Option Explicit
    Private Sub UserForm_Initialize()
        Dim node As MSComctlLib.node
        With Me.TreeView1
            .Style = tvwTreelinesPlusMinusPictureText
            LoadRangeToTreeView ActiveSheet.UsedRange, Me.TreeView1, True
            For Each node In .Nodes
                node.Expanded = True
            Next
        End With
    End Sub
    Private Sub LoadRangeToTreeView( _
        ByRef sourceRange As Excel.Range, _
        ByRef destinationTree As MSComctlLib.TreeView, _
        Optional ByVal hasHeader As Boolean = False)
        Dim ws As Excel.Worksheet
        Dim lngUprBndRow As Long
        Dim lngLwrBndRow As Long
        Dim lngIndxRow As Long
        Dim lngIndxCol1 As Long
        Dim lngIndxCol2 As Long
        Dim strParent As String
        Dim strNode As String
        Set ws = sourceRange.Parent
        lngLwrBndRow = sourceRange.Row
        lngUprBndRow = sourceRange.Rows.Count + lngLwrBndRow - 1
        If hasHeader Then
            lngLwrBndRow = lngLwrBndRow + 1
        End If
        lngIndxCol1 = sourceRange.Column
        lngIndxCol2 = lngIndxCol1 + 1
        destinationTree.Nodes.Clear
        For lngIndxRow = lngLwrBndRow To lngUprBndRow
            strNode = ws.Cells(lngIndxRow, lngIndxCol1)
            strParent = ws.Cells(lngIndxRow, lngIndxCol2)
            If LenB(strParent) Then
                destinationTree.Nodes.Add strParent, tvwChild, strNode, strNode
            Else
                destinationTree.Nodes.Add Key:=strNode, Text:=strNode
            End If
        Next
    End Sub
    • Get better answers! Include your version of Office in your post.

  3. #3
    New Member steve112's Avatar
    Join Date
    Dec 2008
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Re: Tree Data Structure from Excel Data

    Oorang,

    Thank you for your quick reply. However, what I need is some code (a class module named "CTree" for example, that will be a dynamic data structure. I am using a collection called "Children" for each node that holds the child nodes. I am not using the TreeView control as I just need to create the tree in memory for now, so that I can draw a diagram on sheet -- eventually -- from the tree data structure in memory.

    NodeIDParentID
    1 -
    21
    31
    41
    53
    63
    76
    86
    97
    107
    117


    The result will be a tree structure resembling:

    1 - 2 (nodes 2, 3, and 4 are children of 1)
    - 3 - 5 (nodes 5 and 6 are children of 3)
    - 6 -7 -9 (nodes 7 and 8 are children of 6)
    -10 (nodes 9, 10, and 11 are children of 8)
    -11
    -8
    - 4

    Is there a way I can get my Excel file to you? I do not see a way to forward an attachment. Thanks again in advance.

  4. #4
    Board Regular Oorang's Avatar
    Join Date
    Mar 2005
    Posts
    2,071
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Tree Data Structure from Excel Data

    Sorry Steve, I don't accept attachments. Perhaps you could post what code you have now, then we can help you with the bits that are giving you trouble.
    • Get better answers! Include your version of Office in your post.

  5. #5
    New Member steve112's Avatar
    Join Date
    Dec 2008
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Tree Data Structure from Excel Data

    Thanks for your replies. I just bought a book on algorithms that may have what I need. I will post my code when I figure it out. Best regards.

  6. #6
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    18,987
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Tree Data Structure from Excel Data

    Hi Steve
    Welcome to the board

    I'm not sure I understood correctly but it seems you want to

    - store the tree information in a structure in memory
    - later on access it to, for ex., draw a diagram

    You could write a class module to deal with trees, but that doesn't seem necessary in this case. I post a simple example where you load the tree information into a collection and then write it into the worksheet.

    To test it

    1 - run LoadTree

    This loads the tree information. The information is kept in memory after LoadTree ends.

    2 - run DisplayTree

    This displays the tree information like in the table I post.

    Hope this helps


    Code:
    Option Explicit
     
    Public collRoot As Collection
     
    Sub LoadTree()
    Dim coll As Collection
    Dim rParents As Range, rNode As Range
     
        Set collRoot = Nothing
        Set collRoot = New Collection
        Set rParents = Range("A2", Range("A2").End(xlDown))
     
        ' Store the tree in a collection
        On Error Resume Next
        For Each rNode In rParents
            Set coll = Nothing
            Set coll = collRoot(rNode.Value)
            If coll Is Nothing Then collRoot.Add New Collection, rNode.Value
            collRoot(rNode.Value).Add rNode.Offset(, 1).Value
        Next rNode
    End Sub
     
    ' Display the tree
    Sub DisplayTree()
    Dim rOut As Range, sRootNode As String, lRow As Long
     
        sRootNode = "r"
        Set rOut = Range("D2")
     
        rOut = sRootNode
        Call DisplayTree1(sRootNode, rOut, lRow, 1)
    End Sub
     
    Sub DisplayTree1(ByVal sParent As String, rOut As Range, _
                    ByRef lRow As Long, ByVal lLevel As Long)
    Dim vChild, coll As Collection
     
        On Error Resume Next
        For Each vChild In collRoot(sParent)
            lRow = lRow + 1
            rOut.Offset(lRow, lLevel) = vChild
            Set coll = Nothing
            Set coll = collRoot(vChild)
            If Not coll Is Nothing Then Call DisplayTree1(vChild, rOut, lRow, lLevel + 1)
        Next vChild
    End Sub

    ABCDEFGHIJ
    1ParentChild
    2rr_1 r
    3rr_2 r_1
    4r_1r_1_1 r_1_1
    5r_1r_1_2 r_1_1_1
    6r_1_1r_1_1_1 r_1_1_2
    7r_2r_2_1 r_1_2
    8r_1r_1_3 r_1_3
    9r_2r_2_2 r_2
    10r_2_1r_2_1_1 r_2_1
    11r_2_1_1r_2_1_1_1 r_2_1_1
    12r_2_1_1r_2_1_1_2 r_2_1_1_1
    13r_2_1_1_2r_2_1_1_2_1 r_2_1_1_2
    14r_2_1_1r_2_1_1_3 r_2_1_1_2_1
    15r_2_1r_2_1_2 r_2_1_1_3
    16r_2r_2_3 r_2_1_2
    17r_2_3r_2_3_1 r_2_2
    18r_1_1r_1_1_2 r_2_3
    19 r_2_3_1
    20
    [treexlsb.xlsb]Sheet1



    P. S. Anyway, it was a good idea to buy a book on algorithms. There are lots of ways to do this kind of problems using whatever is available in the languages like the Treeview control that Oorang used or the Collection that I used, or a Dictionary, etc. It's important, however, to understand the classic approach with linked lists of arrays.
    Last edited by pgc01; Dec 27th, 2008 at 10:16 PM.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  7. #7
    New Member cainhill's Avatar
    Join Date
    Sep 2014
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Tree Data Structure from Excel Data

    I'd like to thank you PGC. Your way of thinking about this tree recursion problem informed some of my Excel template development.

    I wanted to provide everyday Excel users with a way to easily take a hierarchy that is stored in an table and convert it into a mind map.

    The resulting Excel template is described and shared here: Cain Hill - How to make a huge mindmap from an Excel spreadsheet in less than 5 minutes
    Last edited by cainhill; Sep 12th, 2014 at 10:52 AM.

  8. #8
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    18,987
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Tree Data Structure from Excel Data

    Hi Cain

    Thank you.

    It's always rewarding when a solution is used in a meaningful application.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  9. #9
    New Member
    Join Date
    Jan 2013
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Tree Data Structure from Excel Data

      
    Quote Originally Posted by Oorang View Post
    Hi Steve,
    When you say "In memory" that a teeny bit vague. Do you mean in a nested series of arrays, collections, UDTs? How will the data be used once it's in memory? Also recursion, while using fewer lines, is generally discouraged as it is hard to maintain, can lead to stack overflow, etc, etc. It's generally best to avoid recursion when possible.

    Here is a general example of how to load tree data. To use it:
    1.) Create a userform in Excel.
    2.) Right click on your toolbox and select "Additional Controls"
    3.) Check mark "Microsoft Treeview Control 6.0"
    4.) Add a treeview to your userform.
    5.) Paste this code into the userform code.

    Code:
    Option Explicit
    Private Sub UserForm_Initialize()
        Dim node As MSComctlLib.node
        With Me.TreeView1
            .Style = tvwTreelinesPlusMinusPictureText
            LoadRangeToTreeView ActiveSheet.UsedRange, Me.TreeView1, True
            For Each node In .Nodes
                node.Expanded = True
            Next
        End With
    End Sub
    Private Sub LoadRangeToTreeView( _
        ByRef sourceRange As Excel.Range, _
        ByRef destinationTree As MSComctlLib.TreeView, _
        Optional ByVal hasHeader As Boolean = False)
        Dim ws As Excel.Worksheet
        Dim lngUprBndRow As Long
        Dim lngLwrBndRow As Long
        Dim lngIndxRow As Long
        Dim lngIndxCol1 As Long
        Dim lngIndxCol2 As Long
        Dim strParent As String
        Dim strNode As String
        Set ws = sourceRange.Parent
        lngLwrBndRow = sourceRange.Row
        lngUprBndRow = sourceRange.Rows.Count + lngLwrBndRow - 1
        If hasHeader Then
            lngLwrBndRow = lngLwrBndRow + 1
        End If
        lngIndxCol1 = sourceRange.Column
        lngIndxCol2 = lngIndxCol1 + 1
        destinationTree.Nodes.Clear
        For lngIndxRow = lngLwrBndRow To lngUprBndRow
            strNode = ws.Cells(lngIndxRow, lngIndxCol1)
            strParent = ws.Cells(lngIndxRow, lngIndxCol2)
            If LenB(strParent) Then
                destinationTree.Nodes.Add strParent, tvwChild, strNode, strNode
            Else
                destinationTree.Nodes.Add Key:=strNode, Text:=strNode
            End If
        Next
    End Sub
    When I ran this code on a four column WS I receive the error message: (runtime 35601 Element not found) at the line: "destinationTree.Nodes.Add strParent, tvwChild, strNode, strNode". I am running office 2010 32 bit on Windows 10. Any suggestions as to why this is occurring. I look at many comments on the net with a variety of solutions. Thought I would go back to the author for your comments before looking any further.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com