I have problems executing an office addin in multiple Excel instances. One stops running when both are executed hat the same time.
I did 2 quick ScriptLab samples, where you can reproduce some issues (I pasted them). One contains an UDF-Function, just register it in ScriptLab. The other on is a sample which produces one of my problems.
First register the UDF, than before using the second part, create 2 workbooks with each having 100 worksheets that contain the following function (depending on the Snippent name, which is in my case "Blank snippet (1)", if your name is different, please adjust the formula name here and also in the addin-code in the function "findAllOrNullObject").
=SCRIPTLAB.BLANKSNIPPET1.ADD(1;2)
The quickest way to do this is: Create ten sheets with that function and copy this ten worksheets ten times to the end of the workbook. After this is done, save the workbook a second time with a different name. Afterwards, open both workbooks and click "Run" (in both sheets). Than click into another application while both are running or open one. On the console you will see a counter that indicates, on which sheet the addin is actually working. You should expect "INDEX: 100" in both instances but one instance will stop, when you click into another application or start one and will not reach 100. If you will not have the problem directly, just try again, it will sure appear.
Code for UDF:
/**
* Adds two numbers.
* @customfunction
* @param first First number
* @param second Second number
* @returns The sum of the two numbers.
*/
/* global clearInterval, console, setInterval */
function add(first: number, second: number): number {
return first + second;
}
Code for Addin:
$("#run").click(() => tryCatch(run));
async function run() {
this.refreshWorkbook();
}
async function refreshWorkbook() {
let sheets: Excel.WorksheetCollection;
Excel.run(async (context) => {
sheets = context.workbook.worksheets;
sheets.load("items/name");
await sheets.context.sync();
if (sheets.items.length >= 1) {
for (let sheetIndex = 0; sheetIndex < sheets.items.length; sheetIndex++) {
console.log("INDEX : " + sheetIndex);
const sheet = sheets.items[sheetIndex];
await this.getInfo(sheet.name).then((information) => {
// some stuff
});
}
}
});
}
async function getInfo(worksheetName: string): Promise<string> {
return new Promise<string>((resolve, reject) => {
Excel.run(async (context) => {
const sheet: Excel.Worksheet = context.workbook.worksheets.getItem(worksheetName);
sheet.load("name");
await context.sync();
const usedRange = sheet.getUsedRangeOrNullObject();
if (usedRange) {
const functionCells = sheet.findAllOrNullObject("=SCRIPTLAB.SCRIPTLAB.BLANKSNIPPET1.ADD(", {
matchCase: false,
completeMatch: false
});
functionCells.load("areaCount");
await context.sync();
if (functionCells) {
resolve("A");
} else {
reject("X");
}
}
});
});
}
/** Default helper for invoking an action and handling errors. */
async function tryCatch(callback) {
try {
await callback();
} catch (error) {
// Note: In a production add-in, you'd want to notify the user through your add-in's UI.
console.error(error);
}
}
If I delete the search part it works.
