有没有办法在自定义函数中使用 Excel.run()? [英] Is there a way to use Excel.run() inside Custom Function?

问题描述

由于我的自定义函数，我想填充几个工作表单元格，将调用该函数的单元格作为基本单元格.我必须返回一些复杂的数据，这些数据无法放入一个单元格中.

As a result of my Custom Function I want to fill few worksheet cells, taking cell that the function was invoked as a base cell. I have to return some complex data, that wouldn't fit in one cell.

所以问题是:有没有办法在自定义函数中使用 Excel.run()?或者有没有办法返回更复杂的数据(对象、对象数组)作为自定义函数的返回值?

So the question is: is there a way to use Excel.run() inside the Custom Function? Or is there a way to return more complex data (objects, array of objects) as the return value of Custom Function?

这是我正在尝试做的简单示例，但它不起作用:

This is simple example of what I'm trying to do, but it doesn't work:

/**
 * Adds two numbers.
 * @customfunction
 * @param first First number
 * @param second Second number
 * @returns The sum of the two numbers.
 */
async function add(first: number, second: number): Promise<void> {
  return Excel.run(async context => {
    const worksheet = context.workbook.worksheets.getActiveWorksheet();
    const range = worksheet.getRangeByIndexes(0, 0, 1, 4);
    range.values = [[1, 2, 3, 4]];

    return context.sync();
  })
}
CustomFunctions.associate("ADD", add);

推荐答案

尚无法从自定义函数使用 Excel 对象模型.对于您的场景，据我所知，您可以通过返回类型为 number[][] 的矩阵来返回多个值，并在 Excel 网格中将公式作为数组公式输入(使用 Ctrl+Shift+Enter).一旦动态数组功能广泛可用，将不再需要数组公式，结果将自动溢出"到相邻的单元格.动态数组目前在 Office Insider 版本中处于预览状态.

It is not yet possible to work with the Excel object model from a custom function. For your scenario, as I understand it, you can return multiple values by returning a matrix of the type number[][] and enter the formula in the Excel grid as an array formula (using Ctrl+Shift+Enter). Once the dynamic arrays feature is available broadly, an array formula will no longer be needed, and the result will automatically "spill" to the adjacent cells. Dynamic arrays is currently in preview in the Office Insider builds.

这篇关于有没有办法在自定义函数中使用 Excel.run()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！