HTML in Cells

Rob van Gelder has done it again. Formatting individual words in a cell is a real pain. Rob has developed some code that allows you enter markup-type language in a cell and covert it to formatted text. The text in cell A6 is his test data. I entered the string in cell A1 and “painted” it in cell A4.


Then I entered a different string in A1 and “painted” it in A1. It works. This is where the real benefit will come. I see an add-in with an Application-level SheetChange event that automatically formats the text you enter using markup tags. In such an add-in, he’ll need some unique identifier to start the string, like <html>. If the user enters

<html>I want to <s>delete</s> <i>remove</i> this section.

then the add-in will read the starting tag and know that it will format the remainder of the cell using Rob’s algorithm. Hopefully to produce

I want to delete remove this section

Maybe <html> isn’t the best tag to use lest the user think they have all html tags available to them. Maybe <rvgml> for Rob van Gelder Markup Language would be more appropriate.

Rob’s method is pretty brute-force in that it reads each character one-by-one, but is genius nonetheless. If you step through the code, you can see just how he goes about formatting the string. By his own account, there isn’t much error checking in it yet, but it worked without error for everything that I tried. If you try it out and find an error, leave a comment here.

Cheers to you, Rob, for another job well done.

Posted in Uncategorized

5 thoughts on “HTML in Cells

  1. Dick,

    Thanks for the support. Appreciate you mentioning it on your page.

    Hopefully it’s of use to someone out there.

    Indeed, a good idea about creating an add-in. A rainy day perhaps :)


  2. Rob: I though the add-in would be a pretty easy jump (you already did the hard work) but now I think you would need to able to do the reverse, that is, convert the cell back to markup so it can be easily edited. Maybe a couple of rainy days.

    I thought you were nuts looping through the characters one-by-one and that there had to be an easier way. So far, all the easier ways I’ve thought of didn’t turn out easier. What other approaches, if any, did you try for parsing out the tags?

  3. Dick,

    I tried a few approaches. As I recall, it became difficult to write the text and format it at the same time.
    It seems as though formatting is stored per character, not between characters.

    The reverse would be great wouldn’t it?
    Users able to format their cells and comments the way they want then store that away in a database for a later reload. If I get time I may well write something.

    Here’s the change trigger:

    Const cHTMLCellID = “

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, str As String

    For Each rng In Target
    If StrComp(Left(rng.Value, Len(cHTMLCellID)), cHTMLCellID, vbTextCompare) = 0 Then
    WriteFormattedText rng, rng.Value
    End If
    End Sub

    I thought I would see what happens if I use html as the identifier.
    Excel takes over and applies it’s own method. Something which totally surprised me.
    What Excel does with the string could well make my code redundant!

    It only seems to work on “paste” operation – I don’t know how to activate the “formatting” without doing a paste – yet.

  4. As a chemist I have to deal with a lot of sub- and super scripting. Here is some code (which could certainly be improved upon) which I wrote to automatically subscript and superscript a string according to the normal uses of a chemist i.e. H2O should appear as H2O and Ca+2 should appear as Ca+2. The code also allows for overriding the automated character changing by bracketing superscripted sections with the character “^”, subscripted sections with the character “~” and plain sections with the character “¬” (note that these control characters are lost once the macro has been run).

  5. Thx again for you wonderfull post, I never knew that Excel can support HTML at all , are those tables from Microsoft Words like the Excel ones? May I also do the same out there? How?

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.