Formula not updating, and worksheet not refreshing

Question

I'm trying to create a macro that publishes the past 3 months worth of data with a single run. To do this, I need to update a formula, refresh the worksheet a few times, run a macro to publish the values (addrow), and repeat for the past 3 mo. However my VBA code doesn't update the formula in cell G2 - due to this I'm not sure if it is refreshing the sheet (since only after the formula is updated will the values on the worksheet change after a refresh). Would really appreciate any insight on how to solve this.

My current output is just a repeat of the same days values over and over again.

Worksheets("Historical Close").Activate
Range("G2").Select
ActiveCell.FormulaR1C1 = "=TODAY()-60"
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
Call addrow

Worksheets("Historical Close").Activate
Range("G2").Select
ActiveCell.FormulaR1C1 = "=TODAY()-59"
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
Call addrow

Worksheets("Historical Close").Activate
Range("G2").Select
ActiveCell.FormulaR1C1 = "=TODAY()-58"
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
Call addrow

Worksheets("Historical Close").Activate
Range("G2").Select
ActiveCell.FormulaR1C1 = "=TODAY()-57"
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
Call addrow

What's the purpose of the multiple calls to `ActiveSheet.Calculate`? Seems to me that if you have an iterative process there must be a better way to accomplish it. — jsheeran, Jun 06 '23 at 13:36
Ah the reason for that is it's a very heavy sheet with bloomberg formulas - one refresh usually causes a lot of "requesting for data", two still has a few pending and three refreshes usually does it. please treat it as though there are only 3 ActiveSheet.Calculate — IJUT, Jun 06 '23 at 13:56

eren · Answer 1 · 2023-06-06T21:56:50.303

Seeing your example, this part just repeats itself with only one change and that is the subtraction

Worksheets("Historical Close").Activate
Range("G2").Select
ActiveCell.FormulaR1C1 = "=TODAY()-58"
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
ActiveSheet.Calculate
Call addrow

I suggest to use a simple for cycle for that. + i'd also get rid of ActiveSheet / Cell stuff see this.

Dim fromDay As Integer: fromDay = 60
Dim toDay As Integer: toDay = 1

Debug.Assert fromDay > toDay

For i = fromDay To toDay Step -1
    Sheets("Historical Close").Range("G2").FormulaR1C1 = "=TODAY() - " & CStr(i)
    CalculateAndWait("Historical Close")
    CalculateAndWait("Historical Close")
    CalculateAndWait("Historical Close")
    CalculateAndWait("Historical Close")
    CalculateAndWait("Historical Close")
    Call addrow
Next

To wait for calculations to finish you can add Application.Wait after each calculate, but i'd rather try with this procedure (also edited code above to use this procedure) Taking into consideration @GSerg comment i'd try someting like this (source):

private sub CalculateAndWait(sheetName as String)
    'max wait time for a calculation in seconds
    Const MAX_WAIT_TIME = 10

    Dim startTime as Double: startTime = Timer()
    Sheets(sheetName).Calculate
    Do While Not Application.CalculationState = xlDone 
        DoEvents

        If Timer() - startTime > MAX_WAIT_TIME Then Exit Do
    Loop
end sub

This should wait until all the calculations are done and only then continue so now you should be able to get rid of unnecessary calculations and leave only those that are really needed.

This works better thanks! However the problem now lies with sheets.calculate. Since the sheet requires a refresh --> wait for values to populate --> refresh --> wait for values to populate --> refresh then call autorun the above will populate a lot of value errors when publishing. Do you know of a way to work through the above problem so all the values published from each loop are actually populated? — IJUT, Jun 06 '23 at 14:18
@IJUT Sorry for late reply, i proposed a solution for 'wait' after each calculate. There is also a possibilty to use Application.Wait with some constant time (let's say 5seconds), but proposed solution is better imo. — eren, Jun 06 '23 at 21:10
@eren Apparently you've taken your waiting code from [here](https://stackoverflow.com/a/11277152/11683). It's not going to do much good as it [did not there](https://stackoverflow.com/questions/11277034/wait-until-application-calculate-has-finished#comment55095000_11277152). — GSerg, Jun 06 '23 at 21:21

Formula not updating, and worksheet not refreshing

1 Answers1