VBA on Mac issue

Question

I’m trying to automate a simple task in Excel using VBA on macOS, but the macro doesn’t behave the same way as it does on Windows. Specifically, code that relies on Windows-only objects (such as Scripting.Dictionary or file system calls) either throws errors or silently fails on Mac. I’m looking for the correct way to write cross-platform VBA that works reliably on Excel for macOS.

Im using:

Sub CountItems()

Dim dict As Object

Set dict = CreateObject("Scripting.Dictionary")

dict.Add "Apple", 1

dict.Add "Orange", 2

MsgBox "Total items: " & dict.Count
```End Sub

Issue on macOS:

CreateObject("Scripting.Dictionary") fails because the Windows Scripting Runtime is not available on Excel for Mac.  
  
---  
Mod moved from :  Microsoft 365 and Office | Excel | For business | MacOS

Answer 1

Dear Santek Brenner

Thank you for posting your question in the Microsoft Q&A forum.

To make your VBA code work cross‑platform (Mac & Windows) without complex workarounds, you can simply replace the Scripting.Dictionary with the native VBA Collection object.

Please test the following code and let me know if it meets your requirements:

Sub CountItems()
    ' Use "New Collection" instead of CreateObject to work on Mac
    Dim coll As New Collection
    ' IMPORTANT: Collection.Add syntax is reversed compared to Dictionary
    ' Dictionary: .Add Key, Value
    ' Collection: .Add Value, Key
    coll.Add 1, "Apple"
    coll.Add 2, "Orange"
    MsgBox "Total items: " & coll.Count
End Sub

If this solution doesn’t fully address your needs, please share your feedback so I can assist further.

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.