in excel VBA, how to retrieve the formats of text

Question

In Excel VBA, I want to retrieve the text of a cell together with the format of each word. For example, Cell A1 has value "sample text". The Range("A1").Value property only returns the plain text (i.e. "sample text"). What I want is an object that gives me something like "< i > sample < /i > < b > text < /b >". What is that object in Excel DOM?

Answer 1

You can do so by examining Font of Characters, one by one, and opening/closing formatting tags in your output accordingly:

dim i as long
for i=1 to activecell.characters.count
  with activecell.characters(i,1).font
    if .bold then
      'open <b>, if not already opened
    else
      'close <b>, if not already closed
    end if

    if .italic then
      'open <i>, if not already opened
    else
      'close <i>, if not already closed
    end if

    ' etc
  end with
next