ActiveX组件无法创建对象--- Mac版Excel [英] ActiveX component can’t create object --- Excel for Mac

问题描述

我正在尝试使用包含宏的Excel 2011 32位(对于Mac)电子表格.问题在于此宏在PC上工作正常，但在Mac上却无法正常工作.我尝试导入Tim Hall的Dictionary.cls，但仍然无法正常运行.对于KeyValuePair.cls也是如此.

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.

错误:运行时错误"429" ActiveX组件无法创建对象

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

我不是程序员，所以问题可能出在我身上，不知道要做什么才能使工作正常.对于那些知道自己在做什么的人来说，这可能超级容易.谁能花一些时间查看文件并告诉我我需要更改哪些部分才能运行此文件? [我认为它确实有效...]

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，我曾尝试在两个地方用"New.Dictionary"替换"Scripting.Dictionary"(请参见下文)，但这没有用.

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

Set dAttributes = CreateObject("New.Dictionary")

Set dValues = CreateObject("New.Dictionary")

RandomiseData文件:

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

推荐答案

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

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

Set obj = New Dictionary

或使用后期绑定:

Set obj = CreateObject("Scripting.Dictionary")

但是， Mac OS可以没有Scripting Runtime librar y(脚本运行时库) y，因此您无法使用其中的任何内容-字典，FileSystemObject等.

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

您将需要使用Collection或其他数据类型来代替Dictionary类型，或者您可以从

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.

我尝试导入Tim Hall的Dictionary.cls，但仍然无法正常运行.对于KeyValuePair.cls也是如此.

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

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

I suspect you simply didn't know that you also need to use conditional compilation method to assign the Dictionary class on a Mac OS, and the Scripting.Dictionary class on Windows OS.

删除这两行:

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

如上所述，即使在Windows中，它们也会失败.同样，如果您想同时在Win和Mac环境中使用此代码，则必须在不采取其他预防措施来避免错误的情况下使用Scripting.Dictionary.

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

您将需要使用

You will need to implement conditional compilation using compiler directives to 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.cls and Dictionary.cls code from the other answer which implements the Dictionary replica in to plain text files, and imported both of the modules to your project's 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")

实际上，只要您将代码放置在之前的任意位置，无论在dAttributes还是dValues上调用，放在哪里都无所谓.

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

这在两个操作系统上都应该起作用，因为Dictionary.cls模仿了Scripting.Dictionary的方法.

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

注意:最好对这些对象分配进行分组，而不是在整个过程中随意地添加它们，尤其是在使用条件编译时，尤其是当它们使用时，因为这样更容易理解并且易于维护前进.

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

这篇关于ActiveX组件无法创建对象--- Mac版Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！