Geek Answers Handbook

Remove the first element of a VBA array

Is there a way to remove the first element of an array in VBA ?

Something like javascript shift() method?

 Option Explicit Sub Macro1() Dim matriz() As Variant Dim x As Variant matriz = Array(0) ReDim Preserve matriz(1) matriz(1) = 5 ReDim Preserve matriz(2) matriz(2) = 10 ReDim Preserve matriz(3) matriz(3) = 4 ReDim Preserve matriz(1 To UBound(matriz)) For Each x In matriz Debug.Print x Next x End Sub

This delays the error: Subscript out of range

+7
vba excel-vba excel
source share
3 answers

There is no direct method in VBA, but you can easily remove the first element as follows:

 '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)
+8
source share

Unfortunately not. You must write a method for this. One good example is 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
+4
source share

Not an answer, but a study of array addressing.

This code: ReDim Preserve matriz (1) matriz (1) = 5

Creates an array with two elements: 0 and 1 UBound () returns 1

Here are some codes that can help you fix the problem:

 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

Of:

 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
+2
source share

More articles:


All Articles