Calling Back Into Excel

Sometimes it can be useful to be able to call back into Excel from a worksheet function using Excel’s COM API.

For example, you might have a function that returns a table of data and you want to apply some formatting to that. Or, you might want to update some cells in a location other than where the function was called from¹.

Excel does not allow you to call all of its API from a user defined function. Anything that would update Excel is usually prevented from being called while the worksheet is calculating.

Jinx provides a helper method ExcelAddIn.schedule(java.lang.Runnable task) to schedule a task to be called after Excel has finished calculating. It ensures that Excel is ready to be called before the task is run and so it is safe to call the Excel COM API from a scheduled task.

To access the Excel object model from Java and Jinx, we recommend that you use the jinx-com4j project, which acts as a bridge between your Jinx add-in and Excel. For details of jinx-com4j see its github page https://exceljava.github.io/jinx-com4j.

An example of using ExcelAddIn.schedule(java.lang.Runnable task) is included in the examples in the jinx-com4j project.

Setting cell values using COM from worksheet functions breaks Excel’s dependency graph. Although this can be useful in some situations, it is not always advisable.