none
Excel 1.1 API - Undo stops working in the current sheet if Excel.run(..) batch is called RRS feed

  • Question

  • I have found that Undo stops working in the active sheet if an Excel.run(..) batch to get range information is called from within an BindingDataChanged or DocumentSelectionChanged event.  Trying to Undo changes made in the binding range just beeps as it seems the Undo buffer has been cleared for some reason.  What makes this an even bigger issue is that BindingDataChanged is called sometime even when the actual binding data has not changed.

    Why does this occur? Unless I'm doing something wrong this is a bug and very bad for the user since can't undo anything in the binding range and sometimes any other changes made in the same sheet.

    What I am trying to accomplish is keeping my own "range" object in sync with the binding range in the sheet so I am listening for binding changes.  The Excel 1.1 API allows for getting more information about the range than the 1.0 Bindings object so I am calling the 1.1 APIs in my BindingDataChanged event handler but this is causing Undo to break.

    To reproduce create a small binding range and then add a handler for BindingDataChanged that calls Excel.Run code below.  When you change data in the binding range and try to Undo (Ctrl Z) it does not work (just beeps).

    Also, a complete code sample is below that reproduces the issue.  Just cut/paste this into a new Excel (2016) Task Pane app.

    <!DOCTYPE html>
    <html>
    <head>
       <meta charset="UTF-8" />
       <meta http-equiv="X-UA-Compatible" content="IE=Edge" />
       <title></title>
       <script src="../../Scripts/jquery-1.9.1.js" type="text/javascript"></script>
     
       <link href="../../Content/Office.css" rel="stylesheet" type="text/css"/>
     
       <script src="https://appsforoffice.microsoft.com/lib/1/hosted/office.js" type="text/javascript"></script>
       
       <script>
          (function () {
     
             "use strict";
     
             var bindingId = "TestBindingId";
             var msg = "";
     
             // The initialize function must be run each time a new page is loaded
             Office.initialize = function (reason) {
                $(document).ready(function () {
                   $('#bind-data-from-selection').click(bindDataFromSelection);
     
                   $('#get-data-from-binding').click(getBindingDataById);
     
                   $('#add-binding-datachanged-handler').click(addEventHandlerToBinding);
     
                   $('#remove-binding-datachanged-handler').click(removeEventHandlerFromBinding);
     
                   $('#clear-msg').click(clearMsg);
                });
             };
     
             function bindDataFromSelection() {
                Office.context.document.bindings.addFromSelectionAsync(Office.BindingType.Matrix, { id: bindingId }function (asyncResult) {
                   if (asyncResult.status == Office.AsyncResultStatus.Failed) {
                      write('Action failed. Error: ' + asyncResult.error.message);
                   } else {
                      write('New ' + asyncResult.value.type + ' binding, id = ' + asyncResult.value.id);
                   }
                });
             }
     
             function getBindingDataById() {
                var options = {
                   coercionType: "matrix",    // Office.CoercionType.Matrix,
                   valueFormat: "unformatted"  //Office.ValueFormat 
                };
     
                Office.select("bindings#" + bindingId).getDataAsync(options, function (asyncResult) {
                   write("Binding data returned = " + asyncResult.value);
                });
             }
     
             function addEventHandlerToBinding() {
                Office.select("bindings#" + bindingId).addHandlerAsync(Office.EventType.BindingDataChanged, onBindingDataChanged);
                write("Add BindingDataChanged handler");
             }
     
             function removeEventHandlerFromBinding() {
                Office.select("bindings#" + bindingId).removeHandlerAsync(Office.EventType.BindingDataChanged, { handler: onBindingDataChanged });
                write("Remove BindingDataChanged handler");
             }
     
             function onBindingDataChanged(eventArgs) {
                write("onBindingDataChanged(): id=" + eventArgs.binding.id + ", *** Undo does not work now ***");
     
                Excel.run(function (ctx) {
                   var binding = ctx.workbook.bindings.getItem(bindingId);
                   var excelRange = binding.getRange();
                   excelRange.load(["type,""address""cellCount""columnCount""rowCount"]);
     
                   return ctx.sync().then(function () {
                      // do whatever
                      var rangeAddress = excelRange.m_address;
     
                   }).catch(function (error) {
                      // handle...
                   });
     
                }).catch(function (error) {
                   // handle...
                });
             }
     
     
             function write(message) {
                msg += message + "<br/><br/>";
                $('#message').html(msg);
             }
     
             function clearMsg() {
                msg = "";
                $('#message').html("");
             }
     
          })();
     
       </script>
     
    </head>
    <body>
    <div id="content-main">
     
       <div style="padding10px">
          <h1>Test Binding</h1>
     
          <br/>
          <button id="bind-data-from-selection">Bind data from selection</button>
     
          <br/>
          <button id="get-data-from-binding">Get data for binding</button>
     
          <br/>
          <button id="add-binding-datachanged-handler">Add binding data changed handler</button>
     
          <br/>
          <button id="remove-binding-datachanged-handler">Remove binding data changed handler</button>
     
          <br/>
          <button id="clear-msg">Clear Message</button>
     
          <br/><br/>
          Status
          <div id="message" style="background#e0e0e0padding15px;"></div>
       </div>
    </div>
    </body>
    </html>
    



    • Edited by Jim - Strive Tuesday, March 22, 2016 6:07 PM udpated description to "any Excel.run(...) batch at all
    Friday, March 4, 2016 8:13 PM

Answers

  • Hi Jim,

    I made a test with your code, and I could reproduce your issue.

    With the description of Excel.Run below, tracked objects that were automatically allocated during execution will be released. But if I just with Excel.Run without Binding Object, it worked correctly. I assume your tracked objects were be released too when tracked objects are Binding Object.

        Excel.run = function (batch) {
            /// <summary>
            /// Executes a batch script that performs actions on the Excel object model. When the promise is resolved, any tracked objects that were automatically allocated during execution will be released.
            /// </summary>
            /// <param name="batch" type="function(context) { ... }">
            /// A function that takes in a RequestContext and returns a promise (typically, just the result of "context.sync()").
            /// <br />
            /// The context parameter facilitates requests to the Excel application. Since the Office add-in and the Excel application run in two different processes, the request context is required to get access to the Excel object model from the add-in.
            /// </param>
            batch(new Excel.RequestContext());
            return new OfficeExtension.IPromise();
        }

    Based on your description, it seems you want to sync the two range value within BindingDataChanged event. For a workaround, I suggest you use formulas by setting the formula of second range to the first range. Then, the second range value would change if your first range changes.

    For this issue, I suggest you submit a feedback in the link below:
    http://officespdev.uservoice.com

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.




    Monday, March 7, 2016 2:39 AM

All replies

  • Hi Jim,

    I made a test with your code, and I could reproduce your issue.

    With the description of Excel.Run below, tracked objects that were automatically allocated during execution will be released. But if I just with Excel.Run without Binding Object, it worked correctly. I assume your tracked objects were be released too when tracked objects are Binding Object.

        Excel.run = function (batch) {
            /// <summary>
            /// Executes a batch script that performs actions on the Excel object model. When the promise is resolved, any tracked objects that were automatically allocated during execution will be released.
            /// </summary>
            /// <param name="batch" type="function(context) { ... }">
            /// A function that takes in a RequestContext and returns a promise (typically, just the result of "context.sync()").
            /// <br />
            /// The context parameter facilitates requests to the Excel application. Since the Office add-in and the Excel application run in two different processes, the request context is required to get access to the Excel object model from the add-in.
            /// </param>
            batch(new Excel.RequestContext());
            return new OfficeExtension.IPromise();
        }

    Based on your description, it seems you want to sync the two range value within BindingDataChanged event. For a workaround, I suggest you use formulas by setting the formula of second range to the first range. Then, the second range value would change if your first range changes.

    For this issue, I suggest you submit a feedback in the link below:
    http://officespdev.uservoice.com

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.




    Monday, March 7, 2016 2:39 AM
  • Hi Edward,

    Thanks for the reply.  Just for clarification...what is a "tracked object"?  For example, is excelRange in my above example a "tracked object".  I have a layered application and I am passing the excelRange returned from the promise to a higher layer (an Angular service).  Do I then have to release the excelRange myself using JavaScript "delete"?  Wouldn't it be garbage collected anyway?

    This seems like a bad bug.  Is user voice the best place for this to get some attention?

    Thanks again,

    Jim

    Monday, March 7, 2016 3:35 AM
  • I found an existing User Voice that covers this issue.  I have added it here in case someone wants to increase the vote to fix the issue:

    https://officespdev.uservoice.com/forums/224641-general/suggestions/10754784-fix-the-issue-with-new-javascript-api-for-excel-a

    Jim

    Monday, March 7, 2016 4:50 AM
  • Hi Jim,

    >> ...what is a "tracked object"? 

    I assume the tracked object is the steps you operate in Excel, but I am not sure. For this issue about API, I am afraid we could not modify it, and user voice would be the best place.

    If there is no other issues, I would suggest you mark the helpful reply as answer to close this thread.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, March 7, 2016 9:20 AM
  • Hi Edward,

    I will mark one of your responses above as the answer but if you have a better workaround please post it. 

    I am trying to keep the range address (Excel.Range.m_address) of a binding range in sync with my own object when the binding range changes.  It's just a read operation.  I can keep the data in sync OK because calling the common API to get the new binding data doesn't break Undo.

    Thanks,

    Jim

    Monday, March 7, 2016 2:54 PM
  • Hi Jim,

    I will post back if I have better workaround or there is some new information about this issue.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, March 8, 2016 2:07 AM
  • Thanks Edward.

    Any feedback or workaround would be appreciated. Trying to keeping bindings (range data) in sync without disabling undo seems near impossible.

    Jim

    Tuesday, March 8, 2016 5:51 AM