Edit

Tutorial: Share data and events between Excel custom functions and the task pane

  • Article

Share global data and send events between the task pane and custom functions of your Excel add-in with a shared runtime.

Share a state between custom function and task pane code

The following instructions show how to share a global variable between custom function and task pane code. This tutorial assumes that you've completed the Excel custom functions tutorial, with a Excel Custom Functions using a Shared Runtime project using the script type JavaScript. Use the add-in you created in that tutorial to complete the following instructions.

Create custom functions to get or store shared state

  1. In Visual Studio Code open the file src/functions/functions.js.

  2. On line 1, insert the following code at the very top. This will initialize a global variable named sharedState.

    window.sharedState = "empty";

  3. Add the following code to create a custom function that stores values to the sharedState variable.

    /**
 * Saves a string value to shared state with the task pane
 * @customfunction STOREVALUE
 * @param {string} value String to write to shared state with task pane.
 * @return {string} A success value
 */
function storeValue(sharedValue) {
  window.sharedState = sharedValue;
  return "value stored";
}

  4. Add the following code to create a custom function that gets the current value of the sharedState variable.

    /**
 * Gets a string value from shared state with the task pane
 * @customfunction GETVALUE
 * @returns {string} String value of the shared state with task pane.
 */
function getValue() {
  return window.sharedState;
}

  5. Save the file.

Create task pane controls to work with global data

  1. Open the file src/taskpane/taskpane.html.

  2. After the closing </main> element, add the following HTML. The HTML creates two text boxes and buttons used to get or store global data.

    <ol>
  <li>
    Enter a value to send to the custom function and select
    <strong>Store</strong>.
  </li>
  <li>
    Enter <strong>=CONTOSO.GETVALUE()</strong> into a cell to retrieve it.
  </li>
  <li>
    To send data to the task pane, in a cell, enter
    <strong>=CONTOSO.STOREVALUE("new value")</strong>
  </li>
  <li>Select <strong>Get</strong> to display the value in the task pane.</li>
</ol>

<p>Store new value to shared state</p>
<div>
  <input type="text" id="storeBox" />
  <button onclick="storeSharedValue()">Store</button>
</div>

<p>Get shared state value</p>
<div>
  <input type="text" id="getBox" />
  <button onclick="getSharedValue()">Get</button>
</div>

  3. Before the closing </body> element, add the following script. This code will handle the button click events when the user wants to store or get global data.

    <script>
  function storeSharedValue() {
    let sharedValue = document.getElementById('storeBox').value;
    window.sharedState = sharedValue;
  }

  function getSharedValue() {
    document.getElementById('getBox').value = window.sharedState;
  }
</script>

  4. Save the file.

  5. Build the project.

    npm run build

Try sharing data between the custom functions and task pane

  • Start the project by using the following command.

    npm run start

Once Excel starts, you can use the task pane buttons to store or get shared data. Enter =CONTOSO.GETVALUE() into a cell for the custom function to retrieve the same shared data. Or use =CONTOSO.STOREVALUE("new value") to change the shared data to a new value.

Note

Calling some Office APIs from custom functions using a shared runtime is possible. See Call Microsoft Excel APIs from a custom function for more details.

See also