How do I force my VBA code to write data to the appropriate cells in Excel, and then continue?

I am working on an interactive Excel project and my first proper foray into VBA.

I have a series of UserForms that take user-entered data and write them to cells. In the final UserForm in the sequence, data is written to a final cell. Then, in a further cell, the data is concatenated (not using VBA, but as a standard formula in the cell using CONCAT). Then, the VBA code copies the concatenated cell to another one.

The problem is, things are not happening in that order. What is happening is that all the VBA code from all the UserForms that have been used is executed, and then the CONCAT in the cell kicks in. This results in the cell being copied before the data has been written to any cells at all and the CONCAT formula has returned its results.

This is the existing code:

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

This is the line that copies the concatenated cell:

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

This is my CONCAT formula:

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

When the code is run as above, cell Front!B7 displays WAIT, which is how I knew it was copying the cell before values had been written and concatenated.

I replaced the source cell with the very first cell that should contain data and it is empty when that line of VBA is executed, demonstrating that nothing is being written until the VBA code has been executed to the end.

I have searched and found code to sleep, and to use the Application.CalculationState property, but then it doesn't copy anything at all.

I even tried my hand at rewriting the code to use variables instead of just cells, and again got nothing at all.

If there is a way of simply making sure cell Answers!C25 has executed the concatenation, i.e., no longer displays WAIT, and then the VBA code that copies the values across is executed, that would be perfect. I'm trying to avoid starting again from the beginning if possible. Any help much appreciated.

Answer

You are writing values to B23 and then immediately reading C25, but Excel has not yet calculated the formula in C25.

You must force Excel to recalculate, wait, then read the value.

    ' Force recalc
    Application.Calculate

    ' Wait briefly
    DoEvents
    Application.Wait Now + TimeValue("0:00:01")

    ' Now read the value
    If Range("Answers!C25").Value <> "WAIT" Then
        Range("Front!B7").Value = Range("Answers!C25").Value
    Else
        MsgBox "Formula did not update in time.", vbExclamation
    End If