VBA-UDF处理数组的方式不同 [英] VBA - UDF handles Arrays differently

问题描述

这段代码只是我正在研究的一小部分。我将问题缩小到下一部分。我有这个UDF SampleFunction，我需要将数组{3; 4}作为唯一参数传递给它。

This code is a small subset of what I am working on. I have narrowed the problem down to the following piece. I have this UDF SampleFunction, to which I need to pass an Array, {3;4} as the sole argument.

Function SampleFunction(InputVar As Variant) As Integer
    SampleFunction = InputVar(LBound(InputVar))
End Function

我用两种不同的方式称呼此UDF。首先，通过VBA

I call this UDF in two different ways. First, through VBA

Sub testSF()
    MsgBox SampleFunction(Array(3, 4))
End Sub

其次，通过excel工作表

And secondly, through my excel worksheet as

= {SampleFunction（ ROW（3：4））} ->即作为数组函数。

={SampleFunction(ROW(3:4))} -> i.e. as an array function.

UDF适用于案例1，即通过VBA调用，它给出了<当我通过excel工作表调用案例2时，出现code> #VALUE 错误。

The UDF works for Case 1, i.e. the call through VBA, it gives a #VALUE error for Case 2, when I call it through the excel worksheet.

我在情况2中使用F8进行了逐步操作。 Lbound（InputVar）的计算结果为1（这是不同的从案例1中的子调用开始，结果为0），但是 InputVar（Lbound（InputVar））在案例2中显示下标超出范围错误。

I stepped through the function using F8 for Case 2. Lbound(InputVar) evaluates to 1 (which is different from calling from the sub in Case 1, there it evaluates to 0), yet InputVar(Lbound(InputVar)) shows the "Subscript out of Range" error in Case 2.

我想知道的是如何从工作表中调用SampleFunction函数，即案例2，使其具有与案例1相同的行为如上所示。作为奖励，如果有人可以解释为什么 Lbound（InputVar）在上述情况下的评估结果不同，那就很好了。

All I want to know is how to call SampleFunction function from a worksheet, i.e. Case 2 so that it has the same behaviour as Case 1 shown above. As a bonus, it would be nice if someone could explain why Lbound(InputVar) evaluates differently in the above cases.

一些其他详细信息：

我正在构建UDF以执行一些正则表达式操作。上面的参数 InputVar 将是一个数组{x; y; z; ...}，用于指定xth，yth，zth ...出现的次数。 InputVar 的数据类型保留为Variant，因为我希望能够传递数字（作为一个长度的数组），数组或范围（接受并转换为数组）。

I am building a UDF to perform some regex manipulations. The argument InputVar above, will be an array {x;y;z;...} specifying the xth, yth, zth ... occurences. The data type of InputVar is kept to Variant because I want to be able to pass either numbers (as a one length array), arrays, or ranges (taken in and converted to array) to the function.

谢谢！！

推荐答案

我相信您有两个问题。首先，如果您在非数组公式中使用 SampleFunction ，即InputVar是Range，我认为您的代码将不会评估。您需要采用某种方式处理可以传递到变量中的不同类型的输入。其次，您的代码假定InputVar是一维数组。这将导致任何多维数组出现错误。这是'下标超出范围'。错误的根源，因为数组函数将所有数组参数作为二维数组传递，即使它们可以表示为一维。

I believe you have two problems. First, I don't think your code will evaluate if you use SampleFunction in a non-array formula, i.e., if InputVar is a Range. You need to incorporate some way of dealing with different types of input that can be passed into the variant. Second, your code assumes the InputVar is a one-dimensional array. This will lead to an error for any multi-dimensional array. This is the source of the 'Subscript out of range.' error, because array functions pass all array arguments as two-dimensional arrays even if they are can be represented as one-dimensional.

我建议在函数中声明一个新的动态数组，然后将其设置为与InputVar相等。过去，我已经针对数组公式和非数组公式做了此工作，如下所示。另外，请注意对从数组中检索第一项的更改。

I would suggest declaring a new dynamic array in your function and then setting it equal to InputVar. In the past I've made this work for array and non-array formulas with something like below. Also, note the change to how the first item from the array is retrieved.

Option Explicit
Function SampleFunc(InputVar As Variant) As Integer

Dim tmpArray() As Variant

On Error GoTo ErrHandler
tmpArray = InputVar

'Added extra argument to LBound since dynamic arrays have two dimensions by default.
SampleFunc = tmpArray(LBound(tmpArray, 1), LBound(tmpArray, 2))
Exit Function

ErrHandler:
'Handles the case where InputVar is a Range.
tmpArray = InputVar.Value
Resume Next

End Function

这是快速而肮脏的，尤其是。错误处理，但希望基本思想会有所帮助。

This is quick and dirty, esp. the error handling, but hopefully the basic idea is helpful.

这篇关于VBA-UDF处理数组的方式不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！