Excel-VBA - Insert new first column in datafield array without loops or API calls

Question

Intro

Last year @PrzemyslawRemin posed the question how to add a counter column to an existing matrix in VBA without additional loops and without modifying a worksheet.

The original matrix in this example was a (1-based 2-dim) datafield array resulting from (source cells simply contain their address strings; the inserted row to be filled with numbers)

Dim matrix As Variant
matrix = Range("A1:C5").value

Input matrix: ------------ ▼ Desired result:

+----+----+----+   +----+----+----+----+ 
| A1 | B1 | C1 |   |  1 | A1 | B1 | C1 |
+----+----+----+   +----+----+----+----+ 
| A2 | B2 | C2 |   |  2 | A2 | B2 | C2 | 
+----+----+----+   +----+----+----+----+
| A3 | B3 | C3 |   |  3 | A3 | B3 | C3 | 
+----+----+----+   +----+----+----+----+ 
| A4 | B4 | C4 |   |  4 | A4 | B4 | C4 | 
+----+----+----+   +----+----+----+----+ 
| A5 | B5 | C5 |   |  5 | A5 | B5 | C5 | 
+----+----+----+   +----+----+----+----+ 

Of course the idea suggesting itself is to use a redimmed newMatrix as Dy.Lee proposed, but this would include two loops to shift rows and columns:

Sub test()
Dim matrix As Variant, newMatrix()
Dim i As Long, n As Long, c As Long, j As Long
matrix = Range("A1:C5").Value
n = UBound(matrix, 1)
c = UBound(matrix, 2)
ReDim newMatrix(1 To n, 1 To c + 1)
For i = 1 To n
    newMatrix(i, 1) = i
    For j = 2 To c + 1
        newMatrix(i, j) = matrix(i, j - 1)
    Next j
Next i
Range("a1").Resize(n, c + 1) = newMatrix
End Sub

Another work around avoiding unnecessary loops would be to write the array back to a temporary worksheet starting at column B and collect the data from there again including column A:D, but this means modifying a worksheet.

Florent B. alone solved the problem via extremely fast API calls using MemCopy and there appeared no other approach since. - So for principal reasons it is of some interest if this should be the ultima ratio or if there can be found another approach.

? Modified question (No duplicate!)

Is there any possibility to insert a new first "column" in the existing datafield array

• without loops over "rows" and "columns" to shift the existing values,
• without worksheet modifications and
• without API calls using VBA?

Different from Prezmyslaw's OP I'm not using huge data sets, so that a limitation to approximately 64k rows would be possible (c.f. max. transposing limitation).

Answer 1

Found solution via the Application.Index function

I found a solution simply by trying out some unusual variations of the Application.Index function which I try to resume as a comprehensive generic overview to demonstrate the rich range of application. So any helpful addition is welcome (c.f. @chrisneilsen 's comment).

Some peculiarities of the the Application.Index function

Typically the index function would deliver a well defined item by its row and column position, but there are some not so widely known pecularities:

0. Similarly to the Worksheet.Index function you can get the entire column or row items if the row or column number argument is set to zero (0). - Another frequently unknown way to create a 2-dim array by passing a double-zero parameter can be found at How to initialize a 2-dim array in Excel VBA

1. Use of array arguments possible - This function allows not only the known index indications by given numbers, but also array parameters to extract "rows" or "columns", so it's possible to indicate a wanted set of columns, e.g.A:C via Array(1,2,3) as column array argument.

2. Filtering effects - Furthermore I learnt that it is possible to reduce the choice to some columns (rows) only, e.g. via Array(1,3) and even to change the internal order, e.g. Array(3,2,1)`.

3. Restructuring - The most surprising fact, however, is that it is possible to repeat a column choice, e.g. via Array(1,1,2,3) or even Array(0,1,2,3) where the 0 item is the same as column 1. This can be used to reach the same effect as a column insertion.

This last restructuring capability of the mentioned Index function is the key part of my approach:

Code example

Sub AddFirstIndexColumn()
  Dim v, i&, ws As Worksheet
  Set ws = ThisWorkbook.Worksheets("SourceSheet")  ' << change to source sheet name
' [1] get data
      v = ws.[A1:C5].Value2
' [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(0, 1, 2, 3))   ' columns array where 0 reinserts the first column
' [3] add an current number in the first column
      For i = LBound(v) To UBound(v): v(i, 1) = i: Next i
 End Sub

How to test the result

Just insert the following to the code above:

' [4a] test result by debugging in immediate window
      For i = LBound(v) To UBound(v)
          Debug.Print "#" & i & ": " & Join(Application.Index(v, i, 0), ", ")
      Next i
' [4b] test result by writing back to target sheet
      Dim ws2 As Worksheet
      Set ws2 = ThisWorkbook.Worksheets("TargetSheet")  ' << change to target sheet name
      ws2.Range("A1").Resize(UBound(v), UBound(v, 2)).Offset(0, 0) = v

Caveat

The found solution seems to be restricted to 65536 rows (possibly similar to array transposing limitation), so that you can't use it for bigger data.

Some recent Application.Index examples

• Copy from sheet1 columns A,B,C,G,F,R,S,T to sheet2 in columns A,B,C,D,E,F,G,H
• Multi criteria selection with VBA
• How to join returned values from named range separated by comma