使用VBA用户窗体值更新WORKDAY.INTL公式时触发运行时错误'1004'

问题背景 #

通过用户窗体输入月时长来更新单元格F525的WORKDAY.INTL公式时，触发Run-time error '1004'错误：

• 原有公式：=WORKDAY.INTL(WORKDAY(F3,(26*22)),1,"0111111")
• 期望更新后的公式：=WORKDAY.INTL(WORKDAY(F3,(10*22)),1,"0111111")

错误的VBA代码如下：

Private Sub Calculate_CommandButton_Click()
    Sheets("Project Plan").Select
    Application.ScreenUpdating = False

    Dim lookupValue As String

    Dim targetCell As Range


    ' Get the value from the UserForm control

    lookupValue = Me.GoLiveDuration_TextBox.Value


    ' Set the target cell where the VLOOKUP formula is

    Set targetCell = Range("F525") ' Replace "A1" with the actual cell containing the formula


    ' Update the VLOOKUP formula with the new lookup value

    targetCell.Formula = "=WORKDAY.INTL(WORKDAY(F3,(" & lookupValue & " *22)),1, & Chr(34) 0111111 &   Chr(34) &) "
 
    Application.ScreenUpdating = True
End Sub

错误原因 #

公式字符串拼接存在语法错误：

• Chr(34)（代表双引号）未用&正确连接到字符串中，位置混乱
• 末尾多余了&) 的无效连接符和空格
• 生成的公式格式不符合Excel语法规范，导致Excel无法解析，触发1004错误

修正后的代码 #

Private Sub Calculate_CommandButton_Click()
    Application.ScreenUpdating = False
    
    Dim lookupValue As Double
    Dim targetCell As Range
    
    ' 转换输入为数值，避免非数字输入导致的公式错误
    lookupValue = Val(Me.GoLiveDuration_TextBox.Value)
    
    ' 直接指定工作表和单元格，避免Select操作的潜在问题
    Set targetCell = ThisWorkbook.Sheets("Project Plan").Range("F525")
    
    ' 正确拼接公式字符串
    targetCell.Formula = "=WORKDAY.INTL(WORKDAY(F3,(" & lookupValue & " *22)),1," & Chr(34) & "0111111" & Chr(34) & ")"
    
    Application.ScreenUpdating = True
End Sub

额外优化说明 #

1. 移除Sheets("Project Plan").Select，直接通过ThisWorkbook.Sheets定位单元格，避免激活工作表带来的异常
2. 将lookupValue改为Double类型，并用Val转换输入值，防止用户输入非数字内容导致公式失效
3. 修正公式拼接逻辑，确保生成的公式完全符合Excel语法要求

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