CODEWITHSUNDEEP
excelvba
0x90
0x90

Reputation: 40982

How to refresh cells using VBA?

I've attempted various solutions from Stack Overflow (How to refresh ALL cell through VBA, Getting Excel to refresh data on sheet from within VBA) to refresh cells in Excel VBA without success in Excel 2010.

These methods include toggling the calculation state of the active sheet (ActiveSheet.EnableCalculation = False followed by ActiveSheet.EnableCalculation = True), and using Application.Calculate and Application.CalculateFull.

Unfortunately, none have worked. Interestingly, manually refreshing a cell via right-click works fine. However, trying to programmatically refresh a cell with Sheets("Name_of_sheet").Range("D424").Refresh triggers exception 438.

Questions:

  1. How can I adapt the VBA script to work across Excel 2003, 2007, and 2010?
  2. How can I programmatically select a source file for refreshing data?

Edit:

Upvotes: 5

Views: 125946

Answers (8)

Rocky Scott
Rocky Scott

Reputation: 466

I have a long "macro" in a workbook > 20 MB, tens of thousands of lines, that calls a dll written in Fortran. None of these methods worked:

Call Application.Calculate
Call Application.CalculateFull
Call Application.CalculateFullRebuild
Re-entering the formula
Range.Dirty

Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic

ThisWorkbook.ActiveSheet.EnableCalculation = False
ThisWorkbook.ActiveSheet.EnableCalculation = True

This worked:

On Error Resume Next
Application.SendKeys "{F2}{Enter}{NumLock}"  'SendKeys turns off NumLock for some reason
On Error GoTo 0

This even worked when a chart was selected.

Upvotes: 0

XYZLOL
XYZLOL

Reputation: 11

You can force excel to recalculate a cell or range of cells by marking the cell/range as dirty.

Example :

' Recalculate Column D4 to D24
Sheets("Name_of_sheet").Range("D4:D24").Dirty

or

' Recalculate Cell D4<br>
Sheets("Name_of_sheet").Range("D4").Dirty<br>

Upvotes: 1

user19260138
user19260138

Reputation: 1

Cells(x, y).Select
ActiveCell.FormulaR1C1 = Selection.Value

works perfectly for me

Upvotes: 0

Scott
Scott

Reputation: 1

Application.Calculate didn't work for my function. Not even when followed by DoEvents.

What I found that works is to re-enter the formula in the cell. A simple way to get the formula is to start recording a macro, use F2 to edit the cell, then press enter. The macro will make a great copy of the function text with all needed quotes.

Below is an example.

Sheets("Name_of_sheet").Range("D424").FormulaR1C1 = "=now()"

Upvotes: 0

LeeF
LeeF

Reputation: 9

just a reminder;

be careful when using

Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic

this sets the entire excel application to calculate formula's either automatically or manually. If you use 

Application.Calculation = xlCalculationManual

you'll notice your automatic formulas no longer work.

cheers

Upvotes: 1

0x90
0x90

Reputation: 40982

I finally used mouse events and keystrokes to do it:

Sheets("worksheet34").Select
Range("D15").Select
Application.WindowState = xlMaximized
SetCursorPos 200, 600 'set mouse position at 200, 600
Call mouse_event(MOUSEEVENTF_RIGHTDOWN, 0, 0, 0, 0) 'click left mouse
Application.SendKeys ("R")

Upvotes: 1

Anirudh Ramanathan
Anirudh Ramanathan

Reputation: 46728

You could try using Application.Calculation

Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic

Upvotes: 2

InContext
InContext

Reputation: 2501

For an individual cell you can use:

Range("D13").Calculate

OR

Cells(13, "D").Calculate

Upvotes: 3

Related Questions