Function to Return Font Name

Question

I need a simple string function that will return the name of the font in the target cell.  So if A1 has been formatted as Arial Unicode MS, then =fontstr(A1) would display "Arial Unicode MS"

1. Volatility is not an issue
2. There is only a single font in each cell

I started out like:

Public Function fontstr(r As Range) As String

fonstr = ""

If r.Font.Name = "Arial" Then fontstr = "Arial"

End Function

I realized I would have to hardcode in all the fonts in the system using this approach.

Can anyone suggest a better approach??

---

gsnu201101

Answer 1

Here is, perhaps, a more complete UDF function which will report the empty string if the referenced cell has no text in it, the font name if all character's in the cell have the same font name and a semi-colon delimited list of all the font names in use if the cell's text is composed of characters with different (multiple) font names...

Function FontName(Rng As Range) As String

  Dim X As Long, FN As String

  If Rng.Count > 1 Then Exit Function

  If Len(Rng.Value) = 0 Then Exit Function

  FontName = Rng.Font.Name & ""

  If Len(FontName) Then Exit Function

  For X = 1 To Len(Rng.Value)

    FN = Rng.Characters(X, 1).Font.Name

    If InStr(FontName, FN) = 0 Then FontName = FontName & FN & "; "

  Next

  If InStr(FontName, "; ") Then FontName = Left(FontName, Len(FontName) - 2)

End Function

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

Answer 2

Hi,

Maybe this

Function FontStr(rng As Range) As String

FontStr = rng.Font.Name

End Function

---

If this post answers your question, please mark it as the Answer.

Mike H

Answer 3

Thanks!

---

gsnu201101

Answer 4

Thanks Mike!

---

gsnu201101

your welcome. I should have added that changing font doesn't trigger a calculation event but you could still include Application.volatile in the function.

---

If this post answers your question, please mark it as the Answer.

Mike H

Answer 5

Thanks Mike!

---

gsnu201101