Add column (as first) with 1 to exsiting Variant Array in VBA

Question

I have a array which have 1 or more columns and now I want to add one more column (consists only of 1), but I don't know how do do that. The situation looks like that:

My code:

Dim X() As Variant
X = Range("A1:C3").Value2

It's is important to put column with 1 as first. Probably I need to use ReDim Preserve but nothing works for me.

What are you doing with the array later? Is the `1` purely for processing purposes, or do you need to eventually write it back to the sheet? — Comintern, Dec 08 '16 at 22:54
The beauty of a `1` based array is that you can treat an index of `0` as whatever you want. Just hard code it instead of accessing it from the array. — Comintern, Dec 08 '16 at 23:00
Well I solved it, other way u suggest but it works! Ty for responding. — Roberto, Dec 08 '16 at 23:12
@Roberto - FYI a late post to your question how to insert columns into a variant array using some relatively unknown pecularities of `Application.Index()`. - Would appreciate feedback; feel free to accept/upvote. — T.M., Oct 10 '20 at 17:53

score 0 · Answer 1 · answered Dec 08 '16 at 23:23

I think you have some options, but instead of extending the index of the array and transposing, trying to move the values etc which seems too much of a hassle, I would rather add 1 to the Excel range and then create the array:

Range("B1:D3").Value2 = Range("A1:C3").Value2
Range("A1:A3").Value2 = 1
X = Range("A1:D3").Value2

score 0 · Answer 2 · answered Dec 09 '16 at 01:15

Resize the Array adding a column to the last dimension
Shift all the data to the right.
Assign 1 to the first position in each row

Sub AddColumnShiftData()

    Dim X As Variant
    Dim i As Long, j As Long
    X = Range("A1:C3").Value2
    ReDim Preserve X(1 To 3, 1 To 4)

    For i = 1 To UBound(X)
        For j = UBound(X, 2) To 2 Step -1
            X(i, j) = X(i, j - 1)
        Next
        X(i, 1) = 1
    Next

End Sub

score 0 · Answer 3 · answered Oct 10 '20 at 04:38

Try matrix multiplication by the identify matrix....Well almost identity matrix. Then add 1 to every element in of the resulting matrix. You can use the Excel's Worksheet function for matrix multiplication.

Almost identity matrix

Dim X As Variant
X = Range("A1:C3").Value2
Dim Y As Variant
n = UBound(X, 2)
m = n + 1
Z = UBound(X, 1)
ReDim Y(1 To n, 1 To m)


'Set All values to zero
For i = 1 To n
    For j = 1 To m
        Y(i, j) = 0
    Next j
Next i

' Set  offset diagonal to 1
For i = 1 To n
    Y(i, i + 1) = 1
Next i

' Matrix MMult
X = Application.WorksheetFunction.MMult(X, Y)
' Add 1 to the first column
For i = 1 To Z
    X(i, 1) = 1
Next i

score 0 · Answer 4 · answered Oct 10 '20 at 17:39

Alternative via Application.Index()

Just for fun (note that the resulting array is a 1-based 2-dim array):

Sub AddFirstIndexColumn()
    Const FIXEDVALUE = 1         ' value to replace in new column 1
    '[1] get data
    Dim v: v = getExampleData()

    '[2] define column array inserting first column (0 or 1) and preserving old values (1,2,3)
    v = Application.Index(v, _
                          Application.Evaluate("row(1:" & UBound(v) & ")"), _
                          Array(1, 1, 2, 3))     ' columns array where 0 reinserts the first column
    ' [3] add an current number in the first column
    Dim i As Long
    For i = LBound(v) To UBound(v): v(i, 1) = FIXEDVALUE: Next i
End Sub

Function getExampleData()
' Method: just for fun a rather unusual way to create a 2-dim array
' Caveat: time-consuming for greater data sets; better to assign a range to a datafield array
Dim v
    v = Array(Array(2, 3, 5), Array(3, 8, 9), Array(4, 2, 1))
    v = Application.Index(v, 0, 0)
getExampleData = v
End Function

Related links

Some pecularities of `Application.Index()

Insert vertical slices into array

Add column (as first) with 1 to exsiting Variant Array in VBA

4 Answers4