Declare and array in a function

[Dave]

I'm trying to declare an array from within a function, and then add to that
array certain fields from a range I use as the input variable. For example:

Function test(rng As Range)
valuesArr = rng
ReDim testArray(20) As String

For i = 1 To UBound(valuesArr)
testArray(i) = valuesArr(i, 1)
Next i
test = testArray(5)
End Function

So I want to add all values from within "rng" to "testArray()", and then
take the 5th element of testArray() and return it. I keep getting an error
when I enter the function, any ideas?
Thanks.

 

[Dave Peterson]

I'm not quite sure what you're doing, but you can plop all the values into an
array with a single statement--like the one you used.

But this array will be two dimensional -- rows by columns.

So I could use this:

Option Explicit
Function test(rng As Range)
Dim ValuesArr As Variant
ValuesArr = rng.Value
test = ValuesArr(5, 1) '5th row, 1st column
End Function

And test it with a sub like:

Sub testme()
MsgBox test(worksheets("sheet1").Range("a1:A20"))
End Sub

Or use it from a cell in a worksheet with:
=test(a1:a20)

=====
But if you're doing this from a cell on a worksheet, you could just use:
=index(a1:a20,5)

 

[Stopher]

Is it because you Redim your array as a string?

Sorry got no idea really, just offering a suggestion.

 

[Tom Ogilvy]

Your code worked fine for me both when called from another macro:

[demo'd from the immediate window)

? test(Range("A1:C6"))
13

and from the worksheet

=Test(A1:C6")
displayed 13

If you feed it a range with more than 20 rows or less than 5 rows you would
have a problem