title: ActiveX component can’t create object --- Excel for Mac
body: <p>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.</p>
  
  <blockquote>
    <p>Error: Run-time error ’429’
    ActiveX component can’t create object</p>
  </blockquote>
  
  <p>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…]</p>
  
  <p>FWIW, I have tried to replace “Scripting.Dictionary” with “New.Dictionary” in two places (see below), but that didn’t get it working.</p>
  
  <pre><code>Set dAttributes = CreateObject("New.Dictionary")
  
  Set dValues = CreateObject("New.Dictionary”)
  </code></pre>
  
  <p>RandomiseData file:</p>
  
  <pre><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
  
  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(" &amp; LO.Name &amp; "[Attribute]=""" &amp; dKey &amp; """,MATCH(" &amp; LO.Name &amp; "[Value]," &amp; LO.Name &amp; "[Value],0)),ROW(" &amp; LO.Name &amp; "[Value])-ROW(" &amp; LO.Name &amp; "[[#Headers],[Value]]))&gt;0))")
  cCount = Evaluate("SUM(--(FREQUENCY(IF(" &amp; LO.Name &amp; "[Attribute]=""" &amp; dKey &amp; """,MATCH(" &amp; LO.Name &amp; "[Candidate]," &amp; LO.Name &amp; "[Candidate],0)),ROW(" &amp; LO.Name &amp; "[Candidate])-ROW(" &amp; LO.Name &amp; "[[#Headers],[Candidate]]))&gt;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 &lt;= 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
  </code></pre>
  
  <p><strong><em>EDITED CODE</em></strong></p>
  
  <pre><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(" &amp; LO.Name &amp; "[Attribute]=""" &amp; dKey &amp; """,MATCH(" &amp; LO.Name &amp; "[Value]," &amp; LO.Name &amp; "[Value],0)),ROW(" &amp; LO.Name &amp; "[Value])-ROW(" &amp; LO.Name &amp; "[[#Headers],[Value]]))&gt;0))")
  cCount = Evaluate("SUM(--(FREQUENCY(IF(" &amp; LO.Name &amp; "[Attribute]=""" &amp; dKey &amp; """,MATCH(" &amp; LO.Name &amp; "[Candidate]," &amp; LO.Name &amp; "[Candidate],0)),ROW(" &amp; LO.Name &amp; "[Candidate])-ROW(" &amp; LO.Name &amp; "[[#Headers],[Candidate]]))&gt;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 &lt;= 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
  </code></pre>