我如何切片在Excel VBA中的数组? [英] How do I slice an array in Excel VBA?
问题描述
我可以在Excel中使用VBA函数什么切片数组?
Application.WorksheetFunction.Index(数组,行,列)
块引用>如果您指定的行或列中的值为零,那么你得到的是指定的整列或行。
例如:
Application.WorksheetFunction.Index(数组,0,3)
块引用>这会给你整个第3列。
如果您指定行和列非零,那么你就只能得到特定元素。
有没有简单的方法来获得比一个完整的行或列小片。限制:有一个限制数组大小
WorksheetFunction.Index
如果你使用的Excel的新版本可以处理。如果阵列
有超过65,536行或列65,536,那么它抛出一个类型不匹配的错误。如果这是你的一个问题,那么请参见这种更复杂的答案这是不是受到同样的限制。下面是我写的做我所有的一维和二维切片功能:
公共功能GetArraySlice2D(Sarray作为变型,S型作为字符串,SINDEX整数,Sstart整数,Sfinish作为整数)为Variant这个函数返回一个数组的一个切片,S型或者是行或列
Sstart开始片,Sfinish是片末(Sfinish = 0表示整个
'行或列被取),SINDEX是行或列要被切断
(注:1永远是第一行或第一列)
'为0的SINDEX值意味着该阵列是一维零九年三月二十零日LJR昏暗的VTEMP()为Variant
昏暗我作为整数在错误转到ErrHandler选择案例SINDEX
案例0
如果Sfinish - Sstart = UBound函数(Sarray) - LBOUND(Sarray)然后
VTEMP = Sarray
其他
REDIM VTEMP(1到Sfinish - Sstart + 1)
对于i = 1到Sfinish - Sstart + 1
VTEMP(ⅰ)= Sarray第(i + Sstart - 1)
接下来,我
万一
否则案例
选择案例S型
案例行
如果Sfinish = 0或(Sstart = LBOUND(Sarray,2)和Sfinish = UBound函数(Sarray,2))然后
VTEMP = Application.WorksheetFunction.Index(Sarray,SINDEX,0)
其他
REDIM VTEMP(1到Sfinish - Sstart + 1)
对于i = 1到Sfinish - Sstart + 1
VTEMP(I)= Sarray(SINDEX,I + Sstart - 1)
接下来,我
万一
案列
如果Sfinish = 0或(Sstart = LBOUND(Sarray,1)和Sfinish = UBound函数(Sarray,1))。然后
VTEMP = Application.WorksheetFunction.Index(Sarray,0,SINDEX)
其他
REDIM VTEMP(1到Sfinish - Sstart + 1)
对于i = 1到Sfinish - Sstart + 1
VTEMP(ⅰ)= Sarray第(i + Sstart - 1,SINDEX)
接下来,我
万一
结束选择
结束选择
GetArraySlice2D = VTEMP
退出功能ErrHandler:
昏暗中号作为整数
M = MSGBOX(坏阵列输入,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. Ifarray
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屋!