CODEWITHSUNDEEP
arraysvbaexcelmultidimensional-arrayarraylist
babsdoc
babsdoc

Reputation: 749

VBA Multidimensional Arrays

Is there a way to do the following in VBA?

Initialize a multidimensional array and fill it with a series of numbers,

 1  2  3  4  5 
 6  7  8  9 10
11 12 13 14 15
16 17 18 19 20

Then remove some specific columns, for eg. columns 1, 2, 4. So that the end result is just the 3rd and 5th column.

Lastly how does one convert the final output to a normal one dimensional array.

Upvotes: 0

Views: 46264

Answers (3)

Yaacov
Yaacov

Reputation: 185

Below is some sample code that fully accomplishes the task in the question, via loops/direct copy, i.e. nothing educational, but also tries to demonstrate how VBA Excel's "slicing" function with Application.WorksheetFunction.Index does not actually help with this task, even as it may be useful in other ways:

Public Sub Answer()

    Dim i As Integer, j As Integer

    ' arrA contains the initial 4x5 multidimensional array
    Dim arrA(1 To 4, 1 To 5) As Integer
    For i = 1 To 4
        For j = 1 To 5
            arrA(i, j) = (i - 1) * 5 + j
        Next j
    Next i

    ' arrBv1 and v2 contain the 2x5 subset, just columns 3 and 5
    ' arrBv1 is obtained by direct copy
    Dim arrBv1(1 To 4, 1 To 2) As Variant
    For i = 1 To 4
        arrBv1(i, 1) = arrA(i, 3)
        arrBv1(i, 2) = arrA(i, 5)
    Next i

    ' arrBv2 is obtained by using Excel's "slicing" capability
    Dim arrBv2(1 To 4, 1 To 2) As Integer

    Dim slices(1 To 2) As Variant
    slices(1) = Application.WorksheetFunction.Index(arrA, 0, 3)
    slices(2) = Application.WorksheetFunction.Index(arrA, 0, 5)

    ' but because the slices are actually each 4x1 multidimensional
    ' array, a second loop is required to obtain a data structure
    ' actually equivalent to arrBv1, making this "shortcut" no
    ' shorter for producing a 4x2 array
    For i = 1 To 4
        arrBv2(i, 1) = slices(1)(i, 1)
        arrBv2(i, 2) = slices(2)(i, 1)
    Next i

    ' although arrBv1 and v2 are equivalent, as MsgBox does not appear
    For i = 1 To 4
        For j = 1 To 2
            If arrBv1(i, j) <> arrBv2(i, j) Then
                MsgBox ("equivalence failure with 4x2 arrays")
            End If
        Next j
    Next i

    ' arrCv1 is the 1x8 array obtained by direct copy from the 4x2 array
    Dim arrCv1(1 To 8) As Integer

    For i = 1 To 4
        arrCv1(i) = arrBv1(i, 1)
        arrCv1(i + 4) = arrBv1(i, 2)
    Next i

    ' arrCv2 is the one-dimensional array obtained from the slices, which
    ' does not lead to an additional step, but is not shorter
    ' than just using arrBv1 as immediately above or

    Dim arrCv2(1 To 8) As Integer

    For i = 1 To 4
        arrCv2(i) = slices(1)(i, 1)
        arrCv2(i + 4) = slices(2)(i, 1)
    Next i

    ' arrCv3 is the 1x8 array obtained from the original 4x5 array,
    ' shorter still

    Dim arrCv3(1 To 8) As Integer

    For i = 1 To 4
        arrCv3(i) = arrA(i, 3)
        arrCv3(i + 4) = arrA(i, 5)
    Next i

    ' and arrCv1, v2 and v3 are again all equivalent
    For i = 1 To 8
        If arrCv1(i) <> arrCv2(i) Or arrCv1(i) <> arrCv3(i) Then
            MsgBox ("equivalence failure with one-dimensional array")
        End If
    Next i

    ' so, in sum, nothing.

End Sub

Upvotes: 0

bonCodigo
bonCodigo

Reputation: 14361

Assume that you have these columns in an Excel sheeet. If you only have these data in these columns you may simple delete the columns you need :D Then you will end up with 2 columsn you desire. Without knowing what you really need at the end this is the best blind guess..

e.g. your columns starts at B to F:

Columns("B:B").Delete Shift:=xlToLeft
Columns("C:C").Delete Shift:=xlToLeft
Columns("D:D").Delete Shift:=xlToLeft

You can use the same logic to process the array.

  • Transpose the array into the sheet.
  • Delete the columns.
  • Then transpose the left over two columns to array.

But what will you do with the last two columns without putting it into the sheet? Very curious. So please confirm what you need, so anyone here can help you.

EDIT as per OP's comment:

You may take a look at this posts and articles which has manupulation of arrays in different ways:

Then in order to populate a 2D array for an example in VBA, check this out:

Dim i As Integer, j As Integer
Dim array2D As Variant, newArray2D as Variant
'-- 0 indexed based array with 2 rows 3 columns
ReDim array2D(0 To 1, 0 To 2)

For i = LBound(array2D, 1) To UBound(array2D, 1)
    For j = LBound(array2D, 1) To UBound(array2D, 1)
        array2D(i, j) = i + j
    Next j
Next i

'--to delete the fastest is to use the above logic (worksheet)
'-- here you don't need to declare/redimentioned the array
'-- as transpose will do it with a 1 indexed based array

newArray2D = WorksheetFunction.Transpose(Sheets(2).Range("B2:D").Value)

Upvotes: 3

SeanC
SeanC

Reputation: 15923

to delete row or columns from an array, you will have to transfer the data you wish to keep to a temporary array, or overwrite the values in the array.

To convert dimensions, a loop would be required.

Lots of functions and examples of arrays can be found here

Upvotes: 1

Related Questions