Building a custom object with a recursive function

jh_dempsey

New Member
Joined
May 21, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi All

Im struggling to build a recursive function that can read a spreadsheet I'm given to build up the "tree" structure/heirarchy for use in my own spreadsheet.

This is a sample of the data I'm trying to read with the recursive function. Column F tells me which item this item is a child of.
For example
The item whose ID is "MLOX" is a child of the "100 Ancillary" item
The "100 Ancillary" is a child of the "100 Series" item.
The "100 Series" item is a child of the "ISM_ROOT_FUNCTIONAL" (which is the root item)

BCDEFG
IdNameDescriptionDisciplineExtendsAbstract
ISM_ROOT_FUNCTIONALAssetstrue
100 Series0100 SeriesRoad GeometryISM_ROOT_FUNCTIONALtrue
100 AncillaryAncillary AssetsAncillary Assets100 Seriestrue
MLOXCrossoverA pedestrian or vehicular crossing of a footway/cycleway, verge, central island or central reserve. This includes minor junctions, driveways, field entrances and central reserve crossovers.100 Series100 Ancillary
GNDPDepotA Highways England owned maintenance facility; typically encompassing salt barns, storage/maintenance facilities, offices, and vehicle storage.100 Series100 Ancillary
GNEQEquipment StoreA small building or shed designed to provide storage for highways-related equipment.100 Series100 Ancillary
GNGBSalt BinGrit/Salt Bin.100 Series100 Ancillary
GNTBToilet BlockToilet buildings/blocks100 Series100 Ancillary
300 Series0300 SeriesFencingISM_ROOT_FUNCTIONALtrue
300 Gates/BarriersGates and Barrier AssetsGates and Barrier Assets300 Seriestrue
CCAGAnti-Glare BarrierA barrier designed to prevent glare from headlights crossing onto other areas of carriageway, or neighbouring property. Not considered to offer protection against the passage of large animals.300 Series300 Gates/Barriers
CCBWBlock WallFree standing or retaining walls that have an important visual or screening objective300 Series300 Gates/Barriers
CCBRBrick WallFree standing or retaining walls that have an important visual or screening objective300 Series300 Gates/Barriers
FEGACarriageway GateA barrier across the carriageway to stop traffic proceeding300 Series300 Gates/Barriers
CCFEFenceFree standing fences of timber or other materials300 Series300 Gates/Barriers
CCFE_FEFGFence GateA gate in a fence, wall or barrier which allows access across the fence, wall or barrier.300 Series300 Gates/Barriers
FESGSnow GateA Barrier across the carriageway to stop traffic proceeding due to heavy or potentially heavy snow fall300 Series300 Gates/Barriers
CCSWStone WallFree standing or retaining walls that have an important visual or screening objective300 Series300 Gates/Barriers
300 Non-Motorised UserNon-Motorised User AssetsNon-Motorised User Assets300 Seriestrue
FESIStileA stile provides a passage through or over a fence or boundary via steps or narrow gaps.300 Series300 Non-Motorised User

Eventually - I need to get my output into this format in my spreadsheet:

Level 1Level 2Level 3Asset Name
100 Series0100 Series
100 AncillaryAncillary Assets
MLOXCrossover
GNDPDepot
GNEQEquipment Store
GNGBSalt Bin
GNTBToilet Block
300 Series0300 Series
300 Gates/BarriersGates and Barrier Assets
CCAGAnti-Glare Barrier
CCBWBlock Wall
CCBRBrick Wall
FEGACarriageway Gate
CCFEFence
CCFE_FEFGFence Gate
FESGSnow Gate
CCSWStone Wall
300 Non-Motorised UserNon-Motorised User Assets
FESIStile

I have created a custom "Classification" object, and one of its properties is its children which is a collection of Classification objects.
Therefore I have a series of nested collections of "Classification" objects.

I'm trying to build this object using a recursive function but I'm getting strange results, and the child values of each item are not the ones I'm expecting.
I want to use a recursive function because I never know how many levels of hierarchy there might be when I am given a spreadsheet.

Can anyone see where I might be going wrong?

This is my code so far. I've just shown my custom class and the recursive function.
(I haven't shown the code which reads the rootClassInfo object to put it in the format I want in my spreadsheet)

VBA Code:
    ' Create root node as a starting point
    Dim rootClassInfo As New ClassificationInfo
    rootClassInfo.id = ismClassesWS.Cells(2, 2).value
    rootClassInfo.Name = ismClassesWS.Cells(2, 3).value
    rootClassInfo.AllowableParentTypes = ismClassesWS.Cells(2, 18).value
        
    ' Now run the recursive function to add all the other classification items
    AddChildClassifications rootClassInfo, ismClassesWS

Function AddChildClassifications(classInfo As ClassificationInfo, ws As Worksheet) As Boolean

    Dim row As Long
    row = 3
    
    Do Until ws.Cells(row, 6).value = "" And ws.Cells(row + 1, 6).value = "" And ws.Cells(row + 2, 6).value = ""
        
        ' Find all entries whose parent is that provided in the parentID attribute and add them to that classification item
        ' Perform a recursive call on this function until no more child items are found
        If ws.Cells(row, 6).value = classInfo.id Then
            
            ' This belongs to the provided parents name. Add it as a child. In order to add it to the right entry in the dictionary
            ' we need to recursivly search the classDict to find the object we want since it could be many levels deep
            Dim classInfoNew As New ClassificationInfo
            classInfoNew.id = ws.Cells(row, 2).value
            classInfoNew.Name = ws.Cells(row, 3).value
            classInfoNew.AllowableParentTypes = ws.Cells(row, 18).value
            
            ' Add item to the dictionary entry
            classInfo.AddChild classInfoNew
            
            ' Perform a recursive search, so find any entries in the list that are a parent of the entry we just found
            AddChildClassifications classInfoNew, ws
            
        End If
        
        ' Move to next row
        row = row + 1
    Loop
    
    AddChildClassifications = True
    
End Function

This is the custom class I've made. I've stripped out some of the code to show you just the relevant parts

VBA Code:
' #############
' Id Properties
' #############

Property Get id() As String
    id = c_Id
End Property

Property Let id(dValue As String)
    c_Id = dValue
End Property

' ###############
' Name Properties
' ###############

Property Get Name() As String
    Name = c_Name
End Property

Property Let Name(dValue As String)
    c_Name = dValue
End Property

' ###############
' Assignable Properties
' ###############

Property Get Assignable() As Boolean
    Assignable = c_Assignable
End Property

Property Let Assignable(dValue As Boolean)
    c_Assignable = dValue
End Property

'' #################
'' Parent Properties
'' #################
'
'Property Get Parent() As String
'    Parent = c_Parent
'End Property
'
'Property Let Parent(dValue As String)
'    c_Parent = dValue
'End Property

' #################
' Children Object
' #################

Property Get Children() As Object
    Set Children = c_Children
End Property

Public Sub AddChild(child As ClassificationInfo)
    c_Children.Add child
End Sub

' #################
' Allowable Parent Objects
' #################

Property Get AllowableParentTypes() As String
    AllowableParentTypes = c_AllowableParentTypes
End Property

Property Let AllowableParentTypes(parentTypes As String)
    c_AllowableParentTypes = parentTypes
End Property

' Set some default values on initialization
Private Sub Class_Initialize()
    c_Id = ""
    c_Name = ""
'   c_Assignable = True
    Set c_Children = New Collection
    c_AllowableParentTypes = ""
End Sub
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Is it necessary with a function?
It can be with a macro, check the following link, there is a couple of code for hierarchy. And mine, by the way, is with recursion.

 

Watch MrExcel Video

Forum statistics

Threads
1,113,903
Messages
5,544,959
Members
410,643
Latest member
sng
Top