VBA - 获取数组长度时无效的限定符

I am trying to make a function that puts the param into an array, then returns a random index value of the array. My code gets a compile error: invalid qualifier at arr.Length. So far I have:

我正在尝试创建一个将param放入数组的函数,然后返回数组的随机索引值。我的代码出现编译错误:arr.Length中的无效限定符。到目前为止我有:

Function myFunction(List As Range)
    Dim arr()
    Dim indx

    arr = List
    indx = (Int(Rnd()) * arr.Length)   'error here

    myFunction = indx
End Function

Not sure if I am using the array right, or returning the value right - Please help

不确定我是否正确使用数组,或者返回正确的值 - 请帮助

REVISION 1

Replaced .length with ubound and lbound - now I am getting a #VALUE error in the cell when it should be returning the array's index value.

用ubound和lbound替换.length - 现在我应该在返回数组的索引值时在单元格中得到#VALUE错误。

Function myFunction(List as Range)

    Dim arr()
    Dim indx as Integer

    arr = List
    indx = Int(Rnd() * (UBound(arr) - LBound(arr) + 1)) 'indx

    myFunction = arr(indx)
End Function

1 个解决方案

#1

When you assign the Value of a Range to an array, you get a 2-D array with dimensions (1 to numberOfRows, 1 to numberOfCols), so the solution is to change arr(indx) to

当您将一个范围的值分配给一个数组时,您将获得一个二维数组,其中包含维度(1到numberOfRows,1到numberOfCols),因此解决方案是将arr(indx)更改为

Function myFunction(List as Range)

    Dim arr()
    Dim indx as Integer

    arr = List
    indx = Int(Rnd() * (UBound(arr) - LBound(arr) + 1)) 'indx

    myFunction = arr(indx,1)
End Function