search

OfficeDev / office-js

A repo and NPM package for Office.js, corresponding to a copy of what gets published to the official "evergreen" Office.js CDN, at https://appsforoffice.microsoft.com/lib/1/hosted/office.js.
https://learn.microsoft.com/javascript/api/overview
Other
670 stars 96 forks source link

Custom functions not registered when Excel is launched via COM Automation; any recalculation causes #NAME! or #VALUE! error. #4833

Open gmichaud opened 2 weeks ago

gmichaud commented 2 weeks ago

Your Environment

Expected behavior

Files opened via COM automation work seamlessly.

Current behavior

NAME! error shows up for all the custom formulas, even though side panel loads and ribbon is initialized. You can tweak initialization a bit by changing the order of operations and get custom functions to work, however if there's anything that triggers a recalculation during file opening then #VALUE! error will be displayed.

Steps to reproduce

  1. Unzip attached archive ExcelAutomationCustomFunctionsAdditionalScripts.zip

  2. Edit VBScript file extract in step 1 to change path to SampleWorkbookUsingCustomFunctions.xlsx file also included in ZIP file

  3. Open workbook manually, and confirm that the =VELIXOVERSION() and other =SI... custom functions show up fine when entering them in a cell

Screenshot 2024-08-28 at 9 57 17 AM
  1. Close every Excel window
  2. Run Test.vbs script
  3. Try the =VELIXOVERSION() and =SI... functions again -- they are not available, but you see the Velixo toolbar and ribbon there.
Screenshot 2024-08-28 at 9 57 04 AM
  1. Close Excel
  2. Run Test2.vbs script
  3. Try the =VELIXOVERSION() and =SI... functions again -- they are available and working as expected (only difference is that Application.Visible was set to TRUE before opening the workbook)
  4. Close Excel
  5. Run Test3.vbs script
  6. Check cell A1 -- it shows #VALUE! error.
Screenshot 2024-08-28 at 9 47 58 AM

I inspected the side-panel console and our telemetry and we don't see anything being logged, so assuming function registration is not yet completed at this stage. It sometimes happen that Excel triggers a recalculation on file open; if custom functions are involved, the #VALUE! error will be shown to user as well. Users can then manually force a recalculation, but it looks like a bug to them.

I think Excel should defer calculation or keep waiting until the add-in has been fully initialized in these scenarios.

Link to live example(s)

Recording: https://www.loom.com/share/2db0848bfc8543d98195890b0332afe1

Context

We initially discovered this issue when trying to use a .NET app that uses Excel COM automation to automate Excel (it opens files and sets a named range to a value), but to simplify troubleshooting we have been able to narrow this down to a simple scenario involving VBScript.

Useful logs

Console doesn't show any error message.

gmichaud commented 2 weeks ago

I have updated the bug description above with the updated sample

Here's an updated set of test macros:

ExcelAutomationCustomFunctionsAdditionalScripts.zip

I have also simplified the test workbook down to a single custom function

Screenshot 2024-08-28 at 9 47 58 AM

I inspected the side-panel console and our telemetry and we don't see anything being logged, so assuming function registration is not yet completed at this stage. It sometimes happen that Excel triggers a recalculation on file open; if custom functions are involved, the #VALUE! error will be shown to user as well. Users can then manually force a recalculation, but it looks like a bug to them.

I think Excel should defer calculation or keep waiting until the add-in has been fully initialized in these scenarios.

shanshanzheng-dev commented 2 weeks ago

Hi @gmichaud Thanks for reporting this issue, we'll take a look and report back if we have a suggestion for you.