Windows下正常运行的VBA代码在Mac上出现1004运行时错误

Hey there, let’s sort out this Mac VBA issue with your SaveAsPDF sub. I’ve helped tons of folks debug cross-platform Excel VBA, and the problem almost always boils down to small platform-specific quirks that break Windows-friendly code. Let’s break down what’s likely going wrong and how to fix it.

Common Mac vs Windows VBA Culprits in Your Code #

First, looking at your snippet, I spot a few red flags that would cause issues on Mac:

• Variable mismatch: You declared fname As String but used Filename later—this creates an implicit variable, which Mac VBA (especially with strict settings) will throw errors for.
• File path separators: Windows uses backslashes (\), but Mac requires forward slashes (/). Using the wrong one will lead to invalid save locations.
• Save method reliability: Directly using SaveAs for PDFs can behave unpredictably on Mac; the ExportAsFixedFormat method is far more consistent cross-platform.
• File extension handling: Mac Excel sometimes hides file extensions, so your InStr check might not work as expected if the workbook name doesn’t show .xlsm/.xlsx.

Modified Mac-Friendly Code #

Here’s a revised version of your macro that addresses all these issues, plus adds some robustness:

Option Explicit ' Always add this at the top of your module to catch variable errors

Sub SaveAsPDF()
    Dim sh As Worksheet
    Dim fname As String
    Dim location As String
    Dim baseWorkbookName As String
    
    ' Get workbook name without extension (handles names with multiple dots, like "Project.v2.xlsm")
    baseWorkbookName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1)
    
    ' Set save location: Use Excel's default path, or replace with a specific Mac path (e.g., "/Users/YourName/Documents/")
    location = Application.DefaultFilePath & "/"
    
    ' Build full PDF file path (uses Mac-compatible forward slashes)
    fname = location & baseWorkbookName & ".pdf"
    
    ' Export active sheet to PDF (adjust to export specific sheets if needed)
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=fname, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
        
    ' Optional: Confirm save to user
    MsgBox "PDF saved successfully to:" & vbNewLine & fname, vbInformation
End Sub

Key Fixes Explained #

1. Option Explicit: Forces you to declare all variables, which catches typos (like your fname/Filename mix-up) before they cause runtime errors—critical for Mac VBA.
2. InStrRev: Finds the last occurrence of the file extension dot, so it works even if your workbook name has multiple dots (e.g., "Q3_Report.Final.xlsm").
3. Mac-compatible paths: Uses forward slashes and Application.DefaultFilePath to get a reliable save location (no more guessing where files end up).
4. ExportAsFixedFormat: This is the official cross-platform method for exporting PDFs in Excel; it avoids the quirks of SaveAs on Mac.

Additional Troubleshooting Tips #

If you still run into issues:

• Check permissions: Mac might block Excel from accessing your save folder. Go to System Settings > Privacy & Security > Files and Folders and make sure Excel has access to the folder you’re saving to.
• Avoid invalid characters: Mac doesn’t allow :, /, \, ?, *, or " in filenames—double-check your workbook name doesn’t include these.
• Update Office: Old versions of Office for Mac have buggier VBA support; make sure you’re running the latest Office 365 or 2021 release.

内容的提问来源于stack exchange，提问作者Ely1994