1

I have a large workbook from which I am trying to build reports, including multiple graphs. The workbook has to be in Manual Calculation mode to work. I have compartmentalized the calculations into different sheets, and written vba scripts to run calculations on different combinations of sheets. The problem I am running in to is that after I run these scripts, the graphs that I have will not update. The data does update, and if I manually go in to the graph and "select data" and re-select the same data then they will update. Is there a way I can automate this at the end of my script? I have tried different suggestions on different forums, but nothing seems to work. Here is the code I am currently working with, but it will not update the graphs:

Sub Calculate1()

  Sheets("Sheet 1").Calculate 'Sheet with calculations
  Sheets("Sheet 2").Calculate 'Sheet referencing final numbers from sheet 1, and displaying graphs

  Dim co As ChartObject
  For Each co In Sheets("Sheet 2").ChartObjects
     co.Chart.Refresh
     DoEvents
  Next co
End Sub

Thanks for taking the time to look! I am running Excel for Mac 2016. One suggestion that has been made on other forums is to momentarily set the calculation mode to automatic, but this is not an option for me, as that will crash the program.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
mcflame
  • 38
  • 5

2 Answers2

1

This snippet should work:

' Force the charts to update
Set sht = ActiveSheet
For Each co In sht.ChartObjects
    co.Activate
    For Each sc In ActiveChart.SeriesCollection
        sc.Select
        temp = sc.Formula
        sc.Formula = "=SERIES(,,1,1)"
        sc.Formula = temp
    Next sc
Next co

Credits: https://stackoverflow.com/a/11581258/2707864

This might also work (YMMV):

Worksheets("Sheet 2").Cells.WrapText=False 
' Run your calculations
Worksheets("Sheet 2").Cells.WrapText=True

Credits: https://stackoverflow.com/a/7211065/2707864

See also

Refresh all charts without blinking (and this comment).

Excel chart won't update

sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185
  • Is there any way to do this without using the `.Select` method on the `sc` object? I would prefer to have the sheet protected, and then select will not work. Otherwise this works great! I must have overlooked that comment... Thanks for the re-direct. – mcflame Feb 22 '18 at 21:28
  • @mcflame - I suggest you try it. It doesn't look all that important. – sancho.s ReinstateMonicaCellio Feb 23 '18 at 08:34
  • I removed the sc.select, and it does work without the sheet protected. But with the protection (even without protecting objects) now I am getting a Run-time error for the sc.Formula method. I can't understand why this would be affected by the worksheet being protected. – mcflame Feb 23 '18 at 16:56
  • @mcflame - Me meither, and I can't try right now, I am currently working under Ubuntu. – sancho.s ReinstateMonicaCellio Feb 23 '18 at 19:06
  • No problem. Thanks for the help! Right now I just run `ActiveSheet.Unprotect` and `ActiveSheet.Protect` on either end of the refresh. Works fine, just wish there was a more elegant solution. – mcflame Feb 23 '18 at 19:12
0

You should be able to add co.calculate after your loop. You might also just be able to change Application.Calculation = xlAutomatic before your loop, and then change back to Application.Calculation = xlManual after the loop.

Jordan
  • 148
  • 1
  • 11
  • The `.Calculate` method does not seem to be available for ChartObjects. I get a "Compile error: Method or data member not found" message. And switching back into automatic calculation makes the process take over 5 minutes, or crash excel--the reason I am in Manual calculation to begin with. – mcflame Feb 22 '18 at 20:37
  • Can you calculate the entire worksheet instead of the chart object? Or how about chart.Refresh? – Jacob H Feb 22 '18 at 20:44
  • Sorry if my first post is confusing. "Sheet 1" has a large set of calculations from a raw data set. "Sheet 2" takes only a small set of significant values from this sheet, displays it in series of presentable tables, and an associated charts. So the `Sheets("Sheet 2").Calculate` line accomplishes what you are suggesting, but this does not update the chart. I have also tried calculating "Sheet 2" a second time and it does not update the charts. – mcflame Feb 22 '18 at 20:49