If i have this code is there a simple way to add item1 to the beginning of the array and item2 to the end of the array in VBA?

The below code currently runs.

Dim nameArray as variant
Dim k as integer
Dim item1 as string
Dim item2 as string

k = 1
nameArray = Range(Cells(2, 3), Cells(5, 3)).Value
    For Each i In nameArray
        newcol = baseclmn   k
        tblComp.ListColumns.Add(newcol).Name = i
        k = k   1
    Next I

Thanks for any help you can offer

CodePudding user response：

You could start off with a larger array and replace the first and last items.

Dim nameArray as variant

nameArray = Range(Cells(1, 3), Cells(6, 3)).Value

nameArray(LBound(nameArray), 1) = "Item1"
nameArray(UBound(nameArray), 1) = "Item2"

CodePudding user response：

If you want to expand the array, perhaps something like this:

nameArray = Range(Cells(2, 3), Cells(5, 3)).Value

Dim newArray
ReDim newArray(1 to Ubound(nameArray, 1)   2, 1 to Ubound(nameArray, 2)) 'add two rows

newArray(1, 1) = "item1"
newArray(Ubound(newArray, 1), 1) = "item2"

Dim i As Long
For i = LBound(nameArray, 1) To Ubound(nameArray, 1)
   newArray(i   1, 1) = nameArray(i, 1)
Next

Explanation:

nameArray is a 2-dimensional array, where the first dimension corresponds to rows and the second to columns. Note that this array is one-based, i.e. the first index is 1 and not 0.

The code uses ReDim to create a new array, containing

• Two more rows than nameArray
• The same number of columns as nameArray.

Then it adds the first and last items:

• newArray(1, 1) = "item1": 1, 1 corresponds to the first row, first column.
• newArray(Ubound(newArray, 1), 1) = "item2": Ubound(newArray, 1) corresponds to the last row, and 1 again corresponds to the first column.

Finally it uses a loop to read the items from nameArray into the middle of newArray.

Further helpful reading includes Arrays and Ranges in VBA.