为什么你不能在自定义函数中使用setValue？ [英] Why can't you use setValue in a custom function?

问题描述

我真的很好奇，但是有人知道为什么你不能使用setValue在自定义函数中的不同单元格中写入代码？

自述文件解释说你不能这样做，但没有给出原因：
链接

自定义函数返回值，但不能在其所在单元格外设置值。情况下，
单元格A1中的自定义函数无法修改单元格A5。但是，如果自定义函数返回
double数组，则结果会溢出包含函数
的单元格，并填充下面的单元格并位于包含
自定义函数的单元格的右侧。您可以使用包含
return [[1,2]，[3,4]];。

任何人都知道这是否有充分的理由？

解决方案

我认为这很合乎逻辑。当你在一个单元格中调用一个自定义函数时，例如A1，你期望函数执行计算（或其他），并将结果放在A1中。如果你想在B1中看到结果，你会在B1中写出相同的公式。正如文档解释的那样，可能会出现这种情况，您希望该函数返回多个值，在这种情况下，返回一个2D数组将填充与该数组相邻的单元格一个公式被称为的地方。

在任何情况下，您是否可以提供使用自定义函数来填充其他单元格的用例。你总是可以用其他方式（不作为公式）调用脚本，并让它修改任何你想要的单元格。

I'm really just curious, but does anyone know why you can't use setValue to write in different cell in a custom function?

The readme explains that you can't do this, but doesn't give a reason on why: link

Custom functions return values, but they cannot set values outside the cells they are in. In most circumstances, a custom function in cell A1 cannot modify cell A5. However, if a custom function returns a double array, the results overflow the cell containing the function and fill the cells below and to the right of the cell containing the custom function. You can test this with a custom function containing return [[1,2],[3,4]];.

Anyone knows if there is a good reason for this?

解决方案

I think it is quite logical. When you're calling a custom function in a cell, say A1, you are expecting the function to do the calculation (or whatever) and place the result in A1. If you want to see the result in B1, you'd write the same formula in B1.

As the doc explains, there might be cases where you want the function to return more than one value in which case, returning a 2D array will populate the cells adjacent to the one where the formula was called.

In any case, can you give the use case you have where you want to populate a different cell using a custom function. You can always call a script in other fashions (not as a formula) and have it modify any cell you want.

这篇关于为什么你不能在自定义函数中使用setValue？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！