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