How do I slice an array in Excel VBA?


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


Here's a nifty function I wrote to subset a 2d array

Function Subset2D(arr As Variant, Optional rowStart As Long = 1, Optional rowStop As Long = -1, Optional colIndices As Variant) As Variant
    'Subset a 2d array (arr)
    'If rowStop = -1, all rows are returned
    'colIndices can be provided as a variant array like Array(1,3)
    'if colIndices is not provided, all columns are returned

    Dim newarr() As Variant, newRows As Long, newCols As Long, i As Long, k As Long, refCol As Long

    'Set the correct rowStop
    If rowStop = -1 Then rowStop = UBound(arr, 1)

    'Set the colIndices if they were not provided
    If IsMissing(colIndices) Then
        ReDim colIndices(1 To UBound(arr, 2))
        For k = 1 To UBound(arr, 2)
            colIndices(k) = k
        Next k
    End If

    'Get the dimensions of newarr
    newRows = rowStop - rowStart + 1
    newCols = UBound(colIndices) + 1
    ReDim newarr(1 To newRows, 1 To newCols)

    'Loop through each empty element of newarr and set its value
    For k = 1 To UBound(newarr, 2) 'Loop through each column
        refCol = colIndices(k - 1) 'Get the corresponding reference column
        For i = 1 To UBound(newarr, 1) 'Loop through each row
            newarr(i, k) = arr(i + rowStart - 1, refCol) 'Set the value
        Next i
    Next k

    Subset2D = newarr
End Function
By : Ben

Here is one other way.

This is not multidimensional but would work single row and single column.

f and t parameters are Zero based.

Function slice(ByVal arr, ByVal f, ByVal t)
    slice = Application.Index(arr, Evaluate("Transpose(Row(" & f + 1 & ":" & t + 1 & "))"))
End Function

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.


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.

Limitation: There is a limit to the array size that WorksheetFunction.Index can handle if you're using a newer version of Excel. If array has more than 65,536 rows or 65,536 columns, then it throws a "Type mismatch" error. If this is an issue for you, then see this more complicated answer which is not subject to the same limitation.

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
            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)
                    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)
                    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

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

End Function

This video can help you solving your question :)
By: admin