Excel Addin is intermittently crashing on opening new excel books while old books are open and functions are running

by

Summary

The Excel add-in is intermittently crashing when a new Excel book is opened while old books are open and functions are running. This issue is reproducible with a clean install of the add-in and is caused by the recalculation of functions across all workbooks when a new workbook is opened.

Root Cause

The root cause of the issue is the concurrent recalculation of functions across all workbooks when a new workbook is opened. This causes a high load on the add-in, leading to a 10-20% chance of crash. The number of open Excel books and the number of functions running per book increase the likelihood of the crash.

Why This Happens in Real Systems

This issue occurs in real systems due to the following reasons:

  • Concurrent function recalculation: When a new workbook is opened, Excel recalculates functions across all workbooks, causing a high load on the add-in.
  • Add-in reload: The add-in is reloaded for each new workbook, which can cause performance issues and increase the likelihood of a crash.
  • Shared runtime: The use of a shared runtime for the add-in means that all workbooks share the same resources, increasing the likelihood of a crash when multiple workbooks are open and functions are running.

Real-World Impact

The real-world impact of this issue is significant, causing:

  • Loss of add-in functionality: The crash disables the add-in across all Excel books, leaving users without access to critical features.
  • Degraded Excel experience: The crash and subsequent reload of the add-in slow down Excel performance, causing frustration for users.
  • Increased support requests: The issue is likely to generate a high volume of support requests, increasing the workload for IT teams.

Example or Code

// Example code to replicate the issue
// Create a new Excel add-in project using Yo Office Generator
// Add the CONTOSO.CLOCK() function to the add-in
// Create a spreadsheet with 3000 calls to the CONTOSO.CLOCK() function
// Open multiple copies of the spreadsheet in quick succession

How Senior Engineers Fix It

Senior engineers can fix this issue by:

  • Optimizing function recalculation: Implementing lazy loading or caching to reduce the load on the add-in during function recalculation.
  • Improving add-in architecture: Decoupling the add-in from the shared runtime or implementing a more efficient architecture to reduce the impact of concurrent function recalculation.
  • Error handling: Implementing robust error handling to prevent the add-in from crashing and to provide a better user experience.

Why Juniors Miss It

Junior engineers may miss this issue due to:

  • Lack of experience: Limited experience with concurrent programming and add-in development may lead to a lack of understanding of the potential issues.
  • Insufficient testing: Inadequate testing of the add-in under heavy loads and concurrent usage may not reveal the issue.
  • Overemphasis on functionality: Focusing too much on implementing features may lead to neglecting performance and scalability considerations.