如何在Excel VBA中切割数组？ [英] How do I slice an array in Excel VBA?

问题描述

可以在Excel VBA中使用什么功能来切割数组？

解决方案

Application.WorksheetFunction.Index（array，row，column）

如果为行或列指定零值，那么您将获得指定的整个列或行。

示例：

Application.WorksheetFunction.Index（array，0，3）

这将给你整个第3列。 p>

如果您将行和列指定为非零，那么您只能获得特定的元素。
没有简单的方法可以获得比完整行或列更小的片段。

限制：有限制如果您使用的是较新版本的Excel，则可以处理 WorksheetFunction.Index 的数组大小。如果 array 有超过65,536行或65,536列，则会引发类型不匹配错误。如果这是一个问题，请参阅这个更复杂的答案，这不是同样的限制。

这是我写的完成我的一维和二维切片的功能：

 公共函数GetArraySlice2D（Sarray As Variant，Stype As String，Sindex As Integer，Sstart As Integer，Sfinish As Integer）As Variant 
 
'此函数返回数组的一个切片，Stype是行或列
'Sstart是切片开始，Sfinish是切片结束（Sfinish = 0表示整个
'行或列被使用），Sindex是要切片的行或列
'（注：1总是第一行或第一列）
'Sindex值为0表示数组是一维的3/20/09 ljr 
 
 Dim vtemp（）As Variant 
 Dim i As Integer 
 
 On Err GoTo ErrHandler 
 
选择案例Sindex 
案例0 
如果Sfinish  -  Sstart = UBound（Sarray） -  LBoun d（Sarray）然后
 vtemp = Sarray 
 Else 
 ReDim vtemp（1到Sfinish  -  Sstart + 1）
对于i = 1 To Sfinish  -  Sstart + 1 
 vtemp（i）= Sarray（i + Sstart  -  1）
下一个i 
结束If 
 Case Else 
选择案例Stype 
案例row
如果Sfinish = 0 Or（Sstart = LBound（Sarray，2）和Sfinish = UBound（Sarray，2））然后
 vtemp = Application.WorksheetFunction.Index（Sarray，Sindex，0）
 Else 
 ReDim vtemp（1到Sfinish  -  Sstart + 1）
对于i = 1到Sfinish  -  Sstart + 1 
 vtemp（i）= Sarray（Sindex，i + Sstart  -  1）
 Next i 
 End If 
案例列
如果Sfinish = 0 Or（Sstart = LBound（Sarray，1）和Sfinish = UBound（Sarray，1））然后
 vtemp =申请对于i = 1到Sfinish  -  Sstart + 1 
 vtemp（i）= Sarray（i + Sstart  -  1，Sindex）
下一步i 
结束If 
结束选择
结束选择
 GetArraySlice2D = vtemp 
退出函数
 
 ErrHandler：
 Dim M As Integer 
 M = MsgBox（Bad Array Input，vbOKOnly，GetArraySlice2D）
 
结束函数
  

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

解决方案

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

这篇关于如何在Excel VBA中切割数组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！