VBA - invalid qualifier when getting length of array
I am trying to create a function that puts a parameter into an array and then returns a random array index value. My code is getting compile error: invalid qualifier
into arr.Length
. So far I have:
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 correctly or if I will return its value. Please, help
REVISION 1
Replaced .length with ubound and lbound - I now get an error #VALUE
on a cell when it should return an array index 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
source to share
When you assign a range value to an array, you end up with a 2 dimensional array with dimensions (1 to numberOfRows, 1 to numberOfCols)
, so the solution should change arr(indx)
to
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
source to share