How to refresh cells using VBA?

Question

I am using all the solutions that appear in:

Getting Excel to refresh data on sheet from within VBA

ActiveSheet.EnableCalculation = False  
ActiveSheet.EnableCalculation = True

or

Application.Calculate

or

Application.CalculateFull

None of them works in Excel 2010. When I go to the cell and right click refresh it works. How can I refresh within VBA?

Sheets("Name_of_sheet").Range("D424").Refresh raises an

exception 438

Questions:

How can I make the script support Excel 2003, 2007, 2010?
How can I choose the source file to refresh from using VBA?

EDIT:

I want to simulate a right mouse click and choose refresh in the menu in worksheet 3. That is the entire story.
I work on an Excel file created 10 years ago. When opening in Excel 2010, I can go to a cell and right click on it and choose refresh and then choose the .txt file to refresh from. I am trying to do it automatically within VBA.

how are you loading the spreadsheet and also are you trying to refresh a s/s from code in another s/s? Please give more detail — InContext, Nov 22 '12 at 13:47
if you are enabling calculations at the application level but opening a spreadsheet from the original then you might be calculating the wrong workbook. — InContext, Nov 22 '12 at 13:51
You need to show us your code. All of the methods that you list ***DO*** work, so you are doing something else wrong. — RBarryYoung, Nov 23 '12 at 02:32
@RBarryYoung I work on excel file which created 10 years ago. When opening it in excel 2010, I can go to a cell and right click on it and choose refresh and then choose the .txt file to refresh from, and it works just fine. Now when I try to do it automatically within VBA all the code above doesn't help. thanks — 0x90, Nov 23 '12 at 03:06

Anirudh Ramanathan · Accepted Answer · 2012-11-22T13:49:32.243

2

You could try using Application.Calculation

Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic

edited Nov 22 '12 at 13:49

answered Nov 22 '12 at 13:42

Anirudh Ramanathan

46,179
22
132
191

doesn't work as well... maybe it is because my second question. How can I choose using VBA the source file to refresh from? – 0x90 Nov 22 '12 at 13:46
@0x90 You can try `Application.Calculate`. I don't quite understand your second question. – Anirudh Ramanathan Nov 22 '12 at 13:47
How the cells now where to take the data from? so it will know what to put in it? – 0x90 Nov 22 '12 at 13:50
Could you post the other parts of the code, or explain your workflow? – Anirudh Ramanathan Nov 22 '12 at 13:55

score 2 · Answer 2 · edited Nov 22 '12 at 13:48

2

For an individual cell you can use:

Range("D13").Calculate

OR

Cells(13, "D").Calculate

edited Nov 22 '12 at 13:48

0x90

39,472
36
165
245

answered Nov 22 '12 at 13:46

InContext

2,461
12
24

What version of Excel are you using? – InContext Aug 02 '18 at 15:38

0x90 · Answer 3 · 2012-11-29T17:24:43.263

1

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")

edited Nov 29 '12 at 17:24

answered Nov 29 '12 at 05:52

0x90

39,472
36
165
245

1

i had this problem today, and tried all of the other answers on this page. then i saw this, and it reminded me that i can just record macros in excel. man...excel is a PITA. – sion_corn Jul 10 '14 at 19:10
@sion_corn that is indeed an option to record a macro... :) – 0x90 Jul 10 '14 at 21:00

score 1 · Answer 4 · answered Feb 16 '16 at 17:53

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

score 1 · Answer 5 · edited Aug 03 '22 at 07:49

1

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>

edited Aug 03 '22 at 07:49

Ike

9,580
4
13
29

answered Sep 05 '21 at 11:16

XYZLOL

11
2

score 0 · Answer 6 · edited Mar 09 '21 at 15:12

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()"

score 0 · Answer 7 · answered Aug 02 '22 at 20:21

0

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

works perfectly for me

answered Aug 02 '22 at 20:21

user19260138

1

Rocky Scott · Answer 8 · 2023-02-09T15:05:07.660

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.

How to refresh cells using VBA?

8 Answers8

Linked