XLLs: A New Way To Optimize

Share on Facebook Share on Twitter Share on Linkedin Share via OneNote Share via Email Print

Some XLL add-ins may gain a performance boost if they take into account the worksheet context that calls them. For example, if a function is called from a sheet that is not currently active, and the rest of the workbook does not have dependencies on the results that the function returns to that sheet – then the function may choose to skip its calculation. This way, only the calls within the active sheet actually take up compute time.
In order to do this optimization, the XLL code needs to be able to query the sheet address of the cell from which it is called, and compare this to the active sheet ID. But finding out the calling cell’s sheet address was previously not possible. You could call xlfCaller, but this function would only return the calling cell address, without the cell’s sheet ID. So there was no way for you to decide if you wanted to skip your calculation.
To solve that, we added a new auxiliary function that your code can call back into – xlCallerAddress. This function returns a reference to the calling cells. The reference contains the sheet ID of the calling sheet. You can then call xlSheetId to get the sheet ID of the active sheet, and compare the two IDs. If they match, it means that your function is being called from the active sheet.