How to Format Excel Range text to HTML-CodePudding

I'm developing a VBA function that will upload text from specific Excel range to a server. Since I'm also displaying the server data in a web app, I want to keep the source formatting (ie. if some words are bold in Excel, I want to keep that). For the moment, I only care about bold formatting. I developed the below solution, however it is extremely slow (converting a single cell with several sentences can take ~2 minutes). Looking to see if there is a faster way of achieving this.

Public Function getTextWithBold(rngText As Range) As String

    Dim currentIsBold As Boolean
    currentIsBold = False
    Dim returnValue As String
    returnValue = ""
    
    For i = 1 To Len(rngText.Value)
        If rngText.characters(i, 1).Font.Bold = True Then

            If currentIsBold = False Then
                currentIsBold = True
                returnValue = returnValue & "<b>" & rngText.characters(i, 1).Text
            Else
                returnValue = returnValue & rngText.characters(i, 1).Text
            End If

        Else

            If currentIsBold = True Then
                currentIsBold = False
                returnValue = returnValue & "</b>" & rngText.characters(i, 1).Text
            Else
                returnValue = returnValue & rngText.characters(i, 1).Text
            End If

        End If
        
        If ((rngText.characters(i, 1).Font.Bold = True) And (i = Len(rngText.Value))) Then
            returnValue = returnValue & "</b>"
        End If

   Next i

   getTextWithBold = returnValue

End Function

CodePudding user response：

You want to limit as much as possible the number of times you access Characters.

Should be faster:

Public Function getTextWithBold(rngText As Range) As String
    Dim isBold As Boolean, currBold As Boolean, i As Long
    Dim rv As String, txt As String
    txt = rngText.Value
    For i = 1 To Len(txt)
        isBold = rngText.Characters(i, 1).Font.Bold
        If isBold <> currBold Then
            rv = rv & IIf(currBold, "</b>", "<b>")
            currBold = isBold
        End If
        rv = rv & Mid(txt, i, 1)
    Next i
    getTextWithBold = IIf(currBold, "</b>", "") & rv
End Function

EDIT: only about 2-3x faster...