Originally, back in 2015, I explored retrieving the font listing by automating MS Word and pulling the list from there. You can review that code by reading my post VBA – Enumerate Fonts.
Recently, I revisited that approach and provided the means to get the font list via the use of APIs, so no external apps required. You can read all the details of that approach by reviewing VBA – List Fonts.
As part of my continued exploration of PowerShell, I decided to see if it could be used in any way to simplify retrieving a list of system fonts.
PowerShell to the Rescue, Again!
The beauty of PowerShell is that it enables us to tap into many different coding languages/libraries … As such, thanks to PowerShell, we can tap into the power of .NET and its’ InstalledFontCollection class and the job is basically done for us!
So once we know that, like every other PowerShell example, it is simply a question of writing a simple wrapper function et voilà!
'---------------------------------------------------------------------------------------
' Procedure : PS_GetFonts
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Get an array of the system fonts using PowerShell, Faster, No Apis, ...
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Requires a copy of the PS_GetOutput() function
'
' Usage:
' ~~~~~~
' aFonts() = PS_GetFonts()
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2021-10-13 Initial Release
'---------------------------------------------------------------------------------------
Function PS_GetFonts() As Variant
On Error GoTo Error_Handler
Dim sCmd As String
Dim sItems As String
'Build the necessary PowerShell Command
sCmd = "function PSGetFonts{" & vbCrLf & _
" Add-Type -AssemblyName " & Chr(34) & "System.Drawing" & Chr(34) & vbCrLf & _
" $items = (New-Object System.Drawing.Text.InstalledFontCollection).Families | Sort-Object -Property Name" & vbCrLf & _
" $combined = $items |" & vbCrLf & _
" ForEach-Object { $_.Name }" & vbCrLf & _
" $result = $combined -join ','" & vbCrLf & _
" return $result" & vbCrLf & _
"}" & vbCrLf & _
"PSGetFonts"
'Execute the command and return the output which should be a csv string
sItems = PS_GetOutput(sCmd)
' 'Break the returned csv string into an array
PS_GetFonts = Split(sItems, ",")
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: PS_GetFonts" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
To use it, you would do something along the lines of:
Public Sub ListFonts()
'Output the list of fonts to the VBA immediate window
Dim aFonts() As String
Dim vFont As Variant
Dim i As Long
aFonts() = PS_GetFonts()
For Each vFont In aFonts
i = i + 1
Debug.Print i, vFont
Next vFont
End Sub
The beauty with this approach is that there is no external automation, no APIs, … It uses plain VBA with a simple PowerShell call which doesn’t require any elevated permission and it is fast too. Doesn’t get much better than this!
As with my other examples PowerShell examples, this function requires a copy of my PS_GetOutput() function found in my VBA – Run PowerShell Command article.
I truly hope these recent articles entice some of you to further explore the awesome power of PowerShell.