VBA中获取工作簿公式型已定义名称计算值问题求助

问题原因分析 #

你遇到的问题本质是对Excel命名名称的属性理解有误：

• RefersTo属性返回的是名称的定义文本，不是计算结果，所以拿到表达式是正常行为；
• RefersToRange仅对指向单元格区域的名称有效，你的Week5_FirstDay是计算式名称（无对应单元格），因此触发错误；
• 直接用Evaluate(RefersTo结果)失败，大概率是因为公式里的分号;（区域设置问题），VBA默认用逗号,作为参数分隔符，导致解析错误。

可行解决方案 #

方案1：直接Evaluate命名名称本身

Excel的工作簿级/工作表级命名名称可以直接被Evaluate解析计算，无需处理公式文本：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 仅在ano或mes单元格变化时执行（假设ano在A1，mes在B1）
    If Not Intersect(Target, Me.Range("A1:B1")) Is Nothing Then
        Application.EnableEvents = False ' 避免循环触发事件
        
        Dim week5FirstDay As Date
        ' 直接计算命名名称
        week5FirstDay = Evaluate("Week5_FirstDay")
        
        ' 示例：将结果写入C1
        Me.Range("C1").Value = week5FirstDay
        
        Application.EnableEvents = True
    End If
End Sub

如果是工作表级名称，需加上工作表前缀，比如Evaluate("Sheet1!Week5_FirstDay")。

方案2：VBA手动复刻公式逻辑

如果依赖命名公式不稳定，可直接在VBA中实现相同的日期计算逻辑，避免依赖命名名称：

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A1:B1")) Is Nothing Then
        Application.EnableEvents = False
        
        Dim ano As Integer, mes As String
        Dim firstDayOfMonth As Date, week5FirstDay As Date
        
        ' 获取ano和mes的值
        ano = Me.Range("A1").Value
        mes = Me.Range("B1").Value
        
        ' 计算当月第一天
        firstDayOfMonth = DateSerial(ano, Month(DateValue(mes & " 1")), 1)
        ' 复刻原公式逻辑：VBA中vbMonday对应原公式的WEEKDAY参数12（周一为一周第一天）
        week5FirstDay = DateAdd("d", 8 - Weekday(firstDayOfMonth, vbMonday), firstDayOfMonth)
        
        Me.Range("C1").Value = week5FirstDay
        
        Application.EnableEvents = True
    End If
End Sub

注意事项 #

• 必须在Worksheet_Change中加入Application.EnableEvents = False/True，防止单元格写入操作再次触发事件，形成循环；
• 若ano/mes单元格可能为空或非有效数据，建议加入错误处理（如On Error Resume Next或IsNumeric判断）。

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