VBA IDs for Command Bar Objects in Excel for Mac

Question

Good morning.

The short Excel VBA code below, written on a Windows PC, won’t run on Excel for Mac.  A very small program that takes a word or phrase from a cell on a worksheet and then returns examples that phrase in all available fonts to rows in the worksheet.

I’m getting:  Run-time error ’91’: Object variable or With block variable not set.

Debug takes me to this line:  For I = 1 to cbc.ListCount.

A prior line defines cbc: Set cbc = Application.CommandBars.FindControl(ID:=1728)

I suspect that the “(ID:=1728)”, which I think identifies the font selector on the command bar in Windows Excel may need to be different on Mac Excel. But I haven’t been able to find anything on point in my searches.

I'm using a MacBook Pro 14" w/ M1 chip, 32Gb RAM.

The entire code is pretty short, so here it is:

' 18 September 2021

' Macro to display a phrase in all available fonts.

Sub ShowTheFonts()

Application.ScreenUpdating = False

Dim cbc As CommandBarControl, i%

Dim InputPhrase As String

Set cbc = Application.CommandBars.FindControl(ID:=1728)

InputPhrase = Range("InputPhrase").Value

For i = 1 To cbc.ListCount

    With Cells(i + 6, 1)

        .Value = cbc.List(i)

    End With

    With Cells(i + 6, 2)

        .Formula = "=InputPhrase"

        .Font.Name = cbc.List(i)

    End With

Next i

Range("B6").Value = i & " fonts"

Columns("B:B").AutoFit

Range("A7").CurrentRegion.Rows.AutoFit

Set cbc = Nothing

Application.ScreenUpdating = True

End Sub

Any help appreciated.

Answer 1

The last time CommandBars worked was in Office 2011. What version of Excel are you programming for?

If you want to use Ribbon commands, please see: https://www.macexcel.com/examples/ribbonpages/

Answer 2

I'm using Office 365. The Excel Version shows as 16.66.1

MacOS Catalina 10.15.7

The goal is to access to the list of fonts available to Excel on my system. I would love to be able to query a collection of the fonts. And it seems that the originator of this thread, rinkster, would like that too.

Answer 3

I too am trying to solve this issue of getting a list of Excel fonts on my iMac. I have had the same error at the ListCount or at the List(#) command.

I saw in my online searching that someone stated that the fonts in Word and Excel should be coming from the same source, so they should be identical. (From my results I question if that is indeed accurate.) I tried to call Word from Excel and list the fonts from Word and set the InputPhrase to the font. Of the 1500+ fonts, the first thousand or so of fonts were fine but the last five hundred threw an error, "Unable to set the Name property of the Font class." I also had a kludgy running of the routine, but somewhat ran the routine to completion. (I don't like sharing routines with the world that do not run properly, but for the sake of possibly helping out a fellow software person, I will share this.)

Here is the code:

Sub listWordFonts()

Dim wd As Object

'This statement caused an error but after selecting

'a blank Word template, debug, and run

'it continued succefully

Set wd = CreateObject("Word.Application")

Dim InputPhrase As String

InputPhrase = Range("InputPhrase").Value

Dim fontIndex As Long

For fontIndex = 1 To wd.FontNames.Count

'Write index value

With Cells(fontIndex + 6, 1)

  .Value = fontIndex

End With

'Write Font name

With Cells(fontIndex + 6, 2)

  .Value = wd.FontNames.Item(fontIndex)

End With

'Write the input phrase in the specified font, ...

'if possible

With Cells(fontIndex + 6, 3)

  .Formula = "=InputPhrase"

  On Error Resume Next

    .Font.name = wd.FontNames.Item(fontIndex)

    If Err.Number <> 0 Then

      'If unable to display the input phrase in

      'the specified font, display the error message

    Cells(fontIndex + 6, 4) = Error

  End If

  On Error GoTo 0

End With

Next fontIndex

With Cells(6, 3)

.Value = wd.FontNames.Count & " fonts"

With .CurrentRegion

  .Columns.AutoFit

  .Rows.AutoFit

End With

End With

wd.Quit

Set wd = Nothing

End Sub

As to the kludgy running of the routine:

1. When I ran it I had to have Word not already running.
2. I get an error at the line where I create the Word object
3. But I can successfully continue if:
   1. I select the blank Word template
   2. Select Debug at the error notification
   3. Select Run > Continue

I'm not at all convinced that Word and Excel share the same fonts, but here it is for-what-its-worth. If this helps you, great; if not we both wait for Microsoft to provide Excel with a viewable font list object (or set of objects).

Answer 4

Thank you, Andreas Killer. 

Unfortunately, your code offering did not fix the problem, but it did spur me to think about things differently.  In the end, what I’ve discovered is that it seems the combobox (or listbox) object used for font selection on the command bar…Application.CommandBars.FindControl(ID:=1728)…does not support the ListCount property in Excel for Mac.  It supports lots of other properties and methods, but not that one.  I didn’t see any of the other properties or methods that looked like they would serve the purpose of getting a ListCount or looping through each item in the list to do something with it.

An example of Excel for Mac being a bit “behind” Excel for Windows, I suppose.

So I still have the question: how can I input a phrase and have Excel list that phrase repeatedly in a column, one row for each font available with the font applied? All examples I've found online are written for Excel for Windows, and will not work in Excel for MAC.

Answer 5

I don't have a Mac, but IMHO it should be possible to "detect" the ID.

On a Windows machine the default font is Calibri so if we loop trough all cbc we can check if the Text property contains this font name and show the ID.

Sub FindID()
Dim cb As CommandBar
Dim cbc As CommandBarControl
On Error GoTo Errorhandler
For Each cb In Application.CommandBars
For Each cbc In cb.Controls
If cbc.Type = msoControlComboBox Then
If cbc.Text = "Calibri" Then
Debug.Print cbc.ID
End If
End If
NextCBC:
Next
Next
Exit Sub
Errorhandler:
Resume NextCBC
End Sub

After you have the ID for the Mac you can modify your code with a conditional define, example

#If Mac Then
Const FontID = 1234
#Else
Const FontID = 1728
#End If

Set cbc = Application.CommandBars.FindControl(ID:=FontID)

and your code is compatible to Mac and Windows.

Andreas.