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
