CODEWITHSUNDEEP
arraysvba
Stupid_Intern
Stupid_Intern

Reputation: 3450

How to insert a new value at certain index in an array and shift everything down by one index?

let's say if I have this array in vba

a(0) = 1
a(1) = f
a(2) = g
a(3) = 4
.
..
..
a(k) = a

and I want to insert a new value at (0) and shift every value down by one index like this

a(0) = newVal
a(1) = 1
a(2) = f
a(3) = g
a(4) = 4
.
..
..
a(k+1) = a

is there a short hand code to do this if not what's the fastest way to do this?

I can do this with simple for loop but I was wondering if there is more efficient way?

Example this is what I can come up with

Dim temp()

Redim Preserve temp(0)
temp(0) = "newVal"

For i= lbound(a) + 1 to ubound(a) + 1
     redim preserve temp(i)
     temp(i) = a(i-1)
Next i

Upvotes: 3

Views: 15251

Answers (4)

Keith Swerling
Keith Swerling

Reputation: 185

Public Function InsertFrontArray(InputArr As Variant, value As Variant) As Variant

InsertFrontArray = Split(value & "|" & Join(InputArr, "|"), "|")

End Function

Upvotes: 1

Ben Philipp
Ben Philipp

Reputation: 1877

Generalizing @Shai Rado's answer, here is the ReDim approach as a general reusable function. Parameter index is optional; if omitted, the new item will be added to the end (like push)

Function ArrayInsert(ByRef a As Variant, NewVal As Variant, Optional index As Integer = -1) As Variant
    Dim i As Long
    ReDim Preserve a(0 To UBound(a) + 1)
    If index = -1 Then index = UBound(a)
    For i = UBound(a) To index + 1 Step -1
        a(i) = a(i - 1)
    Next i
    a(index) = NewVal
    ArrayInsert = a
End Function

for convenience, this mutates the input array a as well as returning the mutated array.

Upvotes: 1

user3598756
user3598756

Reputation: 29421

if you don't mind having all string values at the end

Dim newVal As Variant
Dim tempVar As Variant

newVal = "newValue"

tempVar = newVal & "|"  & Join(a, "|")
ReDim a(0 To k + 1) As Variant
tempVar = Split(tempVar, "|")
For i = 0 To k + 1
    a(i) = tempVar(i)
Next

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33692

The code below will add a value (through variable NewVal) to an existing populated array. You can use the code to add an element in the middle of the array or the end (if you need to), you just need to modify the value of ElemId.

Note: If you are reading the array a values from a worksheet's range, then the code can be simplified.

Code 

Option Explicit

Sub AddElemToArray()

Dim a() As Variant
Dim ElemId  As Long, i As Long
Dim NewVal As Variant

ReDim a(0 To 4) '<-- modify th value 4 to your array size
NewVal = "Test"

a(0) = 1
a(1) = "f"
a(2) = "g"
a(3) = 4
a(4) = "a"

ReDim Preserve a(0 To UBound(a) + 1)

ElemId = 2 '<-- which element ID inside the array to modify

For i = UBound(a) To ElemId + 1 Step -1
    a(i) = a(i - 1)
Next i
a(ElemId) = NewVal

End Sub

Upvotes: 3

Related Questions