VBA - macro works on Windows 10, fails on Mac OS

[mfawley]

All,

Some help please. I have a macro (code below) that autoruns to prompt you to select a folder, copy data from any files within that folder and paste it to the master spreadsheet for analysis.

This works absolutely fine on Win 10 (O365 business), but when my colleague tries on his Mac (O365 business), it fails at the very first "with" statement. Unfortunately, I don't have a Mac of my own to perform tests and have a play, and he is VBA illiterate!

So my question is - do you have to change the VBA syntax to make the macro OS independent? Or should it just work?

Many thanks for any pointers.

Martin.

Sub AutoRun()
'
' Open all expenses in a designated folder and pull info from them.
'


'
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Dim StaffName, CheckCell As String
    Dim Month As Date
    Dim i, j, k, next_j As Integer
    Dim fldr As FileDialog
    Dim sItem As String
    
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = strPath
        If .Show <> -1 Then GoTo NextCode
            sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem & "\"
    Set fldr = Nothing
    
    Set wkbDest = ThisWorkbook
    
    Application.ScreenUpdating = False
    
    wkbDest.Activate
    
    Sheets("Data").Activate
    
    Range("A1:E10000").ClearContents
    
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Name"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Category"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Project"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Work Package"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Expense"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Amount"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "VAT"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Total"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "Cost Centre"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "Month"
    
    k = 2


    Application.DisplayAlerts = False


'    Const strPath As String = sItem
    ChDir GetFolder
    strExtension = Dir("*.xls*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(GetFolder & strExtension)
        With wkbSource
            StaffName = .Sheets("Expenses").Range("C5").Value
            Month = .Sheets("Expenses").Range("C6").Value
            
            j = 0
            
            For i = 9 To 30
            
                CheckCell = "C" & i
                If (.Sheets("Expenses").Range(CheckCell).Value <> "") Then
                
                    j = j + 1
                    
                End If
            Next i
            
            next_j = j + k
                        
            .Sheets("Expenses").Range("C9:J30").Copy
            .Close savechanges:=False
        End With
        
        strExtension = Dir
        
        Sheets("Data").Activate
        
        For i = k To next_j - 1
            CheckCell = "A" & i
            Range(CheckCell).Select
            ActiveCell.FormulaR1C1 = StaffName
            CheckCell = "I" & i
            Range(CheckCell).Select
            ActiveCell.FormulaR1C1 = Month
        Next i
        
        CheckCell = "B" & k
        k = k + j
        
        Range(CheckCell).Select
        ActiveSheet.Paste
    Loop
    
    Cells.Replace What:="£", Replacement:="", LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    
    Application.ScreenUpdating = True
    
    ActiveWorkbook.RefreshAll
    
    Application.DisplayAlerts = True
    
End Sub

 

[RoryA]

Should it just work? Yes. Will it? Probably not. There are so many bugs/issues/gotchas with Mac VBA that you really need to have a Mac if you have to develop for both. I'd strongly recommend Ron de Bruin's site for assistance: www.rondebruin.nl as he is one of the bast Mac Excel resources out there.

 

[mfawley]

Thanks Rory. The very first link I clicked on Ron's website says "Dir on a Mac is not working the same as in Excel for Windows, you can not use a Filter and wildcards on a Mac like in Excel for Windows"!

So I think I might have to make him his own bespoke Mac version using Ron's example workbook *sigh*

Thanks for putting me out of (and then back into!!) my misery swiftly!

 

[RoryA]

FWIW it may be easier to just put a virtual machine on his Mac and run Excel in that.