Can't store JS values across Ribbon ExecuteFunction Commands for Office add-ins

Question

I am connecting a Javascript function to an Excel Ribbon Icon through the Manifest and Functionfile.

This is my FunctionFile:

(function () {
    Office.initialize = function (reason) {
       //If you need to initialize something you can do so here.
    };

})();

var i = 42;

//Notice function needs to be in global namespace
function undo() {
    console.log(i);
    i++;
}

Now, when I click the Ribbon Icon, it prints put 42 to the Javascript Console. However, when I try to run it another time, nothing happens. Is that a bug?

Answer 1

Excel creates a new web process every time a command is clicked. As a result, i will be initialized at 42 with every click. And the console will continue to log 42 every time.

The reason that you're seeing nothing on subsequent clicks is probably that you've set your debugger to watch a particular process. If you change console.log(i); to something like Office.context.document.setSelectedDataAsync(i);, you can quickly see this result without a debugger.

In order to achieve the persistent variables that you want, you should store them in the document using the Settings object like this:

(function () {
    Office.initialize = function (reason) {
        var i = 42;
        if(Office.context.document.settings.get("storedI"){
            i = Office.context.document.settings.get("storedI");
        }
        else{
            Office.context.document.settings.set("storedI", i);
            Office.context.document.settings.saveAsync(function (asyncResult) {});
        }
    };
})();    

function undo() {
    console.log(i);
    i++;
    Office.context.document.settings.set("storedI", i);
    Office.context.document.settings.saveAsync(function (asyncResult) {});
}    

-Michael Saunders, PM for Office