有没有一种方法可以在 VBA 中删除数组的第一个元素？
类似于 JavaScript 中的 shift() 方法？
Option Explicit
Sub Macro1()
6个回答
19
在VBA中没有直接的方法，但您可以像这样轻松地删除第一个元素：
'Your existing code
'...
'Remove "ReDim Preserve matriz(1 To UBound(matriz))"
For i = 1 To UBound(matriz)
matriz(i - 1) = matriz(i)
Next i
ReDim Preserve matriz(UBound(matriz) - 1)
- ManishChristian
2
4
很遗憾，没有现成的方法可以做到这一点。您需要编写一个方法来完成。一个很好的例子是http://www.vbforums.com/showthread.php?562928-Remove-Item-from-an-array
'~~> Remove an item from an array, then resize the array
Public Sub DeleteArrayItem(ItemArray As Variant, ByVal ItemElement As Long)
Dim i As Long
If Not IsArray(ItemArray) Then
Err.Raise 13, , "Type Mismatch"
Exit Sub
End If
If ItemElement < LBound(ItemArray) Or ItemElement > UBound(ItemArray) Then
Err.Raise 9, , "Subscript out of Range"
Exit Sub
End If
For i = ItemElement To lTop - 1
ItemArray(i) = ItemArray(i + 1)
Next
On Error GoTo ErrorHandler:
ReDim Preserve ItemArray(LBound(ItemArray) To UBound(ItemArray) - 1)
Exit Sub
ErrorHandler:
'~~> An error will occur if array is fixed
Err.Raise Err.Number, , _
"Array not resizable."
End Sub
- justkrys
1
如果您想将此子程序“原样”合并到您的代码中，请注意将
lTop更改为UBound（ItemArray）。 - wizclown3
如果您有一个字符串数组，您可以将它们连接起来，偏移，并再次拆分。
Public Sub test()
Dim vaSplit As Variant
Dim sTemp As String
Const sDEL As String = "||"
vaSplit = Split("1 2 3 4", Space(1))
sTemp = Join(vaSplit, sDEL)
vaSplit = Split(Mid$(sTemp, InStr(1, sTemp, sDEL) + Len(sDEL), Len(sTemp)), sDEL)
Debug.Print Join(vaSplit, vbNewLine)
End Sub
返回
2
3
4
- Dick Kusleika
1
只是为了艺术而言：看到其他避免循环的解决方案总是很有趣（参见我的当前方法）+1 - T.M.
2
这不是答案，而是关于数组寻址的研究。
这段代码：
ReDim Preserve matriz(1)
matriz(1) = 5
创建了一个包含两个元素0和1的数组。 UBound() 返回1。
下面的代码可能有助于探索这个问题：
Option Explicit
Sub Macro1()
Dim matriz() As Variant
Dim x As Variant
Dim i As Integer
matriz = Array(0)
ReDim Preserve matriz(1)
matriz(1) = 5
ReDim Preserve matriz(2)
matriz(2) = 10
ReDim Preserve matriz(3)
matriz(3) = 4
Debug.Print "Initial For Each"
For Each x In matriz
Debug.Print ":" & x
Next x
Debug.Print "Initial For i = 0"
For i = 0 To UBound(matriz)
Debug.Print ":" & matriz(i)
Next i
Debug.Print "Initial For i = 1"
For i = 1 To UBound(matriz)
Debug.Print ":" & matriz(i)
Next i
Debug.Print "remove one"
For i = 1 To UBound(matriz)
matriz(i - 1) = matriz(i)
Next i
ReDim Preserve matriz(UBound(matriz) - 1)
For Each x In matriz
Debug.Print ":" & x
Next x
Debug.Print "remove one more"
For i = 1 To UBound(matriz)
matriz(i - 1) = matriz(i)
Next i
ReDim Preserve matriz(UBound(matriz) - 1)
For Each x In matriz
Debug.Print ":" & x
Next x
End Sub
输出：
Initial For Each
:0
:5
:10
:4
Initial For i = 0
:0
:5
:10
:4
Initial For i = 1
:5
:10
:4
remove one
:5
:10
:4
remove one more
:10
:4
- rheitzman
1
没有直接的方法，但有一种不需要循环的解决方法 :-)
这种方法使用一个中间目标范围来
- [1] 接收数组数据（从单元格
A10开始）并 - .... 将它们作为调整大小的二维数据字段返回（从单元格
A11开始，因此省略了第一个元素）和 - [2] 将其转置回平坦的一维数组
示例代码
Option Explicit
Sub Macro1()
'Method: use temporary target range to restructure array
Dim matriz() As Variant
Dim rng As Range
'[0.1] Assign same data set to array as in original post
matriz = Array(0, 5, 10, 4)
Debug.Print "a) original matriz(" & LBound(matriz) & " To " & UBound(matriz) & ")", Join(matriz, ", ")
'instead of:
' ReDim Preserve matriz(0 To 3)
' matriz(0) = 0: matriz(1) = 5: matriz(2) = 10: matriz(3) = 4
'[0.2] Set temporary range to memory
Set rng = ThisWorkbook.Worksheets("Tabelle1").Range("A10").Resize(UBound(matriz) + 1, 1)
'[1] Write array data to range and reassign to matriz cutting first row
rng = Application.Transpose(matriz) ' fill in array data (transposed to column)
matriz = rng.Offset(1, 0).Resize(UBound(matriz), 1) ' assign data to (2-dim) array omitting first row
'[2] Transpose back to flat 1-dim array
matriz = Application.Transpose(Application.Index(matriz, 0, 1))
Debug.Print "b) ~~> new matriz(" & LBound(matriz) & " To " & UBound(matriz) & ")", Join(matriz, ", "),
End Sub
在VBE的立即窗口（
Debug.Print）中输出示例a) original matriz(0 To 3) 0, 5, 10, 4
b) ~~> new matriz(1 To 3) 5, 10, 4
//编辑 #1 使用组合框属性和方法的巧妙替代方案
Sub RemoveFirstElement()
'a) Assign same data set to array as in original post
Dim matriz() As Variant
matriz = Array(0, 5, 10, 4)
Debug.Print "a) original matriz (" & LBound(matriz) & " To " & UBound(matriz) & ")", Join(matriz, ", ")
'b) Remove first element in matriz (note 0-based indices!)
RemoveElem matriz, 0 ' << call help procedure RemoveElem
Debug.Print "b) ~~> new matriz (" & LBound(matriz) & " To " & UBound(matriz) & ")", Join(matriz, ", ")
End Sub
帮助程序 RemoveElem
这个帮助程序受益于组合框控件的集成方法.RemoveItem，您可以在不需要创建额外用户窗体的情况下即时获取。
Sub RemoveElem(arr, ByVal elemIndex As Long)
'Use combobox properties and methods on the fly (without need to create form)
With CreateObject("Forms.ComboBox.1")
'a) assign existing values
.List = Application.Transpose(arr)
'b) delete e.g. 1st element (0-based control indices!)
.RemoveItem elemIndex
'c) assign modified values to tmp array (losing 2nd dimension by transposition)
Dim tmp As Variant
tmp = Application.Transpose(.List)
'd) decrement base by 1 (from 1 to 0) - optional
ReDim Preserve tmp(0 To UBound(tmp) - 1)
'e) overwrite original array
arr = tmp
End With
End Sub
在VBE的即时窗口中（
Debug.Print），输出示例a) original matriz(0 To 3) 0, 5, 10, 4
b) ~~> new matriz(1 To 3) 5, 10, 4
相关链接
我能从 ws.UsedRange 返回一个以0为基础的数组吗？
*) 我发现了创建独立组合框的方法，参见创建一个空的2D数组)
- T.M.
0
以下是一个名为“Shift”的函数，它的行为类似于JS中的shift方法，并且还有一个使用“Shift”的示例。
Sub tryShift()
Dim aRy As Variant, sT As Variant
aRy = Array("one", "two", "three", "four")
Debug.Print "Original array:"
For Each sT In aRy
Debug.Print sT
Next
aRy = Shift(aRy)
Debug.Print vbCrLf & "Array having been " & Chr(34) & "shifted" & Chr(34) & ":"
For Each sT In aRy
Debug.Print sT
Next
End Sub
Function Shift(aRy As Variant)
Dim iCt As Integer, iUbd As Integer
iCt = 0
iUbd = UBound(aRy)
Do While iCt < iUbd
aRy(iCt) = aRy(iCt + 1)
iCt = iCt + 1
Loop
ReDim Preserve aRy(UBound(aRy) - 1)
Shift = aRy
End Function
输出：
Original array:
one
two
three
four
Array having been "shifted":
two
three
four
- jwf3148
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
For Each循环将始终针对第一个（第0个）元素报告某些内容。 - rheitzman