Excel/VBA中GrandParent-Parent-Child逻辑实现方案咨询

Hey Kev, great question—this is a super common scenario when dealing with hierarchical ticker data, especially when you can't know the full relationship upfront. Let me walk you through a practical, efficient approach I've used for similar Excel/VBA projects:

The key is to build a cached lookup system to avoid redundant data queries, paired with a traversal method to dig up full parent-grandparent relationships on demand. Here's the breakdown:

1. Use a Dictionary for Fast Parent Lookups #

First, store ticker-parent relationships in a Scripting.Dictionary—this gives you O(1) lookup speed, which is way faster than worksheet functions like VLOOKUP, especially with large datasets. If you're fetching data dynamically via your add-in, you'll populate this dictionary as you discover parent relationships.

' Initialize the dictionary (run once at the start)
Dim parentLookup As Object
Set parentLookup = CreateObject("Scripting.Dictionary")
parentLookup.CompareMode = vbTextCompare ' Ignore case if needed

2. Traverse Hierarchies with Iterative Digging #

Recursion works for shallow hierarchies, but an iterative loop is safer for deep chains (avoids stack overflow). This method will keep digging up parents until it hits a ticker with no parent, building the full hierarchy.

Dynamic Traversal (For On-Demand Data Fetching) #

Since you don't know relationships upfront, modify the traversal to fetch parent data via your add-in when a ticker isn't in the cache:

Function GetFullHierarchy(childTicker As String) As Variant
    Dim hierarchy As New Collection
    Dim currentTicker As String
    currentTicker = childTicker
    
    ' Keep digging up parents until we hit a ticker with no parent
    Do While currentTicker <> ""
        hierarchy.Add currentTicker
        
        ' If we don't have this ticker's parent yet, fetch it via your add-in
        If Not parentLookup.Exists(currentTicker) Then
            Dim fetchedParent As String
            ' Replace this line with your add-in's actual method to get the parent
            fetchedParent = YourExcelAddIn.FetchParentTicker(currentTicker)
            parentLookup(currentTicker) = fetchedParent ' Cache the result
        End If
        
        ' Move up to the parent for the next iteration
        currentTicker = parentLookup(currentTicker)
    Loop
    
    ' Reverse the collection to get GrandParent -> Parent -> Child order
    Dim reversedHierarchy() As String
    ReDim reversedHierarchy(1 To hierarchy.Count)
    Dim i As Integer
    For i = 1 To hierarchy.Count
        reversedHierarchy(i) = hierarchy(hierarchy.Count - i + 1)
    Next i
    
    GetFullHierarchy = reversedHierarchy
End Function

3. Handle Variable Hierarchy Lengths #

Since some tickers only have a parent-child relationship (2 levels) and others have grandparent-parent-child (3 levels), you'll need to map the results to your worksheet dynamically:

Sub PopulateHierarchyResults()
    Dim targetSheet As Worksheet
    Set targetSheet = ThisWorkbook.Sheets("HierarchyOutput")
    
    Dim lastRow As Long
    lastRow = targetSheet.Cells(Rows.Count, 1).End(xlUp).Row
    
    ' Assume Column A has your child tickers to process
    For i = 2 To lastRow
        Dim childTicker As String
        childTicker = Trim(targetSheet.Cells(i, 1).Value)
        
        If childTicker <> "" Then
            Dim hierarchy As Variant
            hierarchy = GetFullHierarchy(childTicker)
            
            ' Map results to columns B (GrandParent), C (Parent), D (Child)
            Select Case UBound(hierarchy)
                Case 1 ' No parent at all
                    targetSheet.Cells(i, 4).Value = hierarchy(1)
                Case 2 ' Parent -> Child
                    targetSheet.Cells(i, 3).Value = hierarchy(1)
                    targetSheet.Cells(i, 4).Value = hierarchy(2)
                Case 3 ' GrandParent -> Parent -> Child
                    targetSheet.Cells(i, 2).Value = hierarchy(1)
                    targetSheet.Cells(i, 3).Value = hierarchy(2)
                    targetSheet.Cells(i, 4).Value = hierarchy(3)
                Case Else ' Handle unexpected deep hierarchies if needed
                    targetSheet.Cells(i, 2).Value = "Deep Hierarchy"
                    targetSheet.Cells(i, 3).Value = hierarchy(UBound(hierarchy)-1)
                    targetSheet.Cells(i, 4).Value = hierarchy(UBound(hierarchy))
            End Select
        End If
    Next i
End Sub

4. Optimization Tips #

• Cache computed hierarchies: Add a second dictionary to store full hierarchy arrays for tickers you've already processed—this avoids re-traversing the same chain multiple times.
• Batch fetch where possible: If your add-in supports it, collect all unknown tickers first and fetch their parents in a single batch instead of one-by-one—this cuts down on API/network calls.
• Error handling: Add On Error Resume Next or proper error traps around your add-in's fetch method to handle cases where a ticker has no parent or the fetch fails.

Let me know if you need help adapting this to your specific add-in's data flow—whether you're pulling data via REST APIs, COM objects, or another method, the core hierarchy logic stays the same.

内容的提问来源于stack exchange，提问作者Kev