vba ActiveX 组件无法创建对象 --- Excel for Mac

ActiveX component can’t create object --- Excel for Mac

提问by user3704103

I'm trying to get an Excel 2011 32-bit (for Mac) spreadsheet working that contains a macro. The problem is that this macro works fine on a PC, but not on the Mac. I tried to import Tim Hall's Dictionary.cls, but it still doesn't work. Same thing for KeyValuePair.cls.

我正在尝试使用包含宏的 Excel 2011 32 位（适用于 Mac）电子表格。问题是这个宏在 PC 上可以正常工作，但在 Mac 上不行。我尝试导入 Tim Hall 的 Dictionary.cls，但它仍然不起作用。KeyValuePair.cls 也是一样。

Error: Run-time error '429' ActiveX component can't create object

错误：运行时错误“429”ActiveX 组件无法创建对象

I'm not a programmer, so the problem is probably me, not knowing what to change to get things working. It's probably super easy for those who know what they are doing. Can anyone spend a few minutes looking at the files and tell me which parts I need to change to get this running? [I assume it does work…]

我不是程序员，所以问题可能是我，不知道要改变什么才能使事情正常工作。对于那些知道自己在做什么的人来说，这可能非常容易。任何人都可以花几分钟查看文件并告诉我需要更改哪些部分才能运行它吗？[我认为它确实有效......]

FWIW, I have tried to replace “Scripting.Dictionary” with “New.Dictionary” in two places (see below), but that didn't get it working.

FWIW，我试图在两个地方用“New.Dictionary”替换“Scripting.Dictionary”（见下文），但这并没有让它工作。

Set dAttributes = CreateObject("New.Dictionary")

Set dValues = CreateObject("New.Dictionary”)

RandomiseData file:

随机数据文件：

Option Explicit
Sub GenerateResults()

Dim LO As ListObject
Dim LO2 As ListObject
Dim LR As ListRow
Dim ws As Worksheet
Dim cCount As Integer
Dim gCount As Integer
Dim dAttributes As Object
Dim dValues As Object
Dim dKey As Variant
Dim c As Range
Dim v As Variant
Dim i As Integer
Dim InsertCount As Integer

Set LO = ActiveSheet.ListObjects("Data")
If LO Is Nothing Then MsgBox "Please select the table and re-run": Exit Sub
With Application
.EnableEvents = False
.DisplayAlerts = False
.ScreenUpdating = False
End With
LO.AutoFilter.ShowAllData

Set ws = ActiveWorkbook.Sheets.Add
ws.Range("A1:C1").Value = Array("Candidate", "Attribute", "Value")
ws.ListObjects.Add xlSrcRange, Range("A1:C1"), , xlYes
Set LO2 = ws.Range("A1").ListObject

Set dAttributes = CreateObject(“New.Dictionary")
For Each c In LO.ListColumns("Attribute").DataBodyRange.Cells
If Not dAttributes.Exists(c.Value) Then dAttributes(c.Value) = c.Value
Next c

For Each dKey In dAttributes.Keys
LO.Range.AutoFilter Field:=LO.ListColumns("Attribute").Index,    Criteria1:=dKey
gCount = Evaluate("SUM(--(FREQUENCY(IF(" & LO.Name & "[Attribute]=""" & dKey & """,MATCH(" & LO.Name & "[Value]," & LO.Name & "[Value],0)),ROW(" & LO.Name & "[Value])-ROW(" & LO.Name & "[[#Headers],[Value]]))>0))")
cCount = Evaluate("SUM(--(FREQUENCY(IF(" & LO.Name & "[Attribute]=""" & dKey & """,MATCH(" & LO.Name & "[Candidate]," & LO.Name & "[Candidate],0)),ROW(" & LO.Name & "[Candidate])-ROW(" & LO.Name & "[[#Headers],[Candidate]]))>0))")
v = GenerateSplit(cCount, gCount)
Set dValues = CreateObject(“New.Dictionary")

For Each c In LO.ListColumns("Value").DataBodyRange.SpecialCells(xlCellTypeVisible)
    If Not dValues.Exists(c.Value) Then dValues(c.Value) = c.Value
Next c

InsertCount = 0
i = 1
For Each c In LO.ListColumns("Candidate").DataBodyRange.SpecialCells(xlCellTypeVisible)
TryAgain:
If i <= v(InsertCount, 2) Then
    Set LR = LO2.ListRows.Add
    LR.Range.Value = Array(c.Value, dKey, dValues.Items()(InsertCount))
    i = i + 1
Else
    i = 1
    InsertCount = InsertCount + 1
    GoTo TryAgain
End If
Next c

Next dKey
LO.AutoFilter.ShowAllData
LO.Range.Worksheet.Select

With Application
.EnableEvents = True
.DisplayAlerts = True
.ScreenUpdating = True
End With

End Sub

EDITED CODE

编辑代码

Option Explicit
Sub GenerateResults()

Dim LO As ListObject
Dim LO2 As ListObject
Dim LR As ListRow
Dim ws As Worksheet
Dim cCount As Integer
Dim gCount As Integer
Dim dAttributes As Object
Dim dValues As Object
Dim dKey As Variant
Dim c As Range
Dim v As Variant
Dim i As Integer
Dim InsertCount As Integer

#If Mac Then
Set dAttributes = New Dictionary
Set dValues = New Dictionary
#Else
Set dAttributes = CreateObject("Scripting.Dictionary")
Set dValues = CreateObject("Scripting.Dictionary")
#End If

Set LO = ActiveSheet.ListObjects("Data")
If LO Is Nothing Then MsgBox "Please select the table and re-run": Exit Sub
With Application
.EnableEvents = False
.DisplayAlerts = False
.ScreenUpdating = False
End With
LO.AutoFilter.ShowAllData

Set ws = ActiveWorkbook.Sheets.Add
ws.Range("A1:C1").value = Array("Candidate", "Attribute", "Value")
ws.ListObjects.Add xlSrcRange, Range("A1:C1"), , xlYes
Set LO2 = ws.Range("A1").ListObject

' Set dAttributes = CreateObject("New Dictionary")
For Each c In LO.ListColumns("Attribute").DataBodyRange.Cells
If Not dAttributes.Exists(c.value) Then dAttributes(c.value) = c.value
Next c

For Each dKey In dAttributes.Keys
LO.Range.AutoFilter Field:=LO.ListColumns("Attribute").Index,    Criteria1:=dKey
gCount = Evaluate("SUM(--(FREQUENCY(IF(" & LO.Name & "[Attribute]=""" & dKey & """,MATCH(" & LO.Name & "[Value]," & LO.Name & "[Value],0)),ROW(" & LO.Name & "[Value])-ROW(" & LO.Name & "[[#Headers],[Value]]))>0))")
cCount = Evaluate("SUM(--(FREQUENCY(IF(" & LO.Name & "[Attribute]=""" & dKey & """,MATCH(" & LO.Name & "[Candidate]," & LO.Name & "[Candidate],0)),ROW(" & LO.Name & "[Candidate])-ROW(" & LO.Name & "[[#Headers],[Candidate]]))>0))")
v = GenerateSplit(cCount, gCount)
' Set dValues = CreateObject("Scripting.Dictionary")

For Each c In  LO.ListColumns("Value").DataBodyRange.SpecialCells(xlCellTypeVisible)
    If Not dValues.Exists(c.value) Then dValues(c.value) = c.value
Next c

InsertCount = 0
i = 1
For Each c In LO.ListColumns("Candidate").DataBodyRange.SpecialCells(xlCellTypeVisible)
TryAgain:
If i <= v(InsertCount, 2) Then
    Set LR = LO2.ListRows.Add
    LR.Range.value = Array(c.value, dKey, dValues.Items()(InsertCount))
    i = i + 1
Else
    i = 1
    InsertCount = InsertCount + 1
    GoTo TryAgain
End If
Next c

Next dKey
LO.AutoFilter.ShowAllData
LO.Range.Worksheet.Select

With Application
.EnableEvents = True
.DisplayAlerts = True
.ScreenUpdating = True
End With

End Sub

采纳答案by David Zemens

New.Dictionaryis not a valid classname and will fail on PC as well. Usually the construct using early binding would be:

New.Dictionary不是有效的类名，在 PC 上也会失败。通常使用早期绑定的构造是：

Set obj = New Dictionary

OR using late binding:

或使用后期绑定：

Set obj = CreateObject("Scripting.Dictionary")

However, Mac OS does not have the Scripting Runtime library, so none of those things will be available to you -- Dictionary, FileSystemObject, etc.

但是，Mac OS 没有 Scripting Runtime 库，因此您无法使用这些东西——Dictionary、FileSystemObject 等。

You'll need to use a Collection or other data type in lieu of Dictionary type, or you can borrow from this other answer and implement a custom dictionary-like Class.

您需要使用 Collection 或其他数据类型来代替 Dictionary 类型，或者您可以从其他答案中借用并实现一个自定义的类似字典的 Class。

I tried to import Tim Hall's Dictionary.cls, but it still doesn't work. Same thing for KeyValuePair.cls.

我尝试导入 Tim Hall 的 Dictionary.cls，但它仍然不起作用。KeyValuePair.cls 也是一样。

I suspect you simply didn't know that you alsoneed to use conditional compilationmethod to assign the Dictionaryclass on a Mac OS, and the Scripting.Dictionaryclass on Windows OS.

我怀疑您根本不知道您还需要使用条件编译方法Dictionary在 Mac OS 上分配类，Scripting.Dictionary在 Windows 操作系统上分配类。

Using Conditional Compilation on Mac/Windows

在 Mac/Windows 上使用条件编译

Remove both of these lines:

删除这两行：

Set dAttributes = CreateObject("New.Dictionary")
Set dValues = CreateObject("New.Dictionary")

They'll fail even in Windows, as I described above. Likewise, if you want to use this code in bothWin and Mac environments, you can't use Scripting.Dictionarywithout taking some additional precautions to avoid errors.

如上所述，它们甚至在 Windows 中也会失败。同样，如果你想使用此代码都赢和Mac环境中，您不能使用Scripting.Dictionary没有采取一些额外的预防措施，以避免错误。

You will need to implement conditional compilationusing compiler directivesto identify the OS. This is not overly complicated for anyone who's done it before, but it's not something that most beginners will even know is available to them, much less how to use it.

您将需要使用编译器指令来实现条件编译来识别操作系统。对于以前做过的人来说，这并不太复杂，但大多数初学者甚至不会知道他们可以使用它，更不用说如何使用它了。

In pseudo-code, basically you're doing this:

在伪代码中，基本上你是这样做的：

If the operating system is Mac, then:
    Do this
ElseIf the operating system is Win, then:
    Do that instead
End If

In your code, do something like this

在你的代码中，做这样的事情

Assuming you've copied the KeyValuePair.clsandDictionary.clscode from the other answer which implements the Dictionary replicain to plain text files, and imported both of the modules to your project's VBE.

假设您已将实现 Dictionary 副本的另一个答案中的KeyValuePair.cls和Dictionary.cls代码复制到纯文本文件中，并将这两个模块导入到您项目的 VBE 中。

#IF Mac Then
    Set dAttributes = New Dictionary
    Set dValues = New Dictionary
#Else
    Set dAttributes = CreateObject("Scripting.Dictionary")
    Set dValues = CreateObject("Scripting.Dictionary")
#End If

I would put this code just above the line:

我会将这段代码放在该行的正上方：

Set LO = ActiveSheet.ListObjects("Data")

Practically speaking, as long as you place that code anywhere beforeyou call on either of dAttributesor dValues, it doesn't matter where you put it.

实际上，只要在调用dAttributes或之前将该代码放置在任何位置，放置它的位置dValues都没有关系。

This should work on both operating systems, as the Dictionary.clsmimics the Scripting.Dictionary's methods.

这应该适用于两个操作系统，因为Dictionary.cls模仿了Scripting.Dictionary的方法。

NB: It's preferable to group these object assignments, rather than peppering them about haphazardly throughout the procedure, especiallywhen you're using conditional compilation as it will be more human-readable and easier to maintain moving forward.

NB：最好将这些对象分配分组，而不是在整个过程中随意地将它们添加进来，尤其是当您使用条件编译时，因为它更易于人类阅读并且更易于维护。

回答by sysmod

I see you said "I tried to import Tim Hall's Dictionary.cls, but it still doesn't work. Same thing for KeyValuePair.cls."

我看到你说“我试图导入 Tim Hall 的 Dictionary.cls，但它仍然不起作用。KeyValuePair.cls 也是如此。”

Tim Halls's 2016 Dictionary.cls is a complete replacement for Scripting.Dictionary and does not require KeyValuePair.cls which is a helper class I provided at https://sysmod.wordpress.com/2011/11/24/dictionary-vba-class-update/for my 2011 Dictionary.cls. Use either his class or my pair of classes, but not both.

Tim Halls 的 2016 Dictionary.cls 是 Scripting.Dictionary 的完全替代品，不需要 KeyValuePair.cls，这是我在https://sysmod.wordpress.com/2011/11/24/dictionary-vba-class提供的帮助类-update/用于我的 2011 Dictionary.cls。使用他的课程或我的一对课程，但不能同时使用。

The advice on conditional compilation is good for writing code that works in either Mac or PC. I would suggestthat if you have your own dictionary class you don't need the Windows Scripting.Dictionary at all. I think it's better to have one class under your control than two where they might deviate in some subtle way.

关于条件编译的建议适用于编写在 Mac 或 PC 上运行的代码。我建议如果您有自己的字典类，则根本不需要 Windows Scripting.Dictionary。我认为让一个班级在你的控制之下比两个班级可能以某种微妙的方式偏离要好。