referring to an array column as a range?

[MikeAshton55Mad]

Hi -

I have an array(10,5), say, and I want to refer to one of the columns as its own array in an argument to another function. That is, I'd like to take the 3rd column of this array and make array2(10,1). Is there an easy way to do this, or even better, to refer to that column AS a range without looping through and defining it by brute-force?

Thanks,

Mike

 

[MikeAshton55Mad]

same problem phrased another way

...the reason i want to do this is I have a function that takes a Range as an argument and I want to pass a 1-dimensional array (that's a column in another array). Even when I make a (redundant) 1-dimensional array, it seems I can't pass it to the function b/c I get a "ByRef" violation.

I always thought Ranges and Arrays were basically the same thing...I know I can't use an Array as an argument in a function, but must use a range, so I'm at a loss to figure out how to pass the array here to the other function...

Help!

Mike

 

[parry]

Hi Mike, you might need to explain a bit more and post your code. You can use an array as an argument but it must be of a variant type. The example below simulates the Index function, albeit just for 1D arrays.

Public Function Tester(MyArray() As Variant, IndexNum As Integer)
Tester = MyArray(IndexNum)
If Err.Number <> 0 Then Tester = CVErr(1)
End Function

You would use the function like this...
=tester({"A","B","C"},3)

 

[MikeAshton55Mad]

code

Okay, here's an example...

SWAPSArray(i, 4) = AAInterpArray(7, EDDates(), EDRates())

THe function AAInterpArray takes arguments of type (integer, variant, variant).

EDDates() and EDRates() are each fully-populated 2-dimensional arrays.

When the pass is made to AAInterpArray, the function blows up on the first reference to EDDates like so (rValuesX() is the name of the argument in the AAInterpArray function):

If numXs < rValuesX(1) then ....

It doesn't seem to like rValuesX(1). I've tried several things...

Mike

 

[parry]

Hi Mike, its best to post the actual code as the more info you give the better the help. However, if Im understanding your problem then its not going to work if you reference a 2d array by using a single index reference. eg rValuesX(1,1) instead of rValuesX(1)

I cant actually see the point in what your doing. If your building arrays based upon cell references and your wanting to then take particular elements out of these arrays it would be better to use a range Intersect or particular logic to get the range you require then work with it. Otherwise building the arrays is somewhat pointless.

You said in your original post that you want to get the column of a particular array. Your assumption that you have to rebuild an array by looping through the elements is the only way I know. You can place a 2D array from a range directly into an array and visa versa.