Run-time error '458' after migrating to 64-bit Office
aprykhus opened this issue · 1 comments
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
- Launch Excel
- Alt+F11
- Import Modules
- JsonConverter.bas
- Dictionary.cls
- Insert Module
- 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
- 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
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.
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