How do I slice an array in Excel VBA?

Question

What function can I use in Excel VBA to slice an array?

Answer 1

Application.WorksheetFunction.Index(array, row, column)

If you specify a zero value for row or column, then you'll get the entire column or row that is specified.

Example:

Application.WorksheetFunction.Index(array, 0, 3)

This will give you the entire 3rd column.

If you specify both row and column as non-zero, then you'll get only the specific element. There is no easy way to get a smaller slice than a complete row or column.

EDIT: Here's the function I wrote to do all my 1D and 2D slicing:

Public Function GetArraySlice2D(Sarray As Variant, Stype As String, Sindex As Integer, Sstart As Integer, Sfinish As Integer) As Variant

' this function returns a slice of an array, Stype is either row or column
' Sstart is beginning of slice, Sfinish is end of slice (Sfinish = 0 means entire
' row or column is taken), Sindex is the row or column to be sliced
' (NOTE: 1 is always the first row or first column)
' an Sindex value of 0 means that the array is one dimensional 3/20/09 ljr

Dim vtemp() As Variant
Dim i As Integer

On Err GoTo ErrHandler

Select Case Sindex
    Case 0
        If Sfinish - Sstart = UBound(Sarray) - LBound(Sarray) Then
            vtemp = Sarray
        Else
            ReDim vtemp(1 To Sfinish - Sstart + 1)
            For i = 1 To Sfinish - Sstart + 1
                vtemp(i) = Sarray(i + Sstart - 1)
            Next i
        End If
    Case Else
        Select Case Stype
            Case "row"
                If Sfinish = 0 Or (Sstart = LBound(Sarray, 2) And Sfinish = UBound(Sarray, 2)) Then
                    vtemp = Application.WorksheetFunction.Index(Sarray, Sindex, 0)
                Else
                    ReDim vtemp(1 To Sfinish - Sstart + 1)
                    For i = 1 To Sfinish - Sstart + 1
                        vtemp(i) = Sarray(Sindex, i + Sstart - 1)
                    Next i
                End If
            Case "column"
                If Sfinish = 0 Or (Sstart = LBound(Sarray, 1) And Sfinish = UBound(Sarray, 1)) Then
                    vtemp = Application.WorksheetFunction.Index(Sarray, 0, Sindex)
                Else
                    ReDim vtemp(1 To Sfinish - Sstart + 1)
                    For i = 1 To Sfinish - Sstart + 1
                        vtemp(i) = Sarray(i + Sstart - 1, Sindex)
                    Next i
                End If
        End Select
End Select
GetArraySlice2D = vtemp
Exit Function

ErrHandler:
    Dim M As Integer
    M = MsgBox("Bad Array Input", vbOKOnly, "GetArraySlice2D")

End Function

Answer 2

You can use a combination of the Rows, Columns, Offset and Resize properties to get a subset of a range.

For example if you have a range that is 5 columns by 3 rows:

Set rng = Range("A1:E3")

You can get any subset by appropriately combining the above properties. For example, if you want to get the rightmost 3 cells on the second row (i.e. "C2:E2" in the above example), you could do something like:

   Set rngSubset = rng.Rows(2).Offset(0, rng.Columns.Count - 3).Resize(1, 3)

You could then wrap this up in a VBA function.

Answer 3

How about Array = Split(variable)

Answer 4

Two things, VBA doesn't support array slicing so whatever you use, you'll have to roll your own. But since this is just for Excel, you can use the build in worksheet function index for array slicing.

Sub Test()
    'All example return a 1 based 2D array.
    Dim myArr As Variant 'This var must be generic to work.
    'Get whole range:
    myArr = ActiveSheet.UsedRange
    'Get just column 1:
    myArr = WorksheetFunction.Index(ActiveSheet.UsedRange, 0, 1)
    'Get just row 5
    myArr = WorksheetFunction.Index(ActiveSheet.UsedRange, 5, 0)
End Sub