1

I've created an Excel add-in using Yeoman and it has taskpane and Custom functions. For my custom functions I use a dialog box using displayDialogAsync method to open the dialog box and display a list of data which user can select and that selected value is then sent to Excel using messageParent. where It takes the value for example - "Hello" is the value selected and sent from Dialog box and displayed in the cell where Excel Custom Functions was invoked.

This works perfectly fine on Excel web but on Excel desktop it shows the dialog box and when the user selects the value it's not displayed back in Excel Desktop application.

Here is my code for using the displayDialogAsync and saving the value in Excel -

var dialog;
//invoc is the cell address where the custom function is called
// arrLocalStorage is some data sent through query parameters which are displayed on dialog box
    Office.context.ui.displayDialogAsync(
        `https://azure-storage-container-url.net/dialog.html?arrToSend=${arrLocalStorage}`, 
        { height: 50, width: 50, displayInIframe: true },
        function (asyncResult) {
          dialog = asyncResult.value;
          dialog.addEventHandler(Office.EventType.DialogMessageReceived, (arg) => {
            dialog.close();
            let msgFromDialog = arg.message;
            Excel.run(async (context) => {
              var ws = context.workbook.worksheets.getActiveWorksheet();
              var range = ws.getRange(invoc);//invoc is cell address where custom function is called
              range.values = [[msgFromDialog]];
              await context.sync();
            });
          });
        }
      );

I've tried to look at why this was causing issue but on excel desktop I can only find this in the console of the page of the dialog box - Uncaught (in promise) TypeError: Cannot read properties of undefined (reading 'register') excel-win32-16.01.js.

Does anyone know what can be done to get this working?

user21064709
  • 11
  • 2
  • When code works in Excel on the Web, but not Excel desktop, that's probably a bug. If you don't get an answer from the Stack Overflow community a couple of days, consider opening a bug on the [office-js repo](https://github.com/OfficeDev/office-js/issues/new/choose). – Rick Kirkham Jan 23 '23 at 18:57
  • Any chance you can include your code in your question? – Jakob Nielsen-MSFT Jan 23 '23 at 19:00
  • Okay I'll update the question – user21064709 Jan 24 '23 at 04:32
  • @JakobNielsen-MSFT I've updated the question to include code – user21064709 Jan 24 '23 at 06:13
  • I'm wondering if it has to do with `invoc` being a `range` where custom function is called from. Can you try doing `console.log(msgFromDialog)` instead of `range.values = [[msgFromDialog]];` and report back if that works correctly>? – FreeSoftwareServers Jan 24 '23 at 21:41
  • @FreeSoftwareServers `console.log(msgFromDialog)` doesn't work on Excel Desktop but on Excel Web, I can see the values where `console.log(msgFromDialog) = Hello` ( Hello is the value I selected from Dialog Box) The value of invoc is Sheet1!B3 where Excel Custom Function was invoked. – user21064709 Jan 25 '23 at 04:29
  • Are you using the Custom Functions runtime or the Shared Runtime? There are a number of limitations in the Custom Functions runtime, so you might want to try with the Shared Runtime. – Jakob Nielsen-MSFT Jan 25 '23 at 15:35
  • I'm using shared runtime @JakobNielsen-MSFT – user21064709 Jan 25 '23 at 15:39
  • The code you have above, do you call that from the custom function code? If you want to change the value of the cell that is calling the custom function, why not just have the custom function return the value instead of setting the value with the range.values API? – Jakob Nielsen-MSFT Jan 25 '23 at 16:18
  • Yes, I do call the dialog box using the custom function. What I have is when my custom function is invoked I make an API call where I can have one or more than one response and if I have a single response the response is returned and displayed on the cell But the issue is that when I have multiple responses I use a dialog box for the user to select the value. – user21064709 Jan 25 '23 at 17:34
  • And I cannot get the value that I select from the dialog box which is opened when I call displayDialogAsync and send it to excel using messageParent on Excel Desktop. As I have to select a value from the dialog box and need that value to be displayed on the cell where the custom function was invoked @JakobNielsen-MSFT – user21064709 Jan 25 '23 at 17:38

0 Answers0