Excel 2016 - Refresh All not working through Macro

[MM1963]

I have an Excel file with a tab that is linked to an Access table. Other tabs in the workbook have named formulas that refer to the data in the data range from the linked tab. The formulas are SUMIFS.
I have the workbook and all the sheets unprotected. I click on Data>Refresh All. Then at the bottom of the screen I see the following messages:
-Connecting to Datasource…
-Preparing worksheet…
-Calculating
The entire workbook calculates and the formulas return the correct values.
When I create a macro button to execute the Refresh All and I click the button, I see the file connecting to Datasource – Preparing worksheet – calculating, however the formulas don’t return a value. If I click in a cell containing the named formula, then press enter, the cell recalcs and displays the correct value.
Here is the code on the button.

Sub Button1_Click()
ActiveWorkbook.RefreshAll
End Sub

I don’t know what I’m missing. Why would a manual Refresh All work, but not through a macro?

 

[SkipVought]

Hi,

Sub Button1_Click()
   [b]Application.Volatile[/b]
   ActiveWorkbook.RefreshAll
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

...and I often had workbooks with queries to external databases and lots of internal calculations. In fact too many to let the workbook calculate automatically every time a change occurred.

So I controlled how the queries (and I had many queries to various dbs) to external databases refreshed via a macro and when the workbook would recalculate via a macro. I would often just recalc a specific sheet, based on the change circumstances.

Something that you might want to consider.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MM1963]

Hi Skip,
I added the line Application.Volatile but it still won't calculate. Even pressing F9 won't recalc the cells. If I click in the cell and press enter it does recalc correctly.
Any other options?

 

[SkipVought]

Save, Close workbook (possibly reboot)

Reopen and proceed with your button click. Observe what happens.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MM1963]

I saved and closed the workbook. I got out of Excel and rebooted. I went back into the Excel file and verified the code on the button. I ran the macro and still no values in these cells. I could see the spreadsheet connecting, preparing and calculating. If I click in the cell and press enter, or I manually go to Data-Refresh all, the formula calculates correctly. Very frustrating, any more ideas? I really need to get this working. Any help is really appreciated.

 

[SkipVought]

You also might consider changing the Table > External Data Properties > Connection Properties (to th RIGHT of Connection Name) > Usage TAB > Refresh Control... and UN-CHECK the Enable Background Refresh.

This is one of the reasons I discretely refresh each query using Query.Refresh BackgroundRefresh:=FALSE

I want each external query refresh to finish BEFORE another external query refresh and all queries to finish refreshing BEFORE I attempt to Recalculate the workbook.

In fact, if I'm sure of the precedents in my calculations, I might control the order and execution of the calculation of each sheet, but you must be very careful here!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MM1963]

I do have the "Enable Background Refresh" as UNCHECKED. I will be protecting the workbook and some sheets in the version of the file that users will access. So I will have code to unprotect then refresh all, then protect. I know unchecking the enable background refresh will ensure the refresh completes before my code protects the workbook and sheets.

 

[SkipVought]

What happens then, if you were to STEP through your button click code?

Put a BREAK in the first executable line of code. Then STEP line by line, observing what is happening or not on each line.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

I forgot, you are just refreshing all. Then calculation automatically starts.

Consider making Calculation Manual and the add a ThisWorkbook.Calculate to your code.

If that fails, consider moving to more discrete refresh code.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MM1963]

my button code is

Sub Button1_Click()
Application.Volatile
ActiveWorkbook.RefreshAll
End Sub

I have stepped through the code. When the refresh executes it takes about a minute as expected but the formulas don't calculate.

 

[SkipVought]

I’ve been doing similar things since the mid 1990s and I’m old and I can hardly remember things I did some 25 years ago.

But I do know that as far back as I can remember, in complex workbooks, of which have been legion, I did not use RefreshAll, but rather refreshed each query in a loop and all my queries had background refresh FALSE.

I deduce from that recollection, that I may have had a similar experience as you are currently involved in.

You may take that any way you choose.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MM1963]

Skip,
Thanks for all your input on this. I took your suggestion of making the calculation manual before doing he refresh. This has improved my results. Here is the code I'm using.

Sub Button1_Click()
Application.Calculation = xlManual
'Make workbook and sheet unprotected to do RefreshAll
ActiveWorkbook.Unprotect Password:="123"
Sheet15.Unprotect Password:="123"
ActiveWorkbook.RefreshAll
'Reprotect workbook and sheet
ActiveWorkbook.Protect Password:="123"
Sheet15.Protect Password:="123"
End Sub

At this point I go to the menu and choose Formulas> Calculation Options> Automatic
This returns the correct results for the entire workbook. However when I include the code: Application.Calculation = xlAutomatic
at the end of my macro I loose values again and my results are bad.
Am I using the right code to change the calculation method?
Thanks for your help.

 

[SkipVought]

What happens if you do an Application.Calculate at the end?

Do you ever have other workbooks open? You might try...

ThisWorkbook.Sheets.Select
ActiveSheet.Calculate

This selects all sheets and calculates ThisWorkbook.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MM1963]

I tried both suggestions, no luck. I did see it calculating during the macro process. However after the macro completed I went to the menu and choose Formulas> Calculation Options> Automatic. The spreadsheet calculated and the results where good. I don't know why it takes going to the menu and manually putting the formula calculations to automatic for this to work and why I don't get the same results by using a macro to change the calculation to automatic.

 

[combo]

Do you have the code and data that you try to refresh in the same workbook? If so, use rather ThisWorkbook instead ActiveWorkbook.
Refreshing external data and calculation mode are different things. You need to restore automatic calculation ([tt]Application.Calculation=xlCalculationAutomatic[/tt] before [tt]End Sub[/tt]).
BTW, the proper name of constant for manual calculation is [tt]xlCalculationManual[/tt]. xlManual is one of excel constants too, but the values match can be a coincidence.

combo

 

[MJV8198]

If I am understanding your problem, the data in the tables refresh but the formulas do not. I had a similar problem that I resolved in this thread:

Link

The answer is Turn off automatic Calculate, refresh data and manually turn on auto calculate. You can create a macro to turn off the auto calculate and run refresh but treturning to Automatic calculate must be dome manually. embedding a sub macro or even running a second macro will not work.