Excel VBA：如何确保CONCAT公式计算完成后再复制单元格值

问题背景 #

我开发的交互式Excel项目通过多个UserForms收集用户输入并写入单元格，最后一个UserForm提交时需要将Answers!C25（含CONCAT拼接公式）的结果复制到Front!B7。但VBA执行复制操作时，单元格公式还未完成计算，导致Front!B7显示公式默认的"WAIT"值。尝试过sleep、Application.CalculationState等方法均无效。

现有关键代码 #

• UserForm提交按钮代码：

Private Sub IssueComp_Done_Click()
    If IssueComp_Yes.Value = True Then
        Range("Answers!B23").Value = "I have verified the matter."
    Else
        Range("Answers!B23").Value = "I have not verified the matter"
    End If
    Range("Front!B7").Value = Range("Answers!C25").Value
    Dim objLoop As Object
    For Each objLoop In VBA.UserForms
        If TypeOf objLoop Is UserForm Then Unload objLoop
    Next objLoop
End Sub

• 核心复制操作：

Range("Front!B7").Value = Range("Answers!C25").Value

• Answers!C25的公式：

=IF(A52="DISP",CONCAT(C2,CHAR(10),CHAR(10),C7),IF(A52="NONDISP",CONCAT(C2,CHAR(10),CHAR(10),C20),"WAIT"))

解决方案 #

方法1：强制触发计算并等待完成

在复制前手动触发工作表计算，确保公式更新后再执行复制：

Private Sub IssueComp_Done_Click()
    If IssueComp_Yes.Value = True Then
        Range("Answers!B23").Value = "I have verified the matter."
    Else
        Range("Answers!B23").Value = "I have not verified the matter"
    End If
    
    ' 强制计算Answers工作表
    Sheets("Answers").Calculate
    ' 等待所有计算完成，避免异步计算延迟
    Do While Application.CalculationState <> xlDone
        DoEvents ' 释放系统资源，防止Excel假死
    Loop
    
    Range("Front!B7").Value = Range("Answers!C25").Value
    Dim objLoop As Object
    For Each objLoop In VBA.UserForms
        If TypeOf objLoop Is UserForm Then Unload objLoop
    Next objLoop
End Sub

方法2：VBA直接实现公式逻辑（推荐）

跳过单元格公式，将CONCAT拼接逻辑直接写入VBA，彻底避免计算延迟问题：

Private Sub IssueComp_Done_Click()
    Dim concatResult As String
    Dim wsAnswers As Worksheet
    Set wsAnswers = ThisWorkbook.Sheets("Answers")
    
    If IssueComp_Yes.Value = True Then
        wsAnswers.Range("B23").Value = "I have verified the matter."
    Else
        wsAnswers.Range("B23").Value = "I have not verified the matter"
    End If
    
    ' 复刻原公式的拼接逻辑
    Select Case wsAnswers.Range("A52").Value
        Case "DISP"
            concatResult = wsAnswers.Range("C2").Value & vbNewLine & vbNewLine & wsAnswers.Range("C7").Value
        Case "NONDISP"
            concatResult = wsAnswers.Range("C2").Value & vbNewLine & vbNewLine & wsAnswers.Range("C20").Value
        Case Else
            concatResult = "WAIT"
    End Select
    
    ' 直接赋值到目标单元格
    ThisWorkbook.Sheets("Front").Range("B7").Value = concatResult
    
    Dim objLoop As Object
    For Each objLoop In VBA.UserForms
        If TypeOf objLoop Is UserForm Then Unload objLoop
    Next objLoop
End Sub

方法3：循环检查单元格值直到非"WAIT"

如果必须依赖单元格公式，可循环检查Answers!C25的值，直到它不再是"WAIT"再执行复制：

Private Sub IssueComp_Done_Click()
    If IssueComp_Yes.Value = True Then
        Range("Answers!B23").Value = "I have verified the matter."
    Else
        Range("Answers!B23").Value = "I have not verified the matter"
    End If
    
    ' 先触发一次计算
    Sheets("Answers").Calculate
    ' 循环等待直到C25跳出WAIT状态
    Do While Range("Answers!C25").Value = "WAIT"
        DoEvents
        Sheets("Answers").Calculate ' 持续触发计算
    Loop
    
    Range("Front!B7").Value = Range("Answers!C25").Value
    Dim objLoop As Object
    For Each objLoop In VBA.UserForms
        If TypeOf objLoop Is UserForm Then Unload objLoop
    Next objLoop
End Sub

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