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

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

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

Related Articles