How to Format Excel Range text to HTML

Question

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

Rather than re-inventing the wheel, you might check out the RangetoHTML() function on this page — Spectral Instance
– Spectral Instance, Commented May 11, 2022 at 18:54

Tim Williams · Accepted Answer · 2022-05-11 20:46:46Z

1

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...

edited May 11, 2022 at 20:46

answered May 11, 2022 at 19:16

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

hozer95 Over a year ago

works well enough for my use case, thanks!

Tim Williams Over a year ago

If only some cells have partial bold formatting then you can check rngText.Font.Bold first to see if you can avoid the looping - if True = just wrap the whole text in "<b></b>", if False = just return the whole text, if Null = do the loop as above

Collectives™ on Stack Overflow

How to Format Excel Range text to HTML

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related