How do I slice an array in Excel VBA?

Question!

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



Answers

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.

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.

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


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