patternMinor

HTML tag-adding function for Excel Formatted cells

Submitted by: @import:stackexchange-codereview·Mar 10, 2026·

Viewed 0 times

tagexcelfunctionaddingformattedcellsforhtml

Problem

I have several functions, whose common end goal is to make it possible to write the contents of an Excel cell to Microsoft Access and be able to bring it back from Access as well, all while retaining formatting! The function that I want to review, below, works very well but it is somewhat slow. It takes a cell as an argument and returns a string with HTML-like tags indicating where to put formats.

Example:

Input:

Output:

4. Demande interne pour hydraulique : Les achats de castings de roue (aubes, plafond, ceinture) sont à acheter très rapidement tel qu’indiqué dans la cédule TTS.[LF] Cette demande doit être...

The part that is slow is continually accessing every single character through Range.Characters. I'm aware it is very expensive and I'd like to replace it with something else. If it was possible to take that into memory it would go a lot faster I'm sure!

To test the function, write some formatted text to a cell and simply use the following line while the cell is selected:

```
debug.Print fnConvert2HTML(Activecell)

Function fnConvert2HTML(myCell As Range) As String
Dim bldTagOn As Boolean, itlTagOn As Boolean, ulnTagOn As Boolean, colTagOn As Boolean, sizTagOn As Boolean
Dim i As Integer, chrCount As Integer
Dim chrSiz As String, chrLastSiz As String, htmlTxt As String
Dim decCol As Long, decLastCol As Long
Dim chr As String

bldTagOn = False
itlTagOn = False
ulnTagOn = False
colTagOn = False
sizTagOn = False
decCol = 0
chrCount = myCell.Characters.Count

For i = 1 To chrCount
With myCell.Characters(i, 1)

decCol = .Font.Color
If decCol <> decLastCol Then
htmlTxt = htmlTxt & ""
decLastCol = decCol
End If

chrSiz = .Font.Size
If Len(chrSiz) = 1 Then chrSiz = "0" & chrSiz
If Not chrLastSiz = chrSiz Then
htmlTxt = htmlTxt & ""
End If
chrLastSiz =

Solution

This isn't a full answer, but information related to @Raystafarian's comment above. (I can't fit all the info into a comment)

I created a workbook with the following entered into Range("A1"):

Tearing apart the xlsx file and opening it as a zip, I was able to find this formatted string in the sharedStrings.xml file. It has all the fully formatted text as shown below.

With this information, you'd have to

Save a copy your xlsx file

Rename the file to change the extension to .zip

Extract the xl/sharedStrings.xml file from the zip archive

Read that XML file and process the strings inside

There are likely more details to the process. I've looked at xlsx/zip archives on a number of occasions, but have never tried to manipulate the linked data within it.

Code Snippets

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="1" uniqueCount="1">
  <si>
    <r>
      <t xml:space="preserve">This </t>
    </r>
    <r>
      <rPr><b/><i/><sz val="11"/><color rgb="FF7030A0"/><rFont val="Calibri"/><family val="2"/><scheme val="minor"/></rPr>
      <t>wildass</t>
    </r>
    <r>
      <rPr><sz val="11"/><color theme="1"/><rFont val="Calibri"/><family val="2"/><scheme val="minor"/></rPr>
      <t xml:space="preserve"> </t>
    </r>
    <r>
      <rPr><sz val="11"/><color rgb="FFFF0000"/><rFont val="Calibri"/><family val="2"/><scheme val="minor"/></rPr>
      <t>text</t>
    </r>
    <r>
      <rPr><sz val="11"/><color theme="1"/><rFont val="Calibri"/><family val="2"/><scheme val="minor"/></rPr>
      <t xml:space="preserve"> is </t>
    </r>
    <r>
      <rPr><b/><i/><sz val="11"/><color theme="1"/><rFont val="Calibri"/><family val="2"/><scheme val="minor"/></rPr>
      <t>formatted</t>
    </r>
    <r>
      <rPr><sz val="11"/><color theme="1"/><rFont val="Calibri"/><family val="2"/><scheme val="minor"/></rPr>
      <t xml:space="preserve"> </t>
    </r>
    <r>
      <rPr><sz val="11"/><color rgb="FF00B050"/><rFont val="Calibri"/><family val="2"/><scheme val="minor"/></rPr>
      <t>several</t>
    </r>
    <r>
      <rPr><sz val="11"/><color theme="1"/><rFont val="Calibri"/><family val="2"/><scheme val="minor"/></rPr>
      <t xml:space="preserve"> </t>
    </r>
    <r>
      <rPr><sz val="11"/><color theme="1"/><rFont val="Tahoma"/><family val="2"/></rPr>
      <t>different</t>
    </r>
    <r>
      <rPr><sz val="11"/><color theme="1"/><rFont val="Calibri"/><family val="2"/><scheme val="minor"/></rPr>
      <t xml:space="preserve"> </t>
    </r>
    <r>
      <rPr><sz val="16"/><color theme="1"/><rFont val="Calibri"/><family val="2"/><scheme val="minor"/></rPr>
      <t>ways.</t>
    </r>
  </si>
</sst>

Context

StackExchange Code Review Q#139079, answer score: 3

Revisions (0)

No revisions yet.