PCO报表自动化报错：无法获取WorksheetFunction类的Concat属性

错误原因 #

Application.WorksheetFunction.Concat是Excel 2019及365专属的工作表函数，若你的Excel版本低于2019，调用该函数就会触发“无法获取WorksheetFunction类的Concat属性”错误。

修复方案 #

1. 兼容全版本的字符串拼接写法 #

直接用VBA原生的字符串连接符&替代Concat函数，无需依赖高版本Excel：

' 原代码
Application.WorksheetFunction.Concat(COL.Cells(col1cell.row - 1, 38).value, COL2.Cells(col2cell.row - 1, 20).value)
' 替换为
COL.Cells(col1cell.row - 1, 38).Value & COL2.Cells(col2cell.row - 1, 20).Value

2. 代码整体优化（解决其他潜在问题） #

原代码存在Range引用不明确、循环效率低、资源未重置等问题，以下是优化后的完整代码：

Sub testing()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim wsInstructions As Worksheet
    Dim wsAssigned As Worksheet
    Dim wsPCOReport As Worksheet
    Dim lrAssigned As Long
    Dim lastRowReport As Long
    Dim colProjectReport As Range
    Dim colProjectAssigned As Range
    Dim projectDict As Object
    Dim cell As Range
    Dim currentProject As String
    Dim concatNotes As String
    
    ' 初始化字典，用于存储项目对应的备注集合
    Set projectDict = CreateObject("Scripting.Dictionary")
    
    ' 绑定工作表（避免依赖激活表）
    Set wsInstructions = Workbooks("Projects - Retail.xlsm").Sheets("Instructions")
    Set wb2 = ThisWorkbook
    Set wsAssigned = wb2.Sheets("Assigned Projects - Retail")
    Set wb1 = Workbooks.Open("C:\Users\joshu\OneDrive\experiment\PCO Report.xlsm")
    Set wsPCOReport = wb1.Sheets("CL CA Report")
    
    ' 获取有效行数（用xlUp避免空行干扰）
    lrAssigned = wsAssigned.Range("A" & wsAssigned.Rows.Count).End(xlUp).Row
    lastRowReport = wsPCOReport.Range("A" & wsPCOReport.Rows.Count).End(xlUp).Row
    
    ' 绑定项目名称列（明确指定工作表）
    Set colProjectAssigned = wsAssigned.Range("B2:B" & lrAssigned)
    Set colProjectReport = wsPCOReport.Range("C2:C" & lastRowReport)
    
    ' 遍历主工作簿项目，将备注存入字典（相同项目合并备注）
    For Each cell In colProjectAssigned
        currentProject = cell.Value
        If projectDict.Exists(currentProject) Then
            ' 合并已有备注和当前备注
            projectDict(currentProject) = projectDict(currentProject) & cell.Offset(0, 18).Value ' 第20列是B列偏移18列
        Else
            projectDict(currentProject) = cell.Offset(0, 18).Value
        End If
    Next cell
    
    ' 遍历PCO报表，匹配项目并写入合并后的备注
    For Each cell In colProjectReport
        currentProject = cell.Value
        If projectDict.Exists(currentProject) Then
            ' 写入合并备注到第38列
            cell.Offset(0, 35).Value = projectDict(currentProject)
            ' 写入第21列内容到第39列
            cell.Offset(0, 36).Value = wsAssigned.Range("U" & colProjectAssigned.Find(currentProject).Row).Value
        End If
    Next cell
    
    ' 保存并重置Excel设置
    wb1.Save
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    Set projectDict = Nothing
    Set wsInstructions = Nothing
    Set wsAssigned = Nothing
    Set wsPCOReport = Nothing
    Set wb1 = Nothing
    Set wb2 = Nothing
End Sub

优化点说明 #

• 字典替代双重循环：将O(n²)的双重循环改为O(n)的两次单循环，大幅提升处理速度，尤其是数据量大时
• 明确Range归属：所有Range都指定所属工作表，避免因激活表变化导致的引用错误
• 资源重置：恢复屏幕更新和提示弹窗，避免Excel处于异常状态
• 空行兼容：用End(xlUp)获取有效行数，跳过中间空行的干扰

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