Windows版Excel VBA代码移植Mac后无法编译求解决方案

我在Windows版Excel中编写了以下VBA代码，移植到Mac版Excel时始终无法通过编译，求修复方案。原代码如下：

Sub GenerateExcelDoc()
    ' Define Excel object and workbook
    Dim objExcel As Object
    Dim objWorkbook As Object
    Dim objSheet As Object
    Set objExcel = CreateObject("Excel.Application")
    Set objWorkbook = objExcel.Workbooks.Add(1)
    objWorkbook.Activate ' Activates the workbook
    Set objSheet = objWorkbook.Sheets(1) ' Set the first sheet as the active sheet

    ' Define source worksheets
    Dim srcSheet1 As Object
    Dim srcSheet2 As Object
    Set srcSheet1 = ThisWorkbook.Sheets("Sheet1")
    Set srcSheet2 = ThisWorkbook.Sheets("Best Practices Guidance")

    ' Copy data from source worksheet to new worksheet
    srcSheet1.UsedRange.Copy
    objSheet.Range("A1").PasteSpecial xlPasteValues

    ' Auto-fit columns in the new worksheet
    objSheet.Columns.AutoFit

    ' Create a new worksheet for "Best Practices Guidance" tab
    Dim newSheet As Object
    Set newSheet = objWorkbook.Sheets.Add(, objSheet)
    newSheet.Name = "Best Practices Guidance"

    ' Copy data from source "Best Practices Guidance" worksheet to new worksheet
    srcSheet2.Cells.Copy
    newSheet.Range("A1").PasteSpecial xlPasteValues

    ' Adjust width of columns in the new workbook
    objSheet.Columns("A").ColumnWidth = objSheet.Columns("A").ColumnWidth + 150
    newSheet.Columns("A").ColumnWidth = newSheet.Columns("A").ColumnWidth + 150

    ' Adjust the height of column A to be 10 cells high
    objSheet.Columns("A").RowHeight = 1.5 * objSheet.Rows(1).RowHeight
    newSheet.Columns("A").RowHeight = 1.5 * newSheet.Rows(1).RowHeight

    ' Show Excel application
    objExcel.Visible = True

    ' Clean up objects
    Set srcSheet1 = Nothing
    Set srcSheet2 = Nothing
    Set objSheet = Nothing
    Set objWorkbook = Nothing
    Set objExcel = Nothing
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Oldvalue As String
    Dim Newvalue As String
    
    Application.EnableEvents = True
    
    On Error GoTo Exitsub
    
    If Target.Address = "$B$40" Or Target.Address = "$B$51" Then
        If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
            GoTo Exitsub
        Else
            If Target.Value = "" Then GoTo Exitsub Else
            Application.EnableEvents = False
            Newvalue = Target.Value
            Application.Undo
            Oldvalue = Target.Value
        
            If Oldvalue = "" Then
                Target.Value = Newvalue
            Else
                If InStr(1, Oldvalue, Newvalue) = 0 Then
                    Target.Value = Oldvalue & ", " & Newvalue
                Else:
                    Target.Value = Oldvalue
                End If
            End If
        End If
    End If
    
    Application.EnableEvents = True

Exitsub:
    Application.EnableEvents = True
End Sub

核心问题与修复方案 #

Mac版Excel的VBA在对象模型、常量引用、语法细节上和Windows存在差异，以下是针对性修复：

• 显式定义缺失常量：Mac版VBA可能未自动加载部分Excel内置常量，需手动声明。
• 调整Excel实例创建方式：Mac不推荐用CreateObject创建Excel实例，改用New Excel.Application（需确保引用Excel对象库）。
• 修正工作表添加语法：Mac上Sheets.Add需显式指定参数名称，避免位置参数报错。
• 注意大小写匹配：Mac系统区分工作表名称大小写，需确保代码中名称与实际完全一致。

修复后的完整代码 #

' 手动定义Mac可能缺失的Excel内置常量
Const xlPasteValues As Long = -4163
Const xlCellTypeAllValidation As Long = -4174

Sub GenerateExcelDoc()
    ' 改用强类型声明，需确保引用Excel对象库
    Dim objExcel As Excel.Application
    Dim objWorkbook As Excel.Workbook
    Dim objSheet As Excel.Worksheet
    ' Mac推荐的Excel实例创建方式
    Set objExcel = New Excel.Application
    Set objWorkbook = objExcel.Workbooks.Add(1)
    objWorkbook.Activate
    Set objSheet = objWorkbook.Sheets(1)

    ' 定义源工作表，注意名称大小写匹配
    Dim srcSheet1 As Excel.Worksheet
    Dim srcSheet2 As Excel.Worksheet
    Set srcSheet1 = ThisWorkbook.Sheets("Sheet1")
    Set srcSheet2 = ThisWorkbook.Sheets("Best Practices Guidance")

    ' 复制源数据到新工作表
    srcSheet1.UsedRange.Copy
    objSheet.Range("A1").PasteSpecial xlPasteValues

    ' 自动调整列宽
    objSheet.Columns.AutoFit

    ' 添加新工作表，显式指定After参数
    Dim newSheet As Excel.Worksheet
    Set newSheet = objWorkbook.Sheets.Add(After:=objSheet)
    newSheet.Name = "Best Practices Guidance"

    ' 复制最佳实践指南数据，改用UsedRange避免复制空白区域
    srcSheet2.UsedRange.Copy
    newSheet.Range("A1").PasteSpecial xlPasteValues

    ' 调整A列宽度
    objSheet.Columns("A").ColumnWidth = objSheet.Columns("A").ColumnWidth + 150
    newSheet.Columns("A").ColumnWidth = newSheet.Columns("A").ColumnWidth + 150

    ' 调整A列行高
    objSheet.Columns("A").RowHeight = 1.5 * objSheet.Rows(1).RowHeight
    newSheet.Columns("A").RowHeight = 1.5 * newSheet.Rows(1).RowHeight

    ' 显示Excel窗口
    objExcel.Visible = True

    ' 释放对象资源
    Set srcSheet1 = Nothing
    Set srcSheet2 = Nothing
    Set objSheet = Nothing
    Set objWorkbook = Nothing
    Set objExcel = Nothing
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Oldvalue As String
    Dim Newvalue As String
    Dim validationCheck As Range
    
    Application.EnableEvents = True
    
    On Error GoTo Exitsub
    
    ' 检查目标单元格地址
    If Target.Address = "$B$40" Or Target.Address = "$B$51" Then
        ' 捕获SpecialCells可能的报错
        On Error Resume Next
        Set validationCheck = Target.SpecialCells(xlCellTypeAllValidation)
        On Error GoTo Exitsub
        
        If validationCheck Is Nothing Then
            GoTo Exitsub
        Else
            If Target.Value = "" Then GoTo Exitsub
            
            Application.EnableEvents = False
            Newvalue = Target.Value
            Application.Undo
            Oldvalue = Target.Value
        
            If Oldvalue = "" Then
                Target.Value = Newvalue
            Else
                ' 加入文本比较，忽略大小写差异
                If InStr(1, Oldvalue, Newvalue, vbTextCompare) = 0 Then
                    Target.Value = Oldvalue & ", " & Newvalue
                Else
                    Target.Value = Oldvalue
                End If
            End If
        End If
    End If
    
    Application.EnableEvents = True

Exitsub:
    Application.EnableEvents = True
End Sub

额外配置说明 #

在Mac Excel中操作时，需通过工具→引用勾选Microsoft Excel xx.x Object Library，确保强类型声明正常工作；若仍有报错，检查文件访问权限（Mac对文件读写权限限制更严格）。

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