Hello - first of all thanks for developing the IScriptingDictionary, I use a Mac and this seems to be the most feature rich alternative to MS's Scripting.Dictionary.
I run a Mac Pro with a Xenon processor 48GB OSX version Monterey 12.7.6.
I am testing the IScriptingDictionary on Excel as part of the Office 2021 for Home and Office suite.
The error I get is the MS Visual Basic Compile error "Method or data member not found", under #IF VBA7 - VT-LONGLONG = VBA.vbLongLong.
This is in the TypeSafeArray module.
I've attached a screenshot of this error and of the modules/classes loaded (I've imported all of the recommended classes/modules).
VBA Script is in the 2nd screenshot, repeated in text below. I created a table on a worksheet, using an array and attempting to rename a key. I'm keeping the amount of data small but will try a test of a 3k row table, if I get this runninng.
Please let me know if you want me to change anything in the script or amend any of the class/standard modules, I'll be happy to continue testing.
Thanks
Chas Bright
Test VBA Script:
Option Explicit
Sub UsingADictionary()
Dim myDictionary As IScriptingDictionary
Dim bItemExists As Boolean
Dim tblSales As ListObject
Dim arrData, arrReport, arrHeaders
Dim i As Long
Dim rng As Range
' Create the IScripting Directory object
Set myDictionary = Dictionary.Create
Set tblSales = Worksheets("Product Data Table").ListObjects("tblSales")
' Put the data into an array
arrData = tblSales.DataBodyRange
' Loop through the array
For i = 1 To UBound(arrData)
' If key exists add to it, else create and add to it
If myDictionary.Exists(arrData(i, 2)) Then
myDictionary.Item(arrData(i, 2)) = myDictionary.Item(arrData(i, 2)) + _
arrData(i, 5)
Else
myDictionary.Add arrData(i, 2), arrData(i, 5)
End If
Next i
' Rename a key to prove the exercise
' The only way to rename a key is to know the original name
myDictionary.Key("Tools") = "Electrical Tools"
'Set a location for summary 2 rows below the table
Set rng = tblSales.Range.Offset(tblSales.Range.Rows.Count + 2).Resize(1, 1)
' Put the dictionary keys and values into an array, then dump to the selected worksheet
arrHeaders = myDictionary.Keys
rng.Resize(myDictionary.Count, 1).Value = Application.Transpose(arrHeaders)
arrReport = myDictionary.Items
rng.Offset(, 1).Resize(myDictionary.Count, 1).Value = Application.Transpose(arrReport)
Set myDictionary = Nothing
Set tblSales = Nothing
Set rng = Nothing
Hello - first of all thanks for developing the IScriptingDictionary, I use a Mac and this seems to be the most feature rich alternative to MS's Scripting.Dictionary.
I run a Mac Pro with a Xenon processor 48GB OSX version Monterey 12.7.6.
I am testing the IScriptingDictionary on Excel as part of the Office 2021 for Home and Office suite.
The error I get is the MS Visual Basic Compile error "Method or data member not found", under #IF VBA7 - VT-LONGLONG = VBA.vbLongLong.
This is in the TypeSafeArray module.
I've attached a screenshot of this error and of the modules/classes loaded (I've imported all of the recommended classes/modules).
VBA Script is in the 2nd screenshot, repeated in text below. I created a table on a worksheet, using an array and attempting to rename a key. I'm keeping the amount of data small but will try a test of a 3k row table, if I get this runninng.
Please let me know if you want me to change anything in the script or amend any of the class/standard modules, I'll be happy to continue testing.
Thanks
Chas Bright
Test VBA Script:
Option Explicit
Sub UsingADictionary() Dim myDictionary As IScriptingDictionary Dim bItemExists As Boolean Dim tblSales As ListObject Dim arrData, arrReport, arrHeaders Dim i As Long Dim rng As Range
' Create the IScripting Directory object
Set myDictionary = Dictionary.Create Set tblSales = Worksheets("Product Data Table").ListObjects("tblSales")
' Put the data into an array arrData = tblSales.DataBodyRange
' Loop through the array For i = 1 To UBound(arrData) ' If key exists add to it, else create and add to it If myDictionary.Exists(arrData(i, 2)) Then myDictionary.Item(arrData(i, 2)) = myDictionary.Item(arrData(i, 2)) + _ arrData(i, 5) Else myDictionary.Add arrData(i, 2), arrData(i, 5) End If
Next i
' Rename a key to prove the exercise ' The only way to rename a key is to know the original name
myDictionary.Key("Tools") = "Electrical Tools"
'Set a location for summary 2 rows below the table Set rng = tblSales.Range.Offset(tblSales.Range.Rows.Count + 2).Resize(1, 1)
' Put the dictionary keys and values into an array, then dump to the selected worksheet arrHeaders = myDictionary.Keys rng.Resize(myDictionary.Count, 1).Value = Application.Transpose(arrHeaders) arrReport = myDictionary.Items rng.Offset(, 1).Resize(myDictionary.Count, 1).Value = Application.Transpose(arrReport) Set myDictionary = Nothing Set tblSales = Nothing Set rng = Nothing
End Sub