Edit text within a Textnode, within a cell using VBA.

I ran into a problem recently where I needed to edit text within various textnodes.  Many of those text nodes were nested inside of cells.  Searching the communities and the rest of the internet basically returned other people running into the same problem that I was running into.  In short, there are known issues with using VBA to edit textnodes.  The best information is summed up in this archived thread which has links to another thread that no longer exists:

https://communities.bentley.com/products/programming/microstation_programming/f/archived-microstation-v8i-vba-forum/28652/update-node-text

Long story short, the "Bad Element" error persists when trying to edit .TextLine(1) or (2) etc.  Effectively, this makes it impossible to update the text of an existing text node using VBA.  So in the interest of helping the next person who runs into this problem here is how I managed to work around what appears to be a decade old known bug.

Sub NodeInCell()
    Dim TempEle As Element
    Dim tEle As TextElement
    Dim tnEle As TextNodeElement
    Dim cEle As CellElement
    Dim sArray() As Element
    Dim s As Long
    Dim tEE As ElementEnumerator
    Dim oEE As ElementEnumerator
    Dim oSC As ElementScanCriteria
    
    Set oSC = New ElementScanCriteria
    oSC.ExcludeAllTypes
    oSC.IncludeType msdElementTypeCellHeader
    Set oEE = ActiveModelReference.Scan(oSC)
    sArray = oEE.BuildArrayFromContents
    sStart = LBound(sArray)
    sEnd = UBound(sArray)
    For s = sStart To sEnd
        Set cEle = sArray(s)
        Do While cEle.MoveToNextElement
            Set TempEle = cEle.CopyCurrentElement
            If TempEle.Type = msdElementTypeTextNode Then
                Set tnEle = TempEle
                Set tEE = tnEle.GetSubElements
                If tnEle.TextLine(3) = "text 3" Then
                    Do While tEE.MoveNext
                        If tEE.Current.AsTextElement.Text = "text 1" Then
                            tEE.Current.AsTextElement.Text = "new 1"
                            tEE.Current.Rewrite
                        ElseIf tEE.Current.AsTextElement.Text = "text 2" Then
                            tEE.Current.AsTextElement.Text = "new 2"
                            tEE.Current.Rewrite
                        ElseIf tEE.Current.AsTextElement.Text = "text 3" Then
                            tEE.Current.AsTextElement.Text = "new 3"
                            tEE.Current.Rewrite
                        End If
                    Loop
                End If
            End If
        Loop
    Next s
End Sub

Basically, don't use ".TextLine" at all.  Pick apart the textnode as if it was a cell and interact with each text element as if it is not part of a textnode or cell.  You just have to dig down to them first.  Hopefully this is helpful to somebody else.  After all the struggles I read about online (and my own) it seemed like this was something worth capturing for posterity.

Cheers,

Ian