Excel-VBA - Insert new first column in datafield array without loops or API calls
arrays, excel, matrix, vba
Solution
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:
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
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.
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)`.
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
Problem
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).