Run-time error '458' after migrating to 64-bit Office

[aprykhus]

Problem

After migrating from 32-bit to 64-bit Office, macro throws run-time error '458': Variable uses an Automation type not supported in Visual Basic.

Environment

Microsoft 365 64-bit Version 2210
Windows 11 22H2

Repro Steps

1. Launch Excel
2. Alt+F11
3. Import Modules

• JsonConverter.bas
• Dictionary.cls

4. Insert Module
5. Copy paste the following code into Module1

Sub BugRepro()
    Dim strJson As String
    strJson = "[{""artist"":""Ray Charles"",""title"":""Mess Around""},{""artist"":""Ratt"",""title"":""Lay It Down""}]"
    
    Dim json As Collection
    Set json = JsonConverter.ParseJson(strJson)
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Dim nRow As Long
    nRow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    Dim song As Variant
    
    For Each song In json
        ws.Cells(nRow, 1) = song("artist")
        ws.Cells(nRow, 2) = song("title")
        nRow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
    Next song
    
End Sub

6. Run Macro by pressing F5 key

Actual Results

Throws error
Microsoft Visual Basic

Run-time error '458':

Variable uses an Automation type not supported in Visual Basic

Expected Results

Sheet1 is populated with data

Workaround

Rename this line in Module1.BugRepro
Dim song As Variant
to
Dim song As Object
Changing the data type from Variant to Object works.

Debugging

When I click the Debug button of the error message and press F8 to step into the code it goes into line 65 of Dictionary class module
Public Property Get item(key As Variant) As Variant
Keep pressing F8 until it gets to line 86, End Property press F8 it throws the error.

[houghtonap]

In your Workaround is the answer to why you are experiencing what you are. The json variable is declared as Dim json as VBA.Collection which is an Object. The For Each causes json variable to be iterated over which would evaluate to another Object because the items in the json variable are JSON Objects which gets mapped to Scripting.Dictionary objects. This is where I suspect the issue is, that you have not declared a reference to the Microsoft Scripting Runtime scrrun.dll and it is complaining that it doesn't know about Scripting.Dictionary so it cannot instantiate the COM object.

Second your code in the For Each will not work. Scripting.Dictionary objects have a different interface than a VBA.Collection. You need to first check if the key is present before you access it's value, otherwise the Scripting.Dictionary object will create a new key with an empty value, per Microsoft's documentation. So your For Each becomes:

    Dim song as Scripting.Dictionary ' Add reference, VBA Editor Tools->Reference, search for Microsoft Scripting Runtime.
    
    For Each song In json
        ws.Cells(nRow, 1) = VBA.IIF( song.Exists("artist"), song.Item("artist"), VBA.CVErr(Excel.XlCVError.xlErrValue))
        ws.Cells(nRow, 2) = VBA.IIF( song.Exists("title"), song.Item("title"), VBA.CVErr(Excel.XlCVError.xlErrValue))
        nRow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
    Next song

Lastly you might want to consider making the area in your worksheet a table, then use the Excel ListObject interface. It is so much easier to deal with tables in VBA code and the Excel UI, rather than keeping track of column references for your data elements. Not to mention maintenance when you change the position of the column, now your code and/or formulas are broken. Certainly a personal preference.

Option Explicit


Sub BugRepro()
  
  Dim strJson As String
  
  Dim json As VBA.Collection
  Dim song As Scripting.Dictionary
  
  Dim ws As Worksheet
  Dim tbl As ListObject
  Dim col As ListColumns
  Dim row As ListRow
  
  
  strJson = "[{""artist"":""Ray Charles"",""title"":""Mess Around""},{""artist"":""Ratt"",""title"":""Lay It Down""}]"
  
  Set json = JsonConverter.ParseJson(strJson)
  
  Set ws = ThisWorkbook.Sheets("Sheet1")
  Set tbl = ws.ListObjects("Table1")
  Set col = tbl.ListColumns
  
  For Each song In json
    Set row = tbl.ListRows.Add
    row.Range(, col("Artist").Index) = VBA.IIf(song.Exists("artist"), song.Item("artist"), VBA.CVErr(Excel.XlCVError.xlErrValue))
    row.Range(, col("Title").Index) = VBA.IIf(song.Exists("title"), song.Item("title"), VBA.CVErr(Excel.XlCVError.xlErrValue))
  Next song
  
  Exit Sub
End Sub