1

Say I have a workbook that I do not want to be updated unless an update procedure is called specifically. That is to say, I want to stop any form and kind of automatic updates - I don't want formulas to update and I don't want any data connections to update.

What's the difference between applying these two?

Private Sub Workbook_Open()
    Application.Calculation = xlCalculationManual
End Sub

vs

Private Sub Workbook_Open()
    Worksheet(1).EnableCalculation = False    
End Sub

The workbook only has 1 sheet.

MSDN says that when EnableCalculation is set to false, you cannot request a recalculation. Does that mean pressing F9 or the Refresh button on the Data tab doesn't work (which would be ideal)?

Would it then be correct to assume that EnableCalculation blocks all requests for recalculation, where xlCalculation just switches between manual/automatic mode?

Vegard
  • 3,587
  • 2
  • 22
  • 40
  • **MSDN says that when EnableCalculation is set to false, you cannot request a recalculation.** So yup, it does mean exactly what you asked and it can indeed be really practical! ;) And yes it works regardless of the calculation mode, you won't be able to recalculate formulas in there while it is set to `False`, you need to change it back first and calculate again after! – R3uK Jun 23 '15 at 09:18
  • I've been testing out some variations, and it seems that Workbook.RefreshAll works even when EnableCalculation is set to false. So seemingly, EnableCalculation does not prevent external data connections from being updated. – Vegard Jun 23 '15 at 09:26
  • Probably not, but External Data isn't really a kind of calculation so not really astonishing! Are you trying to block it too? – R3uK Jun 23 '15 at 09:34
  • Ideally, I want to prevent any update or calculation prior to the execution of a specific procedure, yes. But it looks as though it may be hard... – Vegard Jun 23 '15 at 09:38
  • I just take a look in the object browser and indeed, it might be hard for Connections... Take a look there, might be some interesting infos (it says best solution is to deactivate ribon button) : http://stackoverflow.com/questions/21561446/how-to-block-disable-refresh-all-from-the-excel-2007-ribbon For links (might not be data connection, the parameter is stock in workbook class : `Application.DisplayAlerts = False ThisWorkbook.UpdateLinks = xlUpdateLinksNever`) and `UpdateRemoteReferences` – R3uK Jun 23 '15 at 09:53
  • `Property ConnectionsDisabled As Boolean, **read-only**, Member of Excel.Workbook` and `Sub EnableConnections()` – R3uK Jun 23 '15 at 10:00
  • Thanks, I'll look into those! – Vegard Jun 23 '15 at 10:44

2 Answers2

2

You can use the BeforeRefresh event and its Cancel argument to prevent refreshes. Those events aren't exposed automatically, so you have to create a custom class module and declare a QueryTable variable WithEvents. In the class CQtEvents

Private WithEvents mqt As QueryTable
Private mbPreventRefreshes As Boolean

Public Property Let PreventRefreshes(ByVal bPreventRefreshes As Boolean): mbPreventRefreshes = bPreventRefreshes: End Property
Public Property Get PreventRefreshes() As Boolean: PreventRefreshes = mbPreventRefreshes: End Property
Public Property Set qt(ByVal qt As QueryTable): Set mqt = qt: End Property
Public Property Get qt() As QueryTable: Set qt = mqt: End Property

Private Sub qt_BeforeRefresh(Cancel As Boolean)

    Cancel = Me.PreventRefreshes

End Sub

I set the Cancel argument to another property of the class. You need to instantiate the class, keep it in scope, and hook up the events.

Sub MyUninteruptableProcedure()

    Dim clsQtEvents As CQtEvents

    Set clsQtEvents = New CQtEvents
    Set clsQtEvents.qt = Sheet1.ListObjects(1).QueryTable
    clsQtEvents.PreventRefreshes = True

    'do uninteruptable stuff

    Set clsQtEvents = Nothing 'unhook the events

End Sub
Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
  • 1
    I haven't had the time to attempt an implementation yet, but I will assume that it works purely on the basis of not really knowing how any of that works. Have a checkmark. – Vegard Jun 25 '15 at 16:59
  • did i miss it, so, what's the difference between Application.Calculation and EnableCalculation? – StackExploded Apr 17 '22 at 19:48
  • `EnableCalculation` allows or prevents any calculation from happening. `Application.Calculation` determines whether calculations happen automatically. – Dick Kusleika Apr 18 '22 at 17:51
0

I can't get a viable direct fix for this, so the workaround is to set sheet protection in combination with unlocked cells to allow editing but disallow external data updates.

VBA unprotects the sheet when it's safe to pull an update.

Vegard
  • 3,587
  • 2
  • 22
  • 40