Is there an easy way to add a string to beginning and end of VBA array

Question

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

`nameArray(1, 1) = "item1"`, `nameArray(Ubound(nameArray, 1), UBound(nameArray, 2)) = "item2"` — BigBen, Nov 09 '21 at 00:56
That sort of works but it appears to cut off some items. The range contains 4 values that are loaded into the array. Afterwords there should be six total when 1 is added to the beginning and end. However there appears to be only 4. Also does all that code go on one line? — Manuel, Nov 09 '21 at 01:08
Ah you need to expand the array. Probably easiest to create a new array, add in the first and last items (similar to the provided code), then read the 4 items into the middle of the array. — BigBen, Nov 09 '21 at 01:08
Are you able to expand on how to do that? This is my first time using an array in VBA. Thank you for your help — Manuel, Nov 09 '21 at 01:10

BigBen · Answer 1 · 2021-11-09T02:46:57.530

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.

That works perfectly! Are you able to explain a bit how it works? Im trying to learn — Manuel, Nov 09 '21 at 02:37
@Manuel added an explanation. Lmk if anything is still unclear. — BigBen, Nov 09 '21 at 02:47

score 2 · Accepted Answer · answered Nov 09 '21 at 02:55

2

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"

answered Nov 09 '21 at 02:55

norie

9,609
2
11
18

Yah this is probably easier lol. Though this approach will obviously break if the data to be read starts in row 1. – BigBen Nov 09 '21 at 02:56
Thats an interesting and clever idea. It appears to work perfectly. Can you explain what you mean when you say it will break? I appreciate your explanation on your answer as well that's hugely helpful. Edit: Oh I understand now. My table has a header so that will not be an issue – Manuel Nov 09 '21 at 03:03
This is brilliant – Manuel Nov 09 '21 at 04:10

T.M. · Answer 3 · 2021-11-12T08:34:03.253

You can enlarge (and/or restructure) the existing (vertical) nameArray in one go

via an undocumented feature of Application.Index()
using either the new Sequence() function (available since MS 365!) or a workaround via row evaluation (commented out in comment)

to pass a whole array(!) of row numbers (rowArr) as argument (instead of a single row index):

    newArray = Application.Index(nameArray, rowArr, 1)

where rowArr is a vertical array of sequential row numbers reflecting the currently existing indices, and 1 the unchanged column index.

Sub TopBottomAdditions()
'0. define 1-based 2-dim data field 
    Dim nameArray
    nameArray = Sheet1.Range(Cells(2, 3), Cells(5, 3)).Value 'i.e. data field of cells C2:C5
'1a create a sequence (array) ranging from 0 to elements count plus +1 (2 new elems)
    Dim rowArr              ' {0,1,2,..n,n+1}
    rowArr = WorksheetFunction.Sequence(UBound(nameArray) + 2, 1, 0)
    ''>workaround if you don't dispose of version MS 365
    '  rowArr = Evaluate("row(1:" & UBound(nameArray) + 2 & ")-1")
'1b keep existing values in rows 1..n and add top+bottom element
    'note: index 0 fetches element of existing index 1, n+1 gets a temporary error value
    Dim newArray
    newArray = Application.Index(nameArray, rowArr, 1)
'1c insert new top & bottom values
    newArray(1, 1) = "Top value"                    ' overwrites New elem no 1
    newArray(UBound(newArray), 1) = "Bottom value"  ' writes New last elem
End Sub

Syntax of Sequence()

   =SEQUENCE(rows,[columns],[start],[step])

Is there an easy way to add a string to beginning and end of VBA array

3 Answers3