VBA Compatibility Issues with Excel for Mac

[babyjwhale]

I'm trying to set up a simple database with an input tab and a database tab. One of my primary users has a Mac and is experiencing errors when trying to run two of the macros. We are both O365 subscribers; I'm using Excel version 2411. For better or worse, I've used ChatGPT to create and troubleshoot these until they work great for me on Windows. Two issues:
1) a custom sub is not working. The input tab is basically a transposed table, as it's easier to input in a column rather than in a row, for this case. The Load Comps sub transposes into the database tab and Lease_Database table.
2) a JSON converter (downloaded from GitHub) is not working on Mac, though the code appears to work on Mac? I'm using the JSON Converter to geocode using Google API. JSON Converter: GitHub - VBA-tools/VBA-JSON: JSON conversion and parsing for VBA

For 1, here's the vba code. Anyone have any insight as to why it wouldn't work? If I recall from him telling me it didn't work, I think it was the ListObjects?

Sub LeaseLoadComps()
    Dim dbWs As Worksheet
    Dim dbTable As ListObject
    Dim dbHeaders As Range
    Dim headerMap As Object
    Dim i As Long, j As Long
    Dim rowLabel As String
    Dim dbLastRow As Long
    Dim userName As String, creationDate As Date

    ' Set worksheet for Lease Database
    Set dbWs = ThisWorkbook.Sheets("Lease Database")

    ' Get the Lease_Database table dynamically
    On Error Resume Next
    Set dbTable = dbWs.ListObjects("Lease_Database")
    On Error GoTo 0
    If dbTable Is Nothing Then
        MsgBox "Error: Table 'Lease_Database' not found on Lease Database sheet.", vbExclamation
        Exit Sub
    End If

    ' Get the header range dynamically
    Set dbHeaders = dbTable.HeaderRowRange

    ' Ensure leaseInputRange is initialized
    If leaseInputRange Is Nothing Then
        Call LeaseInitializeInputRange
    End If

    ' Find the last row in the Lease_Database table
    dbLastRow = dbTable.ListRows.Count + dbHeaders.row + 1

    ' Create a dictionary to map Lease_Database headers to their respective columns
    Set headerMap = CreateObject("Scripting.Dictionary")
    For j = 1 To dbHeaders.Columns.Count
        headerMap(dbHeaders.Cells(1, j).value) = dbHeaders.Cells(1, j).Column
    Next j

    ' Fetch user details and timestamp
    userName = Environ("Username")
    creationDate = Date

    ' Loop through each column in Input (B to V)
    For j = 2 To leaseInputRange.Columns.Count
        If leaseInputRange.Cells(1, j).value <> "" Then ' Only process if column has data
            ' Write the data for the current column into a single row in Lease_Database
            For i = 1 To leaseInputRange.Rows.Count
                rowLabel = leaseInputRange.Cells(i, 1).value ' Row label from column B in leaseInputRange
                If headerMap.Exists(rowLabel) Then
                    dbWs.Cells(dbLastRow, headerMap(rowLabel)).value = leaseInputRange.Cells(i, j).value
                Else
                    Debug.Print "No match found for row label: " & rowLabel
                End If
            Next i

            ' Populate GlobalID, CreationDate, Creator
            dbWs.Cells(dbLastRow, headerMap("GlobalID")).value = CreateGlobalID()
            dbWs.Cells(dbLastRow, headerMap("CreationDate")).value = creationDate
            dbWs.Cells(dbLastRow, headerMap("Creator")).value = userName

            ' Move to the next row for the next column in Input
            dbLastRow = dbLastRow + 1
        End If
    Next j
End Sub

Happy to post more if needed. Let me know!

 

[Dan_W]

It isn't clear from your qestion, but have you tried using VBA on the Mac? If not, it's criminallly bad and janky.

It also isn't clear what actual errors are? I would suggest asking the user to try running it again, and get the user to report back the: (1) error number; (2) error message; and (3) the line of code that it broke on. I don't think ListObjects is one of the items that one normally has problems with when trying to do the most basic of things in VBA on the Mac, but I could be wrong on that, because the list is long, and entirely unpredictable.

I was going to say that I couldn't see any of the 'shouldn't be a problem but on a Mac it really is" issues with the code, but then I saw:

CreateObject("Scripting.Dictionary")

If it's not the problem experienced by the user, it most definitely will be. This creates a COM object that simply doesn't exist on the Mac. JSONConverter is compatible on a Mac (from memory), so I'd be surprised if that's a problem. It also uses dictionaries (or a dictionary replacement), but that's because the author uses all manner of dark magic Mac-specific APIs to accomplish that task.

Assuming the dictionary is the problem (and indeed the only problem, hopefully), the only suggestion I can think of is to use Cristian Buse's FastDictionary . I haven't used it myself, but everything else he makes: (1) is genius; and (2) work flawlessly, so I would expect the same here. I would also add that it is designed to be a drop-in replacement for the Scripting Dictionary, so it may just be a matter of adding the code (as set out on his github readme.md) and changing the following lines of code:

Dim headerMap As Dictionary

Set headerMap = New Dictionary

I hope that helps in some way.