Formula calculations too slow

Question

I want to write an Excel macro: I have a few calculations with a starting value. There are over 100 of different starting numbers and I want to automate copying the starting value in the right cell, some other cells have formulas which read this value and work with it. These formulars are fetching some data from the web (afaik), calculations in some other sheets and it takes some seconds to calculate. The cells in the sheet I'm working are just links to other cells in other sheets, so the formular isn't directly in the "working-sheet" (sheet #1). I don't know what these formulas exactly do, they are not from me. For each of the starting numbers I want to copy the results into a another sheet.

Private Sub CommandButton1_Click()
Application.Calculation = xlCalculationManual

For i = 2 To Sheets(2).Range("A1048576").End(xlUp).Row
    Sheets(1).Cells(5, 3).Value = Sheets(2).Cells(i, 3).Value

    Application.CalculateFull

    Sheets(2).Cells(i, 4).Value = Sheets(1).Cells(7, 3).Value
    Sheets(2).Cells(i, 5).Value = Sheets(1).Cells(8, 3).Value
    Sheets(2).Cells(i, 6).Value = Sheets(1).Cells(9, 3).Value
    Sheets(2).Cells(i, 7).Value = Sheets(1).Cells(10, 3).Value
    Sheets(2).Cells(i, 8).Value = Sheets(1).Cells(11, 3).Value
    Sheets(2).Cells(i, 9).Value = Sheets(1).Cells(12, 3).Value
    Sheets(2).Cells(i, 10).Value = Sheets(1).Cells(15, 3).Value
    Sheets(2).Cells(i, 11).Value = Sheets(1).Cells(16, 3).Value
Next i

Application.Calculation = xlCalculationAutomatic

i = 0
End Sub

So I copy from the Sheet(2) the starting-number (C-column) into the Cell(C5) of Sheet(1). When the Value of the Cell(C5) in Sheet(1) is changed, the values in Sheet(1).Range(C7:C16) are getting recalculated. I copy the new calculated values back into Sheet(2), next into the row of the proper starting-number. So Sheet(1).Range(C7:C16) (except of two values) in the Sheet(2).Range(Di:Ki) (i, index for the starting-number). And this looped for every starting-number.

So the Range(C7:C16) holds the results. My problem is, that the macro doesn't wait for the calculation to be finished. All cells in the range(Di:Ki) are getting the same values (the values which are in the range(C7:C16) before I start the macro).

I tried it in different ways, as seen with Application.CalculateFull, with

 Do Until Application.CalculationState = xlDone
        DoEvents
    Loop

and also with a Worksheet_Calculate() sub. I also tried Worksheet_Change() but it doesn't fetch the formula calculations.

When I use simple formulas like basic addition etc. it does work: E.g. Formula for Sheet(1).C7 is C5+1 (starting-number+1), I'll get in Sheet(2)

Starting Number - Result from Sheet(1).C7
1 - 2
2 - 3
3 - 4

Maybe someone can help me out? Thanks in advance!

That seems like a lot of calculations. Can you not fill in all values and then perform a calculation so that they can all be done in one instance?. Then you should be able to pick up updated values — Zac, Mar 06 '18 at 14:56
Your code does exactly what you mention your problem is, ie it copies C7:C16 to all the rows in Sheet(2), it isn't that its not waiting for calculations to finish. If you show us what you mean by `When I use simple formulas like basic addition etc. it does work` it might help formulate an answer? Also where are your starting values and where do they need to be copied into, for the formulas to work? — Xabier, Mar 06 '18 at 15:09
@Xabier Ok I'll try. My sheets: `Calculation(1) - Results(2) - various other` The Results(2) format `Starting Number - Result 1 - Result 2 - Result++` I copy the starting number from Results(2) to Calculation(1). A new value in the cell in Calc(1) starts the calculations. Then I copy the calculation results from Calculation(1) to the `Result x` cells of the Results(2) sheet. This procedure for each starting number. When I just use a `1+` formula instead of the slow formulas I'll get for the Result 1 for starting-number 1 - 1+1=2, for the starting-number 2 -1+2=3, etc. — user3158907, Mar 06 '18 at 15:25
Ok, so you get the value from sheet Results(2), on which Range/Cell/Column? And then you copy that value to the Sheet Calculation(1), again to which Range/Cell? and where are you supposed to get the new value? You say in cell Calc(1), but that isn't a possible range... Sorry but you need to be more specific about it, and even better if you edit your original question with all these details. — Xabier, Mar 06 '18 at 15:36
@Xabier I edited the question, I hope it's a little bit clearer now — user3158907, Mar 06 '18 at 15:53

score 0 · Answer 1 · answered Mar 06 '18 at 14:57

0

Try forcing the screen t update with these fancy tricks

ActiveSheet.Calculate
ActiveWindow.SmallScroll
Application.WindowState = Application.WindowState

Borrowed from

Force a screen update in Excel VBA

answered Mar 06 '18 at 14:57

Doug Coats

6,255
9
27
49

Xabier · Answer 2 · 2018-03-06T17:31:25.697

If I understand your issue properly, then I believe the following will do what you expect, it will take the value from Sheets(2) in Column C, and pass that value into Sheets(1) cell C5, this will make your formulas do some calculations and then it will take the values from C7:C16 and put them into Sheets(2) from Columns D to M in the same row it took the initial values from:

Private Sub CommandButton1_Click()
Dim i As Long
Application.Calculation = xlCalculationAutomatic
    For i = 2 To Sheets(2).Cells(Sheets(2).Rows.Count, "A").End(xlUp).Row
    'loop from row 2 to last on Sheets(2)
        Sheets(1).Cells(5, 3).Value = Sheets(2).Cells(i, 3).Value
        'add value from column C Sheets(2) to C5 of Sheets(1) for the formula calculations to take place
        Sheets(2).Cells(i, 4).Value = Sheets(1).Range("C7").Value
        'get the value from C7 and pass it to the relevant row in Sheets(2) on column D
        Sheets(2).Cells(i, 5).Value = Sheets(1).Range("C8").Value
        'get the value from C8 and pass it to the relevant row in Sheets(2) on column E
        Sheets(2).Cells(i, 6).Value = Sheets(1).Range("C9").Value
        'get the value from C9 and pass it to the relevant row in Sheets(2) on column F, and etc below
        Sheets(2).Cells(i, 7).Value = Sheets(1).Range("C10").Value
        Sheets(2).Cells(i, 8).Value = Sheets(1).Range("C11").Value
        Sheets(2).Cells(i, 9).Value = Sheets(1).Range("C12").Value
        Sheets(2).Cells(i, 10).Value = Sheets(1).Range("C13").Value
        Sheets(2).Cells(i, 11).Value = Sheets(1).Range("C14").Value
        Sheets(2).Cells(i, 12).Value = Sheets(1).Range("C15").Value
        Sheets(2).Cells(i, 13).Value = Sheets(1).Range("C16").Value
    Next i
End Sub

NOTE:

I have removed the line Application.Calculation = xlCalculationManual as this would cause your formulas not to calculate anything until you change it back to Application.Calculation = xlCalculationAutomatic. If this does not work, then it might be worth investigating what the actual formulas are doing, as I've tested this and it does what you expect.

Thanks for the answer. That was also my first idea to solve the problem. But with your solution I can see how the Value in Sheets(1).Range("C5") changes, but not the values in Range("C7:C16"). It seems like the numbers in C5 are changed to fast for C7:C16. So in Sheet(2) all the result values are the same.. — user3158907, Mar 06 '18 at 17:44
Didn't work. It seems like Excel stopped doing stuff during Application.Wait — user3158907, Mar 06 '18 at 18:00
@user3158907 please could you share how your formulas work, as without that I cannot replicate the issue? If maybe you could upload a sample workbook and comment a link to it here, then I will be able to have a better look at it... — Xabier, Mar 07 '18 at 09:16
in Sheet(1) in Range("C7:C16") are just "Formulas" which copy results from Sheet(3). In Sheet(3) happens the calculation. The formula for e.g. Range("C7") is `='Sheet3'!P14`. But it doesn't change if I try to get the Value from Sheet(1).Range("C7") or from Sheet(3).Range("P14"). In Range("P14") there is a "real" formula which calculates something. I think the problem is, that somewhere online data is fetched from a Reuters feed - which is slowing everything down. The excel file is not from me, I just need to make these adjustments. The file just works in a set up environment I can use. — user3158907, Mar 07 '18 at 12:43

Formula calculations too slow

2 Answers2