Mac 上的 VBA (Excel) 词典？

VBA (Excel) Dictionary on Mac?

提问by user1205577

I have an Excel VBA project that makes heavy use of Windows Scripting Dictionaryobjects. I recently had a user attempt to use it on a Mac and received the following error:

我有一个 Excel VBA 项目，它大量使用 Windows Scripting Dictionary对象。我最近有一个用户尝试在 Mac 上使用它并收到以下错误：

Compile Error: Can't find project or library

Which is the result of using the Tools > References > Microsoft Scripting Runtimelibrary.

这是使用Tools > References > Microsoft Scripting Runtime库的结果。

My question is, is there a way to make this work on a Mac?

我的问题是，有没有办法在 Mac 上完成这项工作？

The following are the 3 cases I can think of as being possible solutions:

以下是我认为可能的解决方案的 3 种情况：

1. Use a Mac plugin that enables use of Dictionaries on Macs (my favorite option if one exists)

2. Do some kind of variable switch like the following:

   isMac = CheckIfMac
   If isMac Then
       ' Change dictionary variable to some other data type that is Mac friendly and provides the same functionality 
   End If
   

3. Write 2 completely separate routines to do the same thing (please let this not be what needs to happen):

   isMac = CheckIfMac
   If isMac Then
       DoTheMacRoutine
   Else
      DoTheWindowsRoutine
   End If
   

1. 使用可以在 Mac 上使用词典的 Mac 插件（如果存在的话，我最喜欢的选项）

2. 做一些像下面这样的变量开关：

   isMac = CheckIfMac
   If isMac Then
       ' Change dictionary variable to some other data type that is Mac friendly and provides the same functionality 
   End If
   

3. 编写 2 个完全独立的例程来做同样的事情（请不要让这成为需要发生的事情）：

   isMac = CheckIfMac
   If isMac Then
       DoTheMacRoutine
   Else
      DoTheWindowsRoutine
   End If
   

采纳答案by Frobbit

Pulling the Answer from the comments to prevent link rot.

从评论中提取答案以防止链接腐烂。

Patrick O'Beirne @ sysmodwrote a class set that addresses this issue.

Patrick O'Beirne @ sysmod写了一个类集来解决这个问题。

Be sure to stop by Patirk's Blog to say thanks! Also there is a chance he has a newer version.

请务必访问 Patirk 的博客以表示感谢！他也有可能有更新的版本。

将此保存为名为 KeyValuePair.cls 的纯文本文件并导入 Excel

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "KeyValuePair"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit
'Unrestricted class just to hold pairs of values together and permit Dictionary object updating
Public Key As String
Public value As Variant

将此保存为名为 Dictionary.cls 的纯文本文件并导入到 excel

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "Dictionary"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit

'Collection methods: Add, Count, Item, Remove
'Dictionary : .Add(Key as string, Item as variant), .CompareMode, .Count, .Exists(Key); _
   .Item(Key) - writeable, .Items, .Keys, .Remove(Key), .RemoveAll
'plus KeyValuePairs collection, KeyValuePair(Index as long), Tag as variant
' 25-11-2011 KeyValuePair helper object

Public KeyValuePairs As Collection ' open access but allows iteration
Public Tag As Variant            ' read/write unrestricted

Private Sub Class_Initialize()
   Set KeyValuePairs = New Collection
End Sub

Private Sub Class_Terminate()
   Set KeyValuePairs = Nothing
End Sub

' in Scripting.Dictionary this is writeable, here we have only vbtextCompare because we are using a Collection
Public Property Get CompareMode() As VbCompareMethod
   CompareMode = vbTextCompare   '=1; vbBinaryCompare=0
End Property

Public Property Let Item(Key As String, Item As Variant)    ' dic.Item(Key) = value ' update a scalar value for an existing key
   Let KeyValuePairs.Item(Key).value = Item
End Property

Public Property Set Item(Key As String, Item As Variant)    ' Set dic.Item(Key) = value ' update an object value for an existing key
   Set KeyValuePairs.Item(Key).value = Item
End Property

Public Property Get Item(Key As String) As Variant
   AssignVariable Item, KeyValuePairs.Item(Key).value
End Property

' Collection parameter order is Add(Item,Key); Dictionary is Add(Key,Item) so always used named arguments
Public Sub Add(Key As String, Item As Variant)
   Dim oKVP As KeyValuePair
   Set oKVP = New KeyValuePair
   oKVP.Key = Key
   If IsObject(Item) Then
      Set oKVP.value = Item
   Else
      Let oKVP.value = Item
   End If
   KeyValuePairs.Add Item:=oKVP, Key:=Key
End Sub

Public Property Get Exists(Key As String) As Boolean
   On Error Resume Next
   Exists = TypeName(KeyValuePairs.Item(Key)) > ""  ' we can have blank key, empty item
End Property

Public Sub Remove(Key As String)
   'show error if not there rather than On Error Resume Next
   KeyValuePairs.Remove Key
End Sub

Public Sub RemoveAll()
   Set KeyValuePairs = Nothing
   Set KeyValuePairs = New Collection
End Sub

Public Property Get Count() As Long
   Count = KeyValuePairs.Count
End Property

Public Property Get Items() As Variant     ' for compatibility with Scripting.Dictionary
Dim vlist As Variant, i As Long
If Me.Count > 0 Then
   ReDim vlist(0 To Me.Count - 1) ' to get a 0-based array same as scripting.dictionary
   For i = LBound(vlist) To UBound(vlist)
      AssignVariable vlist(i), KeyValuePairs.Item(1 + i).value ' could be scalar or array or object
   Next i
   Items = vlist
End If
End Property

Public Property Get Keys() As String()
Dim vlist() As String, i As Long
If Me.Count > 0 Then
   ReDim vlist(0 To Me.Count - 1)
   For i = LBound(vlist) To UBound(vlist)
      vlist(i) = KeyValuePairs.Item(1 + i).Key   '
   Next i
   Keys = vlist
End If
End Property

Public Property Get KeyValuePair(Index As Long) As Variant  ' returns KeyValuePair object
    Set KeyValuePair = KeyValuePairs.Item(1 + Index)            ' collections are 1-based
End Property

Private Sub AssignVariable(variable As Variant, value As Variant)
   If IsObject(value) Then
      Set variable = value
   Else
      Let variable = value
   End If
End Sub

Public Sub DebugPrint()
   Dim lItem As Long, lIndex As Long, vItem As Variant, oKVP As KeyValuePair
   lItem = 0
   For Each oKVP In KeyValuePairs
      lItem = lItem + 1
      Debug.Print lItem; oKVP.Key; " "; TypeName(oKVP.value);
      If InStr(1, TypeName(oKVP.value), "()") > 0 Then
         vItem = oKVP.value
         Debug.Print "("; CStr(LBound(vItem)); " to "; CStr(UBound(vItem)); ")";
         For lIndex = LBound(vItem) To UBound(vItem)
            Debug.Print " (" & CStr(lIndex) & ")"; TypeName(vItem(lIndex)); "="; vItem(lIndex);
         Next
         Debug.Print
      Else
         Debug.Print "="; oKVP.value
      End If
   Next
End Sub

'NB VBA Collection object index is 1-based, scripting.dictionary items array is 0-based
'cf Scripting.Dictionary Methods s.Add(Key, Item), s.CompareMode, s.Count, s.Exists(Key); _
   s.Item(Key) - updateable, s.Items, s.Key(Key), s.Keys, s.Remove(Key), s.RemoveAll
'Scripting.Dictionary has no index number; you can index the 0-based variant array of Items returned
'  unlike Collections which can be indexed starting at 1
'Efficient iteration is For Each varPair in thisdic.KeyValuePairs
'Another difference I introduce is that in a scripting.dictionary, the doc says
'  If key is not found when changing an item, a new key is created with the specified newitem.
'  If key is not found when attempting to return an existing item, a new key is created and its corresponding item is left empty.
'but I want to raise an error when addressing a key that does not exist
'similarly, the scripting.dictionary will create separate integer and string keys for eg 2

回答by Alexander Poleschuk

Patirk's implementation doesn't work for MS Office 2016 on Mac. I made use of the implementation by Tim Hall. Here is the link: https://github.com/VBA-tools/VBA-Dictionary

Patirk 的实现不适用于 Mac 上的 MS Office 2016。我利用了 Tim Hall 的实现。这是链接：https: //github.com/VBA-tools/VBA-Dictionary

Also import of cls files into Excel doesn't work in MS Office 2016 on Mac as of September 2017. So I had to create a class module and to copy and paste the contents of Dictionary.cls manually in that module while removing meta info from Dictionary.cls such as VERSION 1.0 CLASS, BEGIN, END, Attribute.

此外，截至 2017 年 9 月，将 cls 文件导入 Excel 在 Mac 上的 MS Office 2016 中不起作用。因此，我必须创建一个类模块并手动复制并粘贴 Dictionary.cls 的内容到该模块中，同时从中删除元信息Dictionary.cls 如VERSION 1.0 CLASS, BEGIN, END, Attribute。

回答by sysmod

I have at last updated the files for Excel 2016 for Mac. http://www.sysmod.com/Dictionary.zip(capital D in Dictionary)

我终于更新了 Excel 2016 for Mac 的文件。 http://www.sysmod.com/Dictionary.zip（字典中的大写 D）

Unzip this and import the class files (tested in Excel 2016 for Mac 16.13 Build 424, 27-Apr-2018)

解压并导入类文件（在 Excel 2016 for Mac 16.13 Build 424 中测试，2018 年 4 月 27 日）

My bug report to MS is at answers.microsoft.com Excel 16.13 for Mac User Defined Class passed as parameter all properties are Null

我向 MS 提交的错误报告位于 answers.microsoft.com Excel 16.13 for Mac User Defined Class 作为参数传递所有属性均为 Null

Let me know if I've missed anything else! Good luck,

如果我错过了其他任何东西，请告诉我！祝你好运，

Patrick O'Beirne

帕特里克·奥贝恩