5

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.

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, 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 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
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
user3704103
  • 61
  • 1
  • 1
  • 5

2 Answers2

2

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

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.

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.

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

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.

Using Conditional Compilation on 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 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")

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.

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.

Community
  • 1
  • 1
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • This is a very comprehensive answer. Thank you. Very frankly, I am less than confident I can pull it off (this stuff is mysterious to non-programmers, maybe arcane is a better word!), but I will try to make it work. Highly appreciate your input. Thanks. – user3704103 May 02 '17 at 15:46
  • @user3704103 if you get stuck, just edit/update the code in your question above and I'll see if I can make any other suggestion. Important part is copying those two `.cls` files and importing them to the Excel file's VBProject. Once you do that, then it's just changing a few lines of code in your subroutine. – David Zemens May 02 '17 at 15:50
  • Yes, those two .cls files were imported successfully into the VBProject. Set dAttributes = CreateObject("Scripting.Dictionary”) <— the dubugger stops here Set dAttributes = CreateObject("New Dictionary”) <— replaced with this, but it still gets the same error* *Error: Run-time error ’429’ ActiveX component can’t create object – user3704103 May 02 '17 at 16:36
  • OK, so you need to get rid of those lines. You have two lines that do `Createobject("New.Dictionary")`, and as I mentioned, these will fail even in Windows, because it's an invalid classname. Then, somewhere towards the top of your code (before you call on either of your two dictionary objects, add the code I put in my answer under "**In your code, do something like this**" :) – David Zemens May 02 '17 at 16:42
  • It's not working yet.. btw, does it look right now? I've commented out those 2 lines you suggested removing as well as adding the extra code. Thanks. – user3704103 May 02 '17 at 16:59
  • Looks right to me, are you still getting error? If so, what error and on what line is it raising the error? – David Zemens May 02 '17 at 17:02
  • Set LO = ActiveSheet.ListObjects("Data") <--- Run-time error '9': Subscript out of range – user3704103 May 02 '17 at 17:04
  • That's a different problem and I will help you with that, but it's apparent that we have definitely solved your original question, and you should consider marking this answer as "Accepted"... – David Zemens May 02 '17 at 17:11
  • Did you Google or search on StackOverflow for that error message? It always means that you're attempting to reference an item of a collection (whether by name or index) which doesn't exist. In other words, there is no table named "Data" on whatever sheet is *active* at the moment you run this code. – David Zemens May 02 '17 at 17:12
  • So, rather than relying on `ActiveSheet`, it may be preferable to do `Set LO = Worksheets("name of sheet").ListObjects("Data")`. If it's a possibility that no such table exists, then you need to add some logic (called error-handling) which does *something* (this part is up to you). Simple cases just inform the user and exit the procedure, a more robust approach may attempt to create the table at runtime, etc. – David Zemens May 02 '17 at 17:15
  • Okay, so that is very good news. I will mark it answered. Perhaps I will create a new question for the remaining problem if it doesn't fall out. Thank you again for the great help! – user3704103 May 02 '17 at 17:21
  • Everything is working perfectly now. Code fixed and all. Thank you! – user3704103 May 03 '17 at 05:23
0

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 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.

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.

sysmod
  • 463
  • 3
  • 11