从宏创建的数组公式的 UDF [英] UDF for array formulas created from macro

问题描述

我想为我在 excel 上编写的公式创建一个 udf.公式如下:

I want to create a udf for a formula I have written on excel. The formula is as follows:

=INDEX('Pivot-LH'!$D$5:$D$1650,SMALL(IF(B93='Pivot-LH'!$A$5:'Pivot-LH'!$A$1650,ROW('Pivot-LH'!$A$5:'Pivot-LH'!$A$1650)-ROW('Pivot-LH'!$A$5)+2),1))

基本上语法是通过 Pivot-LH 表上的一些数据查找单元格 B93(变量)并返回第一个、第二个和第三个值.

Basically the syntax is to look for cell B93 (variable) through some data on Pivot-LH sheet and return the 1st, 2nd and 3rd values.

我想为此定义一个 udf 并尝试通过录制宏来做到这一点.它给出了以下结果，我将其修改为输入 B93 作为名为 newroute 的变量.然而，这总是给出零值:

I want to define a udf for this and tried to do this by recording a macro. It gave the following result which I modified to enter B93 as a variable called newroute. However this always gives the value zero:

Public Function LH(newroute As Range) As Variant

Selection.FormulaArray = "=INDEX(R5C4:R1650C4,SMALL(IF(newroute=R5C1:R1650C1,ROW(R5C1:R1650C1)-ROW(R5C1)+2),1))"

End Function

为什么它给出的结果与公式不同?

Why does it not give the same result as the formula?

推荐答案

如果您想从工作表公式中调用 LH，您的函数只能返回一个值.它不能直接更新工作表.

If you want to call LH from a worksheet formula, your function can only return a value. It cannot update the sheet directly.

请参阅:https://support.microsoft.com/en-us/kb/170787

由工作表单元格中的公式调用的用户定义函数不能更改 Microsoft Excel 的环境.这意味着这样一个函数不能执行以下任何操作:

A user-defined function called by a formula in a worksheet cell cannot change the environment of Microsoft Excel. This means that such a function cannot do any of the following:

• 在电子表格中插入、删除或格式化单元格.
• 更改另一个单元格的值.
• 移动、重命名、删除或向工作簿添加工作表.
• 更改任何环境选项，例如计算模式或屏幕视图.
• 向工作簿添加名称.
• 设置属性或执行大多数方法.

所以你需要这样的东西:

So you need something like:

Public Function LH(newroute As Range) As Variant

    LH = newroute.Parent.Evaluate("=INDEX(R5C4:R1650C4,SMALL(IF(" & _
                              newroute.Address() & _
            "=R5C1:R1650C1,ROW(R5C1:R1650C1)-ROW(R5C1)+2),1))"

End Function

这篇关于从宏创建的数组公式的 UDF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！