Formula calculations too slow

0
votes

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!

2
vbaexcelcopying
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
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
@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+<starting-number> 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
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
@Xabier I edited the question, I hope it's a little bit clearer now - user3158907

2 Answers

0
votes

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

0
votes

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.