Filling an paramarray

[cd1001]

Hello,

I wanna achieve this:

SliceArray= Worksheetfunction.Index(varArray, (Array(2, 4, 5), 0))

in a more generic style like this

Function SliceArray (p_lngStartRow as Long, p_lngEndRow as Long) as Variant

Dim varSubArr() as Variant
Dim i as Long

ReDim varSubArr(1 To (p_p_lngEndRow- p_lngStartRow) + 1)
'...
For i =Lbound(varSubArr) to Ubound(varSubArr)
varSubArr(i)= (p_lngIndexStart - 1) + i
Next i

' And then use varSubArr as Paramarray in the Worksheetfunction like this

SliceArray= Worksheetfunction.Index(varArray, 0, varSubArr))

End Function

But I get a Type-Error

How could I get this to work? Thank you very much.

 

[Norie]

How/where exactly are you trying to use the function?

What is varArray when you get the error?

Why aren't you passing varArray to the function?

By the way, what ParamArray are you referring to?

 

[cd1001]

How/where exactly are you trying to use the function?

What is varArray when you get the error?

varArray is a public Member-Array of the class

Why aren't you passing varArray to the function?

I do, varArray is the bigger Array. From this one I try to get a smaller one (varArrSub) by using the Application.index-Function.

By the way, what ParamArray are you referring to?

Paramarray: The Index-Function can receive Array as Arguments. That means, I can get more than one Row from varArr by using the Index-Function

Thanks for reply.

 

[Norie]

ParamArray (parameter array) - allows you to pass, in VBA, a variable number of arguments to a procedure.

 

[cd1001]

Ok, then we call it just array:

Look at: SliceArray= Worksheetfunction.Index(varArray, (Array(2, 4, 5), 0))
gives me a Variant-Array with three Row-Vectors (2,4,5) in it.


To clarify my Problem: I don't want to hardcode (Array(2,4,6). I wish to have a generic Array with dynamic Values and Dimensions, like this:

Worksheetfunction.Index(varArray, myArrayWithRowNumbersToSelect,0)

Any input?