Change Application.Calculation in different Excel process

Question

In a VBA subroutine I write values to another "Target" workbook that runs in a separate Excel process, like this:

Set xlWB = GetObject(STR_TARGET_WORKBOOK_FILENAME)
xlWB.Worksheets(STR_TARGET_SHEET).Range("B1").value = Now()

I want to suspend calculation in the Target workbook while I write values to it. If it were running in the same Excel process as this VBA I could just do Application.Calculation = xlCalculationManual.

How can I get and set the Application.Calculation mode in the Excel process in which the Target workbook is running?

xlWb will also have an application object. xlWb.Application.Calculation = xlCalculationManual — Sorceri, Mar 11 '21 at 20:03
@Sorceri – that does it! Want to post as answer I can accept? — feetwet, Mar 11 '21 at 20:28

score 0 · Accepted Answer · answered Mar 23 '21 at 20:55

The external workbook object will also have a reference to its Application, so we can simply do:

Dim xlWB As Object
Dim calcMode As Integer

Set xlWB = GetObject(STR_TARGET_WORKBOOK_FILENAME)
' Get the Target's calculation mode
calcMode = xlWB.Application.Calculation
' Suspend calculation in the Target's Excel process
xlWB.Application.Calculation = xlCalculationManual
' ... do stuff to xlWB
' Restore whatever calculation mode was in that process
xlWB.Application.Calculation = calcMode

Change Application.Calculation in different Excel process

1 Answers1