how to get one dimensional vector from an array in vba

Question

Does anyone know how to get a real vector from one column of array in VBA? I've done the following steps:

'' read one column from 2D array
Dim ixy()
ixy = Application.Index(ts.Vertices, , 1)

And everything was looking all right until I wanted to ReDim the vector and add one more value to the end:

ReDim Preserve ixy(LBound(ixy) To UBound(ixy) + 1, 1 To 1)
ixy(UBound(ixy)) = ts.Vertices(1, 1)

This simply doesn't work because I cant ReDim non-last dimension of array. So how to obtain a vector by reading a column from an array? In Locals window the variable ixy looks + : ixy : : Variant(1 to 32, 1 to 1) so it's not 1d array.

I know how to obey this with loops but just want to find clear solution with minimum code. Any suggestions?

What I wanted is transpose. The first ixy = Application.Index(ts.Vertices, , 1) remains the same and its just needed to be transposed. What a simple solution! ixy = Application.Transpose(ixy) Many thanks for this suggestion! — Peter.k
– Peter.k, Commented Feb 19, 2017 at 22:43
I got the parameter off, but If I remember correctly Transpose returns 2D array, and Index( , 0) 1D array — Slai
– Slai, Commented Feb 19, 2017 at 22:45
For me this first solution with zero doesn't work. But the second is great. I use Excel 2016, 64-bit if it matters. — Peter.k
– Peter.k, Commented Feb 19, 2017 at 22:50

Slai · Accepted Answer · 2017-02-19 22:41:46Z

1

From the INDEX function Remarks section:

If you set Row_num or Column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively.

ixy = Application.Index(ts.Vertices, 0, 1)

or

ixy = Application.Transpose(ts.Vertices)

answered Feb 19, 2017 at 22:41

Slai

23k5 gold badges49 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter.k Over a year ago

ixy = Application.Transpose(ixy) works, because ixy = Application.Transpose(ts.Vertices) transposes whole 2d array. This first one should stay the same as: ixy = Application.Index(ts.Vertices, , 1) (without zero)

Collectives™ on Stack Overflow

how to get one dimensional vector from an array in vba

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related