Sub arrays in VBA - I want to slice a dimension off

Question

Say I have a 2D array,

dim iArray(1 to 2, 1 to 2) as integer
iArray(1,1)=1: iArray(1,2)=2: iArray(2,1)=3: iArray(2,2)=4
'iArray =   1  2
'           3  4

and I want to cut one of the dimensions out.
In Matlab, you could:

%Matlab style:
cutArray = iArray(:,2)
%cutArray =  2
%            4

Is there an easy way to do this in VBA?

i don't think there's an easy way to do it in VBA. however, [this](http://stackoverflow.com/questions/175170/how-do-i-slice-an-array-in-excel-vba) is a related post regarding array slicing which might be of help to you. — L42, Nov 11 '13 at 02:40

score 4 · Answer 1 · answered Nov 11 '13 at 05:53

4

You can access a row or column from a 2D array using Application.Index(array, RowNum, ColNum)

to demonstrate

Sub Demo()
    Dim iArray(1 To 2, 1 To 2) As Integer
    iArray(1, 1) = 1: iArray(1, 2) = 2: iArray(2, 1) = 3: iArray(2, 2) = 4

    Dim aRow As Variant
    Dim aCol As Variant
    With Application
        ' Get Row 2
        aRow = .Index(iArray, 2)

        ' Get Column 2
        aCol = .Transpose(.Index(iArray, , 2))

    End With
End Sub

answered Nov 11 '13 at 05:53

chris neilsen

52,446
10
84
123

1

+1 I keep forgetting this; thanks for the reminder. Note that this works only for 2-dimensional arrays; if you have a higher-dimension array, then you're stuck looping. – Jean-François Corbett Nov 11 '13 at 07:52
this code can be modified in an easy way to work for an n x n dimensional array too. you will need a simple for loop but only to loop through dimensions of the array (and not the whole array itself), with every loop you slice a different column or row of the original matrix. – cybujan Jan 05 '17 at 15:21

score 0 · Answer 2 · edited May 23 '17 at 11:46

To my knowledge, you cannot do this without looping.

Below is an example of how you can do it by creating another array and filling it with a loop:

Sub arraySlice()
    Dim i As Integer
    Dim a1(1 To 2, 1 To 2) As Integer

    a1(1, 1) = 1
    a1(1, 2) = 2
    a1(2, 1) = 3
    a1(2, 2) = 4

    Dim a2(1 To 2) As Integer

    For i = 1 To UBound(a1)
        a2(i) = a1(i, 2)
    Next i

End Sub

Sub arrays in VBA - I want to slice a dimension off

2 Answers2