Is there a way to remove the first element of an array in VBA?
Something like javascript shift() method?
Option Explicit
Sub Macro1()
6 Answers
There is no direct method in VBA but you can remove the first element easily like this:
'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)
2 Comments
John Coleman
While this is a good answer and I am upvoting it as such, you could perhaps remark that if the goal is to have something like a queue then this would be a dreadfully inefficient way to implement it. Code that makes heavy use of things like this should probably be modified so as not to require it.
rheitzman
Note that there is a 0th element so the
For Each will always report something for the first (0th) element.There is unfortunately not. You have to write a method to do it. 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
1 Comment
wizclown
If you want to incorporate this subroutine in your code "as-is", be careful to change
lTop to UBound(ItemArray)If you have a string array, you could join, offset, and split again.
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
Returns
2
3
4
1 Comment
T.M.
Just for the sake of the art: it's always interesting to see other solutions avoiding loops (cf. my current approach) +1
Not an answer but a study on array addressing.
This code: ReDim Preserve matriz(1) matriz(1) = 5
Creates an array with two elements: 0 and 1 UBound() returns 1
Here is some code that may help explore the issue:
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
Out:
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
Comments
No direct method, but sort of work around without loops :-)
This approach uses an intermediate target range to
- [1] receive the array data (starting e.g. in cell
A10) and - .... get them back as resized 2-dim datafield (counting from cell
A11thus omitting the first element) and - [2] transpose it back to a flat 1-dim array
Example code
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
Example output in VBE's immediate window (Debug.Print)
a) original matriz(0 To 3) 0, 5, 10, 4
b) ~~> new matriz(1 To 3) 5, 10, 4
//Edit #1 Tricky alternative using combobox properties & methods
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
Help procedure RemoveElem
This help procedure profits from the integrated method .RemoveItem of a combobox control which you can get on the fly without need to create an extra userform *)
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
Example output in VBE's immediate window (Debug.Print)
a) original matriz(0 To 3) 0, 5, 10, 4
b) ~~> new matriz(1 To 3) 5, 10, 4
Related links
Can I return a 0-based array from ws.UsedRange?
*) I found the way to create a solo combobox at Create an empty 2d-array )
Comments
What follows is a function "Shift", which behaves like the shift method in JS, and an example of the use of "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
Output:
Original array:
one
two
three
four
Array having been "shifted":
two
three
four
