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.
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
Cheers
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?
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
Next
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.
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).
Sub AutoSuperAndSub(cref) Dim SupB(20) As Integer, SupE(20) As Integer, SubB(20) As Integer, SubE(20) As Integer Dim PPos As Integer, n As Integer, Nsup As Integer, Nsub As Integer Dim Dat As String, Char As String, NChar As String Dim PCh As Boolean, Num As Boolean, Ch As Boolean, PlMi As Boolean Dim EventStat As Boolean NChar = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ)]" PPos = 0 PCh = False n = 1 Nsup = 0 Nsub = 0 Dat = Range(cref).Value Do Char = Mid(Dat, n, 1) Num = IsNumeric(Char) If InStr("+-", Char) > 0 Then PlMi = True Else PlMi = False If InStr(NChar, Char) > 0 Then Ch = True Else Ch = False 'manual setting of sub and super scripting If Char = "^" Then 'superscript next characters Dat = Left(Dat, n - 1) & Mid(Dat, n + 1, 9999) Nsup = Nsup + 1 SupB(Nsup) = n n = InStr(Dat, Char) Dat = Left(Dat, n - 1) & Mid(Dat, n + 1, 9999) SupE(Nsup) = n PPos = 0 ElseIf Char = "~" Then 'subscript next characters Dat = Left(Dat, n - 1) & Mid(Dat, n + 1, 9999) Nsub = Nsub + 1 SubB(Nsub) = n n = InStr(Dat, Char) Dat = Left(Dat, n - 1) & Mid(Dat, n + 1, 9999) SubE(Nsub) = n PPos = 0 ElseIf Char = "¬" Then 'characters unchanged Dat = Left(Dat, n - 1) & Mid(Dat, n + 1, 9999) If PPos = -1 Then SubE(Nsub) = n If PPos = 1 Then SupE(Nsup) = n n = InStr(Dat, Char) Dat = Left(Dat, n - 1) & Mid(Dat, n + 1, 9999) PPos = 0 'now automatic setting of sub and super scripts ElseIf PPos = 0 Then If Ch Then ElseIf (PCh And Num) Then Nsub = Nsub + 1 SubB(Nsub) = n PPos = -1 ElseIf (PCh And PlMi And IsNumeric(Mid(Dat, n + 1, 1))) Then Nsup = Nsup + 1 SupB(Nsup) = n PPos = 1 End If ElseIf PPos = -1 Then If Num Then ElseIf PlMi Then SubE(Nsub) = n Nsup = Nsup + 1 SupB(Nsup) = n PPos = 1 Else SubE(Nsub) = n PPos = 0 End If ElseIf PPos = 1 Then If Num Then Else SupE(Nsup) = n PPos = 0 End If End If PCh = Ch n = n + 1 Loop Until n > Len(Dat) EventStat = Application.EnableEvents Application.EnableEvents = False Range(cref) = Dat For n = 1 To Nsup Range(cref).Characters(Start:=SupB(n), Length:=SupE(n) - SupB(n)).Font.Superscript = True Next For n = 1 To Nsub Range(cref).Characters(Start:=SubB(n), Length:=SubE(n) - SubB(n)).Font.Subscript = True Next Application.EnableEvents = EventStat End SubThx 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?