VBA not allowing calculation before copy and pasting

Question

I am running a macro and one iteration of a While loop in the macro does not complete all of the calculations before data is copied and pasted into another tab. So incorrect data is copied and pasted in the subsequent step. Is there a way to force these calculations to occur? Is this a memory issue?

The calculations in question always complete immediately after the Macro finishes and are no different than the calculations that occur in prior iterations of the loop.

I have tried:

Do While Not Application.CalculationState = xlDone
            If Application.CalculationState = xlPending Then Application.Calculate
            Application.Wait (Now + TimeValue("00:00:02"))
Loop

As well as other versions of this paradigm trying to force calculations to complete prior to proceeding to the copy and paste section for each iteration.

Edit:

Sub Pull_Portfolio_Collateral()
Dim i As Integer
Dim portfolio_rows As Integer

Application.ScreenUpdating = False
portfolio_rows = GetSizePortfolio() + 1

Dim template As Worksheet
Dim portfolio As Worksheet
Dim full_coll_list As Worksheet
Dim individual_list As Worksheet
Dim row_cnt_tmp As Integer

Set portfolio = ActiveWorkbook.Sheets("Portfolio")
Set template = ActiveWorkbook.Sheets("Security - Deal Collateral")
Set full_coll_list = ActiveWorkbook.Sheets("Full Coll List")

For i = 2 To portfolio_rows
     'Set Template sheet
     template.Copy After:=Worksheets(Sheets.Count)
     If portfolio.Range("B" & i).Value <> "" Then
        'Rename Newly created sheet and set the CLO security name
        On Error Resume Next
        ActiveSheet.Name = Left(portfolio.Range("B" & i).Value, 12) & " - Coll"
        Set individual_list = ActiveWorkbook.Sheets(ActiveSheet.Name)
        individual_list.Range("E3").Value = portfolio.Range("B" & i).Value
        'Wait for Application to Calculate before pasting
        Application.Calculate
        Do While Not Application.CalculationState = xlDone
            DoEvents
        Loop
        'Identify the Last Row and copy it to clipboard, then paste in full collateral sheet
        row_cnt_tmp = individual_list.Range("O2").Value
        row_cnt_tmp = row_cnt_tmp + 7
        If i = 2 Then
            full_coll_list.Range("B2:V100000").ClearContents
            individual_list.Range("D8:X" & row_cnt_tmp).Copy
            full_coll_list.Range("B2").PasteSpecial xlPasteValues
        Else
            individual_list.Range("D8:X" & row_cnt_tmp).Copy
            full_coll_list.Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        End If
        
        
     End If
     

     template.Activate
Next i

individual_list.Range("D8:X" & row_cnt_tmp).Copy
full_coll_list.Range("B2:V").PasteSpecial xlPasteFormats
MsgBox "End Pull Collateral"
End Sub

Where the function GetSizePortfolio() is:

Function GetSizePortfolio() As Integer
Dim rng As Range, n#, b#

Set rng = ActiveWorkbook.Sheets("Portfolio").Range("B2:B1000")
On Error Resume Next
b = WorksheetFunction.CountBlank(rng)
n = rng.Cells.Count - b
On Error GoTo 0

MsgBox "The number of non-blank cells in column " & col & " is " & n
GetSizePortfolio = n


End Function

Have you tried putting the code in a separate function, so before exiting they must be completed? — Oran G. Utan, Aug 11 '23 at 13:13
Do you mind elaborating on how this would alter the execution? I am just not that familiar with how functions would be evaluated differently. Are you saying that if I put the portion that is executing too early in a function that it will wait to execute before all preceding calculations are complete? — Drew Winters, Aug 14 '23 at 15:37
I have had issues in pasting shapes across PowerPoint and Excel (with VBA of course) and I found a world of questions all with similar solutions: adding delays to force the copying (most of the answers explain it is due to the improvement of the machines through the years). I [proposed a solution](https://stackoverflow.com/a/76794690/18247317) for this particular issue, that works for me (and is limited to copy/paste). In your particular case I think that it should be enough something like ( `Dim xLAppL As Application: Set xLAppL = wKSh.Parent.Parent` `xLAppL .Calculate ` with wKSh input — Oran G. Utan, Aug 14 '23 at 17:29
Thanks for suggesting this, I tried it and it didn't work unfortunately. I have run the above macro several times and the cells that are not refreshed before copy-paste occurs change every time with no pattern so it really seems like an application/memory bug. Any other suggestions would be greatly appreciated. — Drew Winters, Aug 14 '23 at 20:56
I will try and reproduce somehow and do more testing. I found [this](https://stackoverflow.com/a/56036633/18247317) that, however related to pasting the conditional formatting, it still is a separated way of pasting. I still think that a similar principle should work in your case *if* it is caused by issues in the timing of the execution of tasks. — Oran G. Utan, Aug 16 '23 at 06:10
Thank you, will keep an eye out in case you're able to figure it out. — Drew Winters, Aug 16 '23 at 17:24

score 3 · Answer 1 · answered Aug 11 '23 at 13:55

3

The problem with using Application.Wait is that it doesn't just tell your VBA code to pause, it actually tells the whole Application (ie Excel itself) to pause for those 2 seconds ... hence it never actually gets an opportunity to complete the calculations.

Try replacing your code with this:

Application.Calculate
Do While Not Application.CalculationState = xlDone
    DoEvents
Loop

For more on DoEvents see the MS Docs

answered Aug 11 '23 at 13:55

JohnM

2,422
2
8
20

I tried this in the above and it is not resolving the issue of cells being pasted before the worksheets have calculated accordingly when cell E3 is updated via: individual_list.Range("E3").Value = portfolio.Range("B" & i).Value It is helpful to know that the initial Application.Wait was not helping though. – Drew Winters Aug 14 '23 at 15:47
Having seen more of your code, I assume the concern about calculating relates to the line immediately before the `DoEvents` loop ie `individual_list.Range("E3").Value = portfolio.Range("B" & i).Value`. Note there is nothing in this line that requires a calculation. The value is inserted directly into the cell. To test this, you could temporarily add the following line after that line `If individual_list.Range("E3").Value <> portfolio.Range("B" & i).Value Then Stop` – JohnM Aug 15 '23 at 06:41
To find where the actual problem lines, first comment-out the `On Error Resume Next` and run your code to see if it has any problems running. Next, either (or both) step through your code (ie using F8) while testing values of variables in the Locals window, or add various `Debug.Print` lines to display values of variables in the Immediate window. Look for values you weren't expecting at each step. You might also want to read about how to avoid using copy/paste in this [SO Q&A](https://stackoverflow.com/questions/51405731/avoiding-select-with-copy-and-paste-vba) – JohnM Aug 15 '23 at 06:47
I think you correctly identified the issue in your first comment, John. What happens is that when individual_list.Range("E3").Value = portfolio.Range("B" & i).Value the formulas in the individual_list sheet recalculate. This is not happening for random (it appears to be non-deterministic as it changes with with each time I either run or step through the Macro) iterations of the loop I shared. I am trying to force each individual_list sheet to calculate all formulas prior to copy and pasting (or setting the destination cells equal to the origin cells, if we want to avoid copy and paste) – Drew Winters Aug 15 '23 at 14:17
I tried the steps you suggested but they do not identify why individual_list sheet does not calculate after its E3 value is set. Stopping will just cause the Macro not to run, correct? I am hoping it will proceed just with all prior calculations completed. – Drew Winters Aug 15 '23 at 14:18

Oran G. Utan · Answer 2 · 2023-08-14T20:01:43.637

To clarify what I meant in the comments, this is what I would propose, however it is untested and I am not sure it is connected to your issue specifically, I just mean that, if the other solution proposed is a DoEvents and that the same solution is proposed for copy/paste issues between Excel and PowerPoint, the principle behind what that works for me for the copy/paste issue could be applied to this case as well, so the code would be something like the below:

Sub caLcuLateExcL(wrKs As Excel.Worksheet)
Dim xLAppL As Application: Set xLAppL = wrKs.Parent.Parent
 xLAppL.Calculate 'see here https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.calculate(method) for more info
End Sub

I used the App method, but you can use other elements of the tree of course with possible addition of Manual/Automatic forms of calculations inside. I was reluctant to post it as, once more, it is untested.

VBA not allowing calculation before copy and pasting

2 Answers2