Excel for Mac not running VBA scripts consistently

Hi @P Keith

Thank you for posting your question in Microsoft Q&A. 

Based on the information you provided, I understand that you are attempting to run VBA automation scripts on Excel for Mac (Microsoft 365) that were originally designed for Windows. Despite enabling macros and granting Full Disk Access, you are encountering inconsistent behavior where scripts stop without error or fail to execute functions like FileDialog and Shell.

I tested your script in my environment, and while the core logic seems functional, the Application.InputBox(..., Type:=8) (used for range selection) is indeed notoriously unstable on macOS. In my tests, it often fails to update the reference correctly, returns only the currently active cell regardless of selection, or crashes when trying to select ranges across different sheets.

Please try the optimized code below, which includes better error handling for the Mac environment.

Option Explicit
 
' Cross‑platform (Windows + macOS) formatting & summing macro
Sub FormatAndSumRange_MacSafe()
    Dim rng As Range
    Dim cell As Range
    Dim total As Double
    Dim resp As Variant
   
    Application.ScreenUpdating = False
    Application.EnableEvents = False
   
    ' Ask user to select a range (Type:=8 returns a Range object)
    On Error GoTo CancelOrInvalid
    Set rng = Application.InputBox( _
                Prompt:="Select a range to process:", _
                Type:=8)  ' Works on Mac
    On Error GoTo 0
   
    If rng Is Nothing Then
        MsgBox "No range selected.", vbExclamation
        GoTo Cleanup
    End If
   
    ' Loop through and format cells
    total = 0
    For Each cell In rng.Cells
        If IsNumeric(cell.Value) Then
            total = total + CDbl(cell.Value)
            With cell
                .Font.Bold = True
                .Interior.Color = RGB(220, 230, 241)
            End With
        End If
    Next cell
   
    ' Show result
    MsgBox "Total numeric value: " & total, vbInformation
   
Cleanup:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
 
CancelOrInvalid:
    ' Handles Cancel press or invalid selection
    MsgBox "No range selected or input was invalid.", vbExclamation
    Resume Cleanup
End Sub

Additionally, please note the following crucial differences for Mac VBA development:

1. Shell Commands: The Shell function (used for Windows CMD) is not supported on Mac. Instead, you should use AppleScriptTask to call an AppleScript file that executes the necessary do shell script commands.
2. File Dialogs: Application.FileDialog is limited and often unstable in the Mac sandbox environment. It is highly recommended to use Application.GetOpenFilename or Application.GetSaveAsFilename instead.

Please try these adjustments and let me know if the stability improves.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".     

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.