通过 Application.Index 函数找到解决方案

Found solution via the Application.Index function

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

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).

Application.Index 函数的一些特性

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:

1. 与 Worksheet.Index 函数类似，如果行号或列号参数设置为零 (0)，您可以获得整个列或行项目.- 另一种通过传递双零参数创建二维数组的常见方法可以在 如何在Excel VBA中初始化二维数组

1. 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

可能使用数组参数 - 此函数不仅允许给定数字的已知索引指示，而且允许数组参数提取行"；或列"，因此可以通过 Array(1,2,3) 作为列数组参数来指示所需的一组列，例如 A:C.

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.

过滤效果 - 此外，我了解到可以将选择减少到仅某些列(行)，例如通过 Array(1,3) 甚至改变内部顺序，例如Array(3,2,1)`.

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)`.

重组 - 然而，最令人惊讶的事实是可以重复列选择，例如通过 Array(1,1,2,3)甚至 Array(0,1,2,3) 其中 0 项与第 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.

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

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

代码示例

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

如何测试结果

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

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

警告

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

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.

最近的一些Application.Index示例

Some recent Application.Index examples

• 从 sheet1 复制A、B、C、G、F、R、S、T列到A、B、C、D、E、F、G、H列中的sheet2
• 使用 VBA 选择多条件
• 如何加入以逗号分隔的命名范围的返回值