Is it possible to execute an excel function from an office dialog?

Question

I have built an excel add-in in react typescript using the yeoman generator. My dialog is only opened through a button found within the custom tab I've created. As a developer, I want to allow a user run an excel function from a dialog that will update the worksheet.

I've read through the office documentation but can only find authentication related examples. Can anyone shed further light on this?

score 1 · Accepted Answer · answered Jun 26 '23 at 19:06

You can call some of the built-in Excel functions from an add-in. (See here.) But to do it from a dialog is tricky. The only Office.js APIs that are supported in the dialog are isSetSupported and messageParent. So, your button in the dialog can't directly call the workbook.functions API. What you need to do is have the button in the dialog send a message back to the parent JavaScript runtime where displayDialogAsync was called. The message identifies the function to be called. Code in the parent handles the MessageReceived event and calls the workbook.functions API, passing the name of the function to be called. To the end user it looks like the button in the dialog is running the Excel function.

score 0 · Answer 2 · answered Jun 26 '23 at 21:24

You can use the Office dialog API to open dialog boxes in your Office Add-in. See Use the Office dialog API in Office Add-ins for more information.

Code in the dialog box uses the messageParent function to send a string message to the host page. The string can be a word, sentence, XML blob, stringified JSON, or anything else that can be serialized to a string or cast to a string. To use the messageParent method, the dialog box must first initialize the Office JavaScript API:

Office.onReady(function() {
   // Add any initialization code for your dialog here.
});

// Called when dialog signs in the user.
function userSignedIn() {
    Office.context.ui.messageParent(true.toString());
}

The messageParent function is one of only two Office JS APIs that can be called in the dialog box. The host page must be configured to receive the message. You do this by adding a callback parameter to the original call of displayDialogAsync. The callback assigns a handler to the DialogMessageReceived event. For example:

let dialog; // Declare dialog as global for use in later functions.
Office.context.ui.displayDialogAsync('https://www.contoso.com/myDialog.html', {height: 30, width: 20},
    function (asyncResult) {
        dialog = asyncResult.value;
        dialog.addEventHandler(Office.EventType.DialogMessageReceived, processMessage);
    }
);

Basically, from your message box instance you can send a message to the host page where you can call a function.

Is it possible to execute an excel function from an office dialog?

2 Answers2