Application events, determine when Excel is really ready to read from

Question

I've been looking at allowing an application to link with Excel using either COM interop or Excel-dna. Ideally users can link ANY Excel workbook which may or may not contain VBA code. Typically operation would be

i) C# writes to Excel sheet

ii) Excel re-calculates

iii) Optionally, Excel may run VBA if any Worksheet_Change or Worksheet_Calculate events are included by user

iv) C# then reads back from Excel sheet which will contain updated data from calcs/VBA

Is there a FINAL Application event that notifies when everything is complete?

For example the Application level events include AfterCalculate() and SheetChange but one or both could fire which makes it difficult to know which one to react to for iv) above.

I've setup a small mock project in VBA, when changing a worksheet the application level events trigger in the following order

Worksheet_Calculate
mApp_AfterCalculate Calc ended at: 20/03/2020 13:23:29
Worksheet_Change Started
Worksheet_Change Ended
mApp_SheetChange Calc ended at: 20/03/2020 13:23:29 --> New Value: 20/03/2020 13:23:29

If there a simple way to know if mApp_AfterCalculate will be the final event or mApp_SheetChange?

Mock Project Code:

Sheet1:

Private Sub Worksheet_Calculate()
    Debug.Print ("Worksheet_Calculate")
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    Debug.Print ("Worksheet_Change Started")
    Call Setup

    Application.EnableEvents = False
    Sheet1.Range("F1").Value = Now
    Application.EnableEvents = True

    Debug.Print ("Worksheet_Change Ended")
End Sub

Class Module: clsAppEvents

Option Explicit

Private WithEvents mApp As Application

Private Sub Class_Initialize()
    Set mApp = Application
End Sub

Private Sub mApp_AfterCalculate()
    Debug.Print "mApp_AfterCalculate Calc ended at: " & Now
End Sub

Private Sub mApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Debug.Print "mApp_SheetChange Calc ended at: " & Now & " --> New Value: " & Sh.Range("F1").Value

End Sub

Module1:

Private mAppEvents As clsAppEvents

Public Sub Setup()
    If mAppEvents Is Nothing Then
        Debug.Print ("Setup initialized")
        Set mAppEvents = New clsAppEvents
    End If
End Sub

C. Augusto Proiete C. Augusto Proiete · Accepted Answer · 2020-03-20T21:59:12

From what I understood of your question, the fundamental problem is that you are trying to predict the calculation graph of Excel by assuming events are going to be executed in a certain order, and that they will always execute synchronously... Both of which you can't reliably predict 100% of the time.

My suggestion would be to use a different, more reliable, approach where you decouple writing from reading:

Write: On the C# code that writes to the spreadsheet, do just that. Write and don't wait for anything (and don't read anything)
Read: Also on the C# side, subscribe to Application.SheetChange and react there - i.e. Every time this event runs in the C# side, read the values you need from the spreadsheet.

That way, it doesn't matter who changed the spreadsheet... If it was your C# code, the VBA code, or the user manually changing it. You know that every time a change happens in the spreadsheet, will cause your C# code to run and give you the ability react (or not) depending if the change happened in a sheet you care about and also that the change happened in a range that you care about.

Pseudo-code how your Application.SheetChange would look like:

public void Application_SheetChange(object sheet, Range range) 
{ 
    if (range.Worksheet.Name != "SheetYouAreInterested")
    {
        return; // nothing to do
    }

    if (!range.IntersectsWith(rangeYouAreInterested))
    {
        return; // nothing to do
    }

    // Read the values that changed, etc.
}

Now, the order of events also is not important anymore, given that your event handler will keep getting executed every time a change occurs, so any stale values that you might have read before will eventually get refreshed by the last time the event runs.

Of course, the reading code should not make any changes to the sheet directly... It should only read, otherwise you'd be stuck in an infinite loop :D

Application events, determine when Excel is really ready to read from

1 Answers