(Solved) – Macro on 32-bit Excel not working on 64-bit MacBook Pro

(solved)-–-macro-on-32-bit-excel-not-working-on-64-bit-macbook-pro

Trying to get this 32-bit macro to work on 64-bit Excel MacBook Pro. I tried using PTrSafe but cannot get it working (I am Novice at this). Any help will be greatly appreciated! Also, should you need a sample spreadsheet, I can provide one as well.

Sub test()
 Dim a, e, s, Ignore As String, temp, x
 Dim dic As Object, dic2 As Object, dic3 As Object
 Set dic = CreateObject("Scripting.Dictionary")
 dic.CompareMode = 1
 Set dic2 = CreateObject("Scripting.Dictionary")
 dic2.CompareMode = 1
 Set dic3 = CreateObject("Scripting.Dictionary")
 dic3.CompareMode = 1
 With Range("m1").CurrentRegion.Offset(1)
 Ignore = Join(Application.Transpose(.Resize(.Rows.Count - 1).Value), Chr(2))
 End With
 a = Range("a1", Range("a" & Rows.Count).End(xlUp)).Value
 With CreateObject("VBScript.RegExp")
 .Global = True: .IgnoreCase = True
 .Pattern = "([$()-^|\[]* ?.])"
 Ignore = "[^w ]|b(" & Replace(.Replace(Ignore, "$1"), Chr(2), "|") & ")b"
 For Each e In a
 If e <> "" Then
 x = GetSentense(e, 2)
 If IsArray(x) Then
 For Each s In x
 dic2(s) = dic2(s)   1
 Next
 End If
 x = GetSentense(e, 3)
 If IsArray(x) Then
 For Each s In x
 If s <> "" Then dic3(s) = dic3(s)   1
 Next
 End If
 .Pattern = Ignore
 temp = Application.Trim(.Replace(e, ""))
 For Each s In Split(temp)
 If s <> "" Then dic(s) = dic(s)   1
 Next
 End If
 Next
 End With
 Range("b2").Resize(dic.Count, 2).Value = _
 Application.Transpose(Array(dic.keys, dic.items))
 Range("e2").Resize(dic2.Count, 2).Value = _
 Application.Transpose(Array(dic2.keys, dic2.items))
 Range("h2").Resize(dic3.Count, 2).Value = _
 Application.Transpose(Array(dic3.keys, dic3.items))
End Sub


Function GetSentense(ByVal txt As String, myStep)
 Dim i As Long, ii As Long, temp, x
 On Error Resume Next
 x = Split(txt): ReDim temp(UBound(x) - myStep)
 If Err Then GetSentense = Empty: Exit Function
 On Error GoTo 0
 For i = 0 To UBound(x) - myStep - 1
 For ii = 1 To myStep
 temp(i) = Trim$(temp(i) & " " & x(i   ii - 1))
 Next
 Next
 GetSentense = temp
End Function

Leave a Reply

Your email address will not be published. Required fields are marked *