Building a custom object with a recursive function

[jh_dempsey]

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)

B	C	D	E	F	G
Id	Name	Description	Discipline	Extends	Abstract
ISM_ROOT_FUNCTIONAL	Assets				true
100 Series	0100 Series	Road Geometry		ISM_ROOT_FUNCTIONAL	true
100 Ancillary	Ancillary Assets	Ancillary Assets		100 Series	true
MLOX	Crossover	A 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 Series	100 Ancillary
GNDP	Depot	A Highways England owned maintenance facility; typically encompassing salt barns, storage/maintenance facilities, offices, and vehicle storage.	100 Series	100 Ancillary
GNEQ	Equipment Store	A small building or shed designed to provide storage for highways-related equipment.	100 Series	100 Ancillary
GNGB	Salt Bin	Grit/Salt Bin.	100 Series	100 Ancillary
GNTB	Toilet Block	Toilet buildings/blocks	100 Series	100 Ancillary
300 Series	0300 Series	Fencing		ISM_ROOT_FUNCTIONAL	true
300 Gates/Barriers	Gates and Barrier Assets	Gates and Barrier Assets		300 Series	true
CCAG	Anti-Glare Barrier	A 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 Series	300 Gates/Barriers
CCBW	Block Wall	Free standing or retaining walls that have an important visual or screening objective	300 Series	300 Gates/Barriers
CCBR	Brick Wall	Free standing or retaining walls that have an important visual or screening objective	300 Series	300 Gates/Barriers
FEGA	Carriageway Gate	A barrier across the carriageway to stop traffic proceeding	300 Series	300 Gates/Barriers
CCFE	Fence	Free standing fences of timber or other materials	300 Series	300 Gates/Barriers
CCFE_FEFG	Fence Gate	A gate in a fence, wall or barrier which allows access across the fence, wall or barrier.	300 Series	300 Gates/Barriers
FESG	Snow Gate	A Barrier across the carriageway to stop traffic proceeding due to heavy or potentially heavy snow fall	300 Series	300 Gates/Barriers
CCSW	Stone Wall	Free standing or retaining walls that have an important visual or screening objective	300 Series	300 Gates/Barriers
300 Non-Motorised User	Non-Motorised User Assets	Non-Motorised User Assets		300 Series	true
FESI	Stile	A stile provides a passage through or over a fence or boundary via steps or narrow gaps.	300 Series	300 Non-Motorised User

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

Level 1	Level 2	Level 3	Asset Name
100 Series			0100 Series
	100 Ancillary		Ancillary Assets
		MLOX	Crossover
		GNDP	Depot
		GNEQ	Equipment Store
		GNGB	Salt Bin
		GNTB	Toilet Block
300 Series			0300 Series
	300 Gates/Barriers		Gates and Barrier Assets
		CCAG	Anti-Glare Barrier
		CCBW	Block Wall
		CCBR	Brick Wall
		FEGA	Carriageway Gate
		CCFE	Fence
		CCFE_FEFG	Fence Gate
		FESG	Snow Gate
		CCSW	Stone Wall
	300 Non-Motorised User		Non-Motorised User Assets
		FESI	Stile

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)

    ' 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

' #############
' 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

 

[DanteAmor]

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.

VBA Code to run hierarchy

Hi, I need help with VBA code so that I can enter a supervisor's ID and name and then run the macro to build a complete hierarchy for that person. Per the example below I have a list of supervisors and employees. I want to type in a supervisor's ID and name (ID "1000", Name "Person A") as o...

www.mrexcel.com