If you need to get a list of all installed fonts, you'll find that Excel does not provide a direct way to retrieve that information. One approach is to read the font names from the Font control on the Formatting toolbar.
The following procedure displays a list of the installed fonts in column A of the active worksheet. It uses the FindControl method to locate the Font control on the Formatting toolbar. If this control is not found (for example, it was removed by the user), a temporary CommandBar is created, and the Font control is added to it.
Refer to Chapter 22 for more information about working with CommandBar controls.
Dim FontList As CommandBarControl Dim TempBar As CommandBar Dim i As Integer
Set FontList = Application.CommandBars("Formatting"). FindControl(ID:=1728)
' If Font control is missing, create a temp CommandBar If FontList Is Nothing Then
Set TempBar = Application.CommandBars.Add Set FontList = TempBar.Controls.Add(ID:=17 28) End If
' Put the fonts into column A Range("A:A").ClearContents For i = 0 To FontList.ListCount - 1
' Delete temp CommandBar if it exists On Error Resume Next TempBar.Delete End Sub
As an option,you can display each font name using the actual font.To do so, add this statement inside of the For-Next loop: Cells(i+1,1).Font.Name = FontList.List(i+1)
Be aware, however, that using many fonts in a workbook can eat up lots of system resources, and it could even crash your system.
Was this article helpful?