Excel VBA:将计算结果数组作为参数传递给函数 [英] Excel VBA: Passing a calculation result array as an argument to a function

问题描述

我有一个VBA函数，可以接受数组，如果我给它传递诸如 A1:A10 之类的引用，它会很好用.它不接受诸如 A1:A10 + 1 或 A1:A10 ^ 2 之类的公式"参数，结果单元格包含 #VALUE

I have a VBA function that accepts arrays, it works great if I pass it a reference such as A1:A10. It does not accept a "formula" argument such as A1:A10+1 or A1:A10^2, the resulting cells contain #VALUE.

许多excel函数都支持此功能，例如linest(...)，我可以编写以下公式来获得四阶拟合，而不必手动列出 X 参数的四列.

Many excel functions support this, such as linest(...) which i can write the following formula to get a forth order fit without having to manually list the four columns for the X argument.

     =LINEST( B1:B10, A1:A10^{1,2,3} )

我的函数声明是:

     Function FFT(ByRef A) As Variant

我希望Excel对表达式进行评估并将结果传递给函数.Excel VBA无法做到这一点吗?

I'm expecting Excel to evaluate the expression and pass the result into the function. Is this not possible with Excel VBA?

推荐答案

如果要从另一个VBA子例程或函数调用函数，则Excel不会为您解释范围计算.在调用函数之前，必须自己在范围区域上执行任何数据操作.

If you're calling your function from another VBA subroutine or function, Excel will not interpret the range calculation for you. You have to perform any data manipulations over the range area yourself before calling your function.

但是，只要您将函数用作工作表单元格中的UDF，Excel就会在您描述范围参数时对其进行解释.请注意，对范围应用任何计算时，您必须通过键入 control-shift-enter 来完成单元格公式，才能标记"单元格公式以使其成为数组公式.

But as long as you're using the function as a UDF from a worksheet cell, Excel will interpret the range parameters as you describe them. The caveat when applying any calculations to the range is you must "tag" the cell formula to excel as an array formula by typing with control-shift-enter to complete the cell formula.

考虑此设置:

Option Explicit

Public Function CalculateThis(inRange) As Double
    Dim answer As Double
    answer = 0#
    CalculateThis = answer
End Function

按预期输入范围参数A1:E1，即 Variant/Object/Range .使用VBA编辑器中的View-> Locals窗口进行浏览.

The range parameter A1:E1 comes in as expected, a Variant/Object/Range. Use the View-->Locals window in the VBA Editor to take a look.

现在，如果要将计算应用于之前函数的范围，您仍然可以.

Now, if you wanted to apply a calculation to the range before it gets to your function, you still can.

考虑公式 = CalculateThis(A1:E1 ^ 2).您期望的是五个值的数组，每个值均平方.但是，如果您通过键入简单的 enter 输入此公式，则得到的是单个值而不是数组.(奖金指向可以解释为什么值是9的人.)

Consider the formula =CalculateThis(A1:E1^2). What you expect is an array of five values, each value squared. But if you enter this formula by typing a simple enter, what you get is a single value instead of an array. (Bonus points to someone who can explain why the value is 9.)

但是，用 control-shift-enter 输入相同的公式，您将获得五个值的期望数组，每个值均平方.

But enter the same formula with control-shift-enter and you get the expected array of five values, with each value squared.

如果范围和数组计算不完全匹配，则必须检查一些错误情况.例如， = CalculateThis(A1:E1 ^ {1,2,3,4,5})(输入 control-shift-enter )可为您带来预期的效果:

You have to check for some error conditions if your range and array calculation don't quite match up. For example =CalculateThis(A1:E1^{1,2,3,4,5}) (entered with control-shift-enter) gets you the expected:

但是 = CalculateThis(A1:E1 ^ {1,2,3})不会:

(变量数组中的错误是您可以检查的字符串错误2042".)

(The errors in the variant array are strings "Error 2042" that you can check for.)

这篇关于Excel VBA:将计算结果数组作为参数传递给函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！