Excel VBA嵌套工作表函数调用与复杂数组公式插入问题求助

错误原因 #

直接在VBA中套用Excel单元格公式会报错，核心问题：

• VBA不支持Excel的@结构化引用语法
• TEXTBEFORE/TEXTAFTER是Excel 365专属函数，即使通过WorksheetFunction调用，也需要处理引号转义，远不如VBA原生方法可靠

可行方案 #

方案1：VBA原生提取（推荐） #

直接操作文件名，无需依赖Excel函数：

Sub GetWorkbookYear()
    Dim wbName As String
    Dim yearStr As String
    
    wbName = ThisWorkbook.Name
    yearStr = Left(wbName, 4) ' 提取文件名前四位
    
    ' 可选：验证年份有效性
    If IsNumeric(yearStr) And yearStr >= "1900" Then
        ' 示例：将结果写入A1单元格
        Range("A1").Value = yearStr
    Else
        MsgBox "文件名需以四位年份开头"
    End If
End Sub

方案2：调用Excel工作表函数（保留原逻辑） #

转义引号并去掉@，通过WorksheetFunction执行：

Sub Test_1_Fixed()
    Dim fileNamePart As String
    Dim yearVal As Integer
    
    fileNamePart = WorksheetFunction.TextAfter( _
        WorksheetFunction.TextBefore(ThisWorkbook.FullName, "[", 1), " ", 1)
    yearVal = Year(DateSerial(CInt(fileNamePart), 1, 1))
    
    MsgBox "提取年份：" & yearVal
End Sub

错误原因 #

1004错误来自三点：

• 公式中引号嵌套冲突（VBA字符串需转义双引号）
• 数组公式需用Formula2属性（Excel 365/2021）而非Formula
• 路径引用格式不符合VBA语法要求

解决方案（含自动路径替换+避免重复执行） #

步骤1：编写公式更新过程 #

Sub UpdateApplePickingFormula()
    Dim ws As Worksheet
    Dim targetCell As Range
    Dim currentWbPath As String
    Dim originalFormula As String
    Dim updatedFormula As String
    Dim yearStr As String
    
    ' 获取当前工作簿年份，模板（1900开头）直接退出
    yearStr = Left(ThisWorkbook.Name, 4)
    If yearStr = "1900" Then Exit Sub
    
    ' 指定目标工作表和单元格（按需调整）
    Set ws = ThisWorkbook.Worksheets("Apple Picking History")
    Set targetCell = ws.Range("A1")
    
    ' 检查是否已更新过，避免重复执行
    If InStr(targetCell.Formula2, yearStr) > 0 Then Exit Sub
    
    ' 模板公式：用<<PATH>>做占位符，双引号转义为两个双引号
    originalFormula = "=EXPAND(IF(IFERROR(SORT(FILTER('<<PATH>>[" & yearStr & " Apple Picking.xlsm]Apple Picking'!$A$4:$H$10003,'<<PATH>>[" & yearStr & " Apple Picking.xlsm]Apple Picking'!$A$4:$A$10003 = Filter_selection_current_apple_name,""""),2,-1,FALSE),"""") = 0,"""",IFERROR(SORT(FILTER('<<PATH>>[" & yearStr & " Apple Picking.xlsm]Apple Picking'!$A$4:$H$10003,'<<PATH>>[" & yearStr & " Apple Picking.xlsm]Apple Picking'!$A$4:$A$10003 = Filter_selection_current_apple_name,""""),2,-1,FALSE),""""),10000,8,"""")"
    
    ' 获取当前工作簿路径（不含文件名）
    currentWbPath = Left(ThisWorkbook.FullName, Len(ThisWorkbook.FullName) - Len(ThisWorkbook.Name))
    
    ' 替换路径占位符并插入数组公式
    updatedFormula = Replace(originalFormula, "<<PATH>>", currentWbPath)
    targetCell.Formula2 = updatedFormula
End Sub

步骤2：设置工作簿打开自动执行 #

打开ThisWorkbook模块，添加打开事件：

Private Sub Workbook_Open()
    UpdateApplePickingFormula
End Sub

关键说明 #

• 重复执行规避：通过检查公式中是否包含当前年份，判断是否已完成更新
• 引号处理：VBA字符串中用""表示实际的"，路径引用的单引号直接使用即可
• 兼容性：Excel 2019及更早版本需将Formula2改为ArrayFormula，但EXPAND/SORT/FILTER仅支持365版本

内容的提问来源于stack exchange，提问作者G.D.