Excel Add-in: How to rerun functions on a cell linked to another cell that got changed

Question

In the Common API we can use a binding and an event handler to understand if a cell (cell-1) has been modified.

However, if I have another cell (cell-2) that uses the changed cell (cell-1) as an input, how do I rerun the custom function on cell-2 so it gives the newly updated result? Or does it also trigger the event handler of cell-2 the moment cell-1 is changed?

Answer 1

Hi @Hadi Tarhini,

To rerun a custom function on a cell that uses another cell as an input, you can use the Office.EventType.BindingSelectionChanged event to detect when the user selects the cell that uses the changed cell as an input. Once the cell is selected, you can use the Excel.run method to recalculate the formula and update the cell with the new result. The event handler for the Office.EventType.BindingSelectionChanged event should be added to the binding for the cell that uses the changed cell as an input.

It is important to note that changing a cell that is used as an input for another cell does not automatically trigger the event handler for the cell that uses the changed cell as an input. The Office.EventType.BindingDataChanged event is only triggered when the data in the cell that is bound to the custom function is changed.

Example code:

Excel.run(function (context) {
  // Get the binding for the cell that uses the changed cell as an input
  var binding = context.workbook.bindings.getItem("bindingName");
  // Add an event handler for the BindingSelectionChanged event
  binding.addHandlerAsync(Office.EventType.BindingSelectionChanged, function (eventArgs) {
    // Recalculate the formula and update the cell with the new result
    eventArgs.binding.getDataAsync({
      coercionType: "matrix",
      valueFormat: "formatted"
    }, function (result) {
      // Use the result to update the cell
      // ...
    });
  });
  return context.sync();
}).catch(function (error) {
  console.log(error);
});

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.