How to get a list of a cell's font values in Excel using VBA

Question

Is it possible to get a list of a cell's attributes in excel using VBA? I want to find out if a cell is bold, underlined, in italics, etc. So far all the suggestions i've found on the internet suggests i check if the value is true. For example, in the code below i'm verifying if the cell is bold:

Sub format_cells()
    Dim ws As Worksheet
    Set ws = Sheets("Uploads")

    rc = ws.UsedRange.Rows.Count

    For i = 2 To rc
        If ws.Cells(i, 10).Font.Bold = True Then
            Debug.Print "Pass"
        End If
    Next

End Sub

I'd rather not have to create a conditional or case statement to check for every possibility. I'd rather just be able to get the properties with one line of code.

For example, in Python I can accomplish this using the Openpyxl library:

from openpyxl import load_workbook

wb = load_workbook("Book1.xlsx")
ws = wb["Uploads"]

ex = ws.cell(2,10)
print(ex.font)

I would then get the following output:

<openpyxl.styles.fonts.Font object>
Parameters:
name='Calibri', charset=None, family=2.0, b=False, i=False, strike=None, outline=None, shadow=None, condense=None, color=<openpyxl.styles.colors.Color object>
Parameters:
rgb=None, indexed=None, auto=None, theme=1, tint=0.0, type='theme', extend=None, sz=11.0, u=None, vertAlign=None, scheme='minor'

But, if i tried doing that in VBA I get a "Run-time error '424' Object required"

Sub test()
    Dim ws As Worksheet
    Set ws = Sheets("Uploads")

    ex = ws.Range("J3")
    Debug.Print ex.Font

End Sub

Thank you.

For the example you show that looks like 5 mins of coding in VBA? — Tim Williams, Jan 16 '20 at 19:00
Thank you, @Warcupine. if i understand your statement, you mean i should write something like this: "Dim ex As Range Set ex = ws.Range("J2") Debug.Print ex.Font.Bold". So that still means i have to specify each and every possibility. — Robert, Jan 16 '20 at 19:01
You have to specify every possibility - yes, there's no alternative in the Excel Object Model. — BigBen, Jan 16 '20 at 19:05
`ex = ws.Range("J3")` is a value assignment, so `ex` is holding the *value* of J3, and you can't make a member call against a value (hence "object required"). If you want `ex` to be a `Range` object, you need a *reference* assignment, with the `Set` keyword. Note that `ex` is also undeclared, which means `Option Explicit` is missing from your module, ...which means any typo will merrily run and throw that kind of run-time errors. Use `Option Explicit` ...always. — Mathieu Guindon, Jan 16 '20 at 19:10

score 8 · Accepted Answer · answered Jan 16 '20 at 19:10

8

For example:

Debug.Print FormatString(Range("A1"))


Function FormatString(c As Range)
    Dim f, e
    With c
        For Each e In Array("Bold", "Italic", "Underline", "Color", _
                            "ColorIndex", "Superscript", "subscript")
            f = f & e & "=" & CallByName(.Font, e, VbGet) & ";"
        Next e
    End With
    FormatString = f
End Function

answered Jan 16 '20 at 19:10

Tim Williams

154,628
8
97
125

3

Clever use of `CallByName` here. Note that arrays should normally be iterated with a `For...Next` loop. Won't make much of a difference for that few elements though. – Mathieu Guindon Jan 16 '20 at 19:13
1

See also: https://stackoverflow.com/questions/14305750/list-object-methods-and-properties – Tim Williams Jan 16 '20 at 19:50

How to get a list of a cell's font values in Excel using VBA

1 Answers1