Array List of Fonts?

I'm trying to do an arraylist of fonts but I keep getting the error Object variable or with block variable not set. here is my code:

    Dim formatArrayRight(7 To 60) As Font
    Dim act As Integer
    For act = 7 To 60
    formatArrayRight(act) = Range("M" & act).Font
    Next

Can you create Font arrays?

Hello Andrew,

What type of an array are you trying to create? A array of font names or something else?

Hello Andrew,

You can get those settings using the Font.FontStyle property which is a variant.

    Dim formatArrayRight(7 To 60) As Variant
    Dim act As Integer
    For act = 7 To 60
      formatArrayRight(act) = Range("M" & act).Font.FontStyle
    Next

ohhhh Thanks a Ton!!!

This doesn't take into account the font size. Will I need a seperate array for this?

Hello Andrew,

Glad I could help.

Hi Andrew,

One thing you could do is create a structure called a user defined type (UDT) to hold the relevant values you need. For example:

Type tFont
    Fontstyle As Variant
    Size As Variant
End Type

Sub foo()

    Dim formatArrayRight(7 To 60) As tFont
    Dim act As Long
     
    For act = 7 To 60
        With Range("M" & CStr(act)).Font
             formatArrayRight(act).Fontstyle = .Fontstyle
             formatArrayRight(act).Size = .Size
        End With
    Next act

End Sub

It's quite unusual to give an array a lower bound of 7 though. Usually programmers use 0 or 1.

By the way, the reason the code errored in your original post is because Font is an object, so you need to use the Set keyword:

    '(your original code)

    Dim formatArrayRight(7 To 60) As Font
    Dim act As Integer
    
    For act = 7 To 60
        Set formatArrayRight(act) = Range("M" & act).Font
    Next act

Hello Andrew,

You need to place the type declaration at the top of a standard VBA module before any Sub or Function definitions.