Getting Excel to refresh data on sheet from within VBA

Question

How do you get spreadsheet data in Excel to recalculate itself from within VBA  without the kluge of just changing a cell value

User · Answer

I had an issue with turning off a background image (a DRAFT watermark) in VBA. My change wasn't showing up (which was performed with the Sheets(1).PageSetup.CenterHeader = "" method) - so I needed a way to refresh. The ActiveSheet.EnableCalculation approach partly did the trick, but didn't cover unused cells.

In the end I found what I needed with a one liner that made the image vanish when it was no longer set :-

Application.ScreenUpdating = True

User · Answer

You might also try   Application CalculateFull   or  Application CalculateFullRebuild   if you don t mind rebuilding all open workbooks  rather than just the active worksheet   CalculateFullRebuild rebuilds dependencies as well

User · Answer

After a data connection update  some UDF s were not executing   Using a subroutine  I was trying to recalcuate a single column with   Sheets  mysheet   Columns  D   Calculate   But above statement had no effect   None of above solutions helped  except kambeeks suggestion to replace formulas worked and was fast if manual recalc turned on during update   Below code solved my problem  even if not exactly responsible to OP  kluge  comment  it provided a fast reliable solution to force recalculation of user-specified cells   Application Calculation   xlManual DoEvents For Each mycell In Sheets  mysheet   Range  D9 D750   Cells     mycell Formula   mycell Formula Next DoEvents Application Calculation   xlAutomatic

User · Answer

The following lines will do the trick   ActiveSheet EnableCalculation   False   ActiveSheet EnableCalculation   True     Edit   The  Calculate   method will not work for all functions  I tested it on a sheet with add-in array functions   The production sheet I m using is complex enough that I don t want to test the  CalculateFull   method  but it may work

User · Answer

This should do the trick      recalculate all open workbooks Application Calculate   recalculate a specific worksheet Worksheets 1  Calculate    recalculate a specific range Worksheets 1  Columns 1  Calculate

User · Answer

Sometimes Excel will hiccup and needs a kick-start to reapply an equation  This happens in some cases when you are using custom formulas   Make sure that you have the following script  ActiveSheet EnableCalculation   True   Reapply the equation of choice   Cells RowA ColB  Formula   Cells RowA ColB  Formula   This can then be looped as needed

[vba] Getting Excel to refresh data on sheet from within VBA

Examples related to vba

Examples related to excel