arrays - Excel-VBA - 在数据字段数组中插入新的第一列，无需循环或 API 调用

Question

简介

去年@PrzemyslawRemin提出了这个问题how to add a counter column to an existing matrix in VBA 无需额外循环并且无需修改工作表。

此示例中的原始矩阵是一个(基于 1 的 2-dim)数据字段数组(源单元格仅包含其地址字符串；插入的行将填充数字)

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

输入矩阵:------------ ▼ 期望结果:

+----+----+----+   +----+----+----+----+ 
| 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 | 
+----+----+----+   +----+----+----+----+ 

当然，建议本身的想法是使用重新调暗的newMatrix，如Dy.Lee提议的那样，但这将包括两个循环来转换行和列:

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

避免不必要的循环的另一个解决方法是将数组写回从 B 列开始的临时工作表，并再次从那里收集数据，包括 A:D 列，但这意味着修改工作表.

Florent B.单独solved通过使用 MemCopy 进行极快的 API 调用 来解决这个问题，此后就没有其他方法了。 - 因此，出于主要原因，如果这应该是最后比率或者是否可以找到另一种方法，那么这是很有趣的。

► 修改后的问题(不重复!)

是否有可能在现有数据字段数组中插入新的第一“列”

• 无需循环“行”和“列”来移动现有值，
• 无需修改工作表并且
• 没有使用 VBA 调用 API？

与 Prezmyslaw 的 OP 不同，我没有使用巨大的数据集，因此可能限制为大约 64k 行(参见最大转置限制)。

Answer 1

通过 Application.Index 找到解决方案功能

我通过尝试 Application.Index 的一些不寻常变体找到了解决方案 函数，我尝试将其恢复为全面的通用概述，以展示丰富的应用范围。因此，欢迎任何有用的补充(参见@chrisneilsen 的评论)。

Application.Index 的一些特性功能

通常，索引函数会根据其行和列位置提供明确定义的项目，但有一些不那么广为人知的特性:

类似于 Worksheet.Index如果行号或列号参数设置为零 (0)，则可以使用函数获取整个列或行项目。 - 通过传递双零参数来创建 2 维数组的另一种常见未知方法可以在 How to initialize a 2-dim array in Excel VBA 中找到。

可以使用数组参数 - 此函数不仅允许通过给定数字指示已知索引，还允许数组参数提取“行”或“columns”，因此可以指示一组想要的列，例如A:C通过Array(1,2,3)作为列数组参数。

过滤效果 - 此外，我了解到可以将选择减少到仅某些列(行)，例如通过Array(1,3) and even to change the internal order, e.g. 数组(3,2,1)`。

重组 - 然而，最令人惊讶的事实是可以重复列选择，例如通过Array(1,1,2,3)甚至Array(0,1,2,3)其中0 item 与第 1 列相同。这可以用来达到与列插入相同的效果。

上述Index的最后重组能力函数是我方法的关键部分:

代码示例

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

如何测试结果

只需在上面的代码中插入以下内容:

' [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

警告

找到的解决方案似乎限制为 65536 行(可能类似于数组转置限制)，因此您无法将其用于更大的数据。

最近的一些Application.Index示例

• 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