Remove an element from an array based on an index(vba)

Question

I want a vba function that takes an 2D(1 to n,1 to 1) array and an Index and remove an element(based on that index) from the array. I coded as follows:

'this function removes an element of a 2D(1 to n,1 to 1) array based on the input index
Function RemoveElementFromArray(Arr1, Index As Long)
    Dim Arr2
    Dim i, ElmToRemoveIndex As Long
    Dim UB1, LB1, UB2, LB2 As Long
   
    ElmToRemoveIndex = Index
    LB1 = LBound(Arr1): UB1 = UBound(Arr1)
    LB2 = LB1: UB2 = UB1 - 1
    If ElmToRemoveIndex < LB1 Or ElmToRemoveIndex > UB1 Then
        MsgBox "The index is out of range!", vbExclamation
    ReDim Arr2(LB2 To UB2, 1 To 1)
    ElseIf ElmToRemoveIndex = LB1 Then
        For i = LB1 To i = UB2
           Arr2(i, 1) = Arr1(i + 1, 1)
        Next
    ElseIf ElmToRemoveIndex > LB1 And ElmToRemoveIndex < UB1 Then
        For i = LB1 To i = ElmToRemoveIndex - 1
           Arr2(i, 1) = Arr1(i, 1)
        Next
        For i = ElmToRemoveIndex To i = UB2
           Arr2(i, 1) = Arr1(i + 1, 1)
        Next
    ElseIf ElmToRemoveIndex = UB2 Then
        For i = LB1 To i = UB2
           Arr2(i, 1) = Arr1(i, 1)
        Next
    End If
    RemoveElementFromArray = Arr2
End Function

but when I tried to use it in a sub I encountered a run-time error '13': Type mismatch, while I expected to get "saeed" printed!

Sub test()
    Dim Arr1(1 To 5, 1 To 1)
    Dim Arr2
    Dim ElmToRemoveIndex As Long
    Arr1(1, 1) = "ali"
    Arr1(2, 1) = "reza"
    Arr1(3, 1) = "ahmad"
    Arr1(4, 1) = "saeed"
    Arr1(5, 1) = "shah"
    ElmToRemoveIndex = 3
    
    Arr2 = RemoveElementFromArray(Arr1, ElmToRemoveIndex)
    Debug.Print Arr2(3, 1)
End Sub

What's the problem of this code?! please help me if you can.

your: ReDim Arr2(LB2 To UB2, 1 To 1) should be before the IF. Right now it only gets ReDimmed when ElmToRemoveIndex < LB1 Or ElmToRemoveIndex > UB1 — Scott Craner
– Scott Craner, Commented Apr 27, 2021 at 14:38
BTW: Dim UB1, LB1, UB2, LB2 As Long only declares LB2 as a Long the others are all Variant Also the Fors need only be like: For i = LB1 To UB2 — Scott Craner
– Scott Craner, Commented Apr 27, 2021 at 14:41

Scott Craner · Accepted Answer · 2021-04-27 14:59:48Z

2

As stated in the comments we need to redim the new array outside the IF.

Also the multiple Ifs can be replaced with a counter:

Function RemoveElementFromArray(Arr1 As Variant, Index As Long)

    Dim UB1 As Long
    UB1 = UBound(Arr1, 1)
    
    Dim LB1 As Long
    LB1 = LBound(Arr1, 1)
    
    Dim Arr2() As Variant
    ReDim Arr2(LB1 To UB1 - 1, 1 To 1)
    
    If Index < LB1 Or Index > UB1 Then
        MsgBox "The index is out of range!", vbExclamation
    Else
        Dim k As Long
        k = LB1
        
        Dim i As Long
        For i = LB1 To UB1 - 1
            If i <> Index Then
                Arr2(k, 1) = Arr1(i, 1)
                k = k + 1
            End If
        Next i
    End If
    
    RemoveElementFromArray = Arr2
End Function

edited Apr 27, 2021 at 14:59

answered Apr 27, 2021 at 14:54

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amir Hossein Ganji Over a year ago

very nice answer, thanks. you helped a lot.

Amir Hossein Ganji · Accepted Answer · 2021-04-27 16:27:29Z

thanks @Scott Craner your code is almost correct and with that idea and your help, I modify it as follows and reaches the desired results:

Function RemoveElementFromArray(Arr1 As Variant, Index As Long)
    Dim UB1, LB1 As Long
    Dim Arr2() As Variant
    Dim i, k As Long
    
    UB1 = UBound(Arr1, 1): LB1 = LBound(Arr1, 1)
    
    ReDim Arr2(LB1 To UB1 - 1, 1 To 1)
    
    If Index < LB1 Or Index > UB1 Then
        MsgBox "The index is out of range!", vbExclamation
    ElseIf Index = LB1 Then
        k = LB1
        For i = LB1 To UB1 - 1
            Arr2(k, 1) = Arr1(i + 1, 1)
            k = k + 1
        Next i
    ElseIf Index > LB1 And Index < UB1 Then
        k = LB1
        For i = LB1 To Index - 1
            Arr2(k, 1) = Arr1(i, 1)
            k = k + 1
        Next i
        k = Index
        For i = Index To UB1 - 1
            Arr2(k, 1) = Arr1(i + 1, 1)
            k = k + 1
        Next i
    ElseIf Index = UB1 Then
        k = LB1
        For i = LB1 To UB1 - 1
            Arr2(k, 1) = Arr1(i, 1)
            k = k + 1
        Next i
    End If
    
    RemoveElementFromArray = Arr2
End Function

What's your opinion? it's better, isn't it?

Collectives™ on Stack Overflow

Remove an element from an array based on an index(vba)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related