arrays - 直接通过 Evaluate 检索一维数组的值

Question

背景:

在我的former问题我设置了如何检索数组中的一系列工作表以便循环遍历它们。

我想更进一步，检索一个值数组，例如:{"Val1", "Val2", "Val3"}

代码:

要使用以下代码，您可以通过创建一堆工作表(名为“Sheet1”、“Sheet2”等)来复制我试图实现的目标。然后输入一个模块:

Sub Test

Dim lwr As Long: lwr = 2
Dim uppr As Long: uppr = 5

'Options will follow here *

End sub

这就是我使用动态行变量检索工作表数组的方法。您将看到它将提取我们可以使用的工作表名称数组，例如:For Each ws In ThisWorkbook.Sheets(shts):

Dim shts As Variant: shts = Application.Evaluate("TRANSPOSE(""Sheet""&ROW(" & lwr & ":" & uppr & "))")

但是现在，我想直接从这些工作表中获取一组值。这就是我检索我感兴趣的单元格(所有工作表中的同一单元格)的方式，例如A1:

Dim shts As Variant: shts = Application.Evaluate("TRANSPOSE(""Sheet""&ROW(" & lwr & ":" & uppr & ")&""!A1"")")

单元格引用毫无意义，但可以使用的一个示例是，当所有值都是数字时，我希望能够像这样求和:

Debug.Print Application.Evaluate("SUM(" & Join(shts, ",") & ")")

但是我想将它们直接拉入数组变量(无需循环遍历工作表)，无论是数字还是非数字，例如:{5,3,"Val1",6}

问题:

我已经尝试了 INDIRECT、VALUE 甚至 ADDRESS 等多种方法，首先将范围放入命名范围中。所有尝试都是徒劳的，因为这些函数似乎都不适用于数组。例如:

Dim vals As Variant: vals = Application.Evaluate("TRANSPOSE(INDIRECT(""Sheet""&ROW(" & lwr & ":" & uppr & ")&""!A1""))")

将抛出一个包含大量错误的数组，因为问题很可能出在非连续范围上。

问题:

有什么方法可以直接通过求值成功检索一维数组吗？或者我正在寻找的东西根本不可能，在这种情况下，这会让我回到循环来获取我的数组。

Answer 1

最终在 .Evaluate 中使用 CHOOSE 为我做到了:

Dim lwr As Long: lwr = 2
Dim uppr As Long: uppr = 5

Dim shts As Variant: shts = Application.Evaluate("TRANSPOSE(""Sheet""&ROW(" & lwr & ":" & uppr & ")&""!A1"")")
Dim vals As Variant: vals = Application.Evaluate("TRANSPOSE(CHOOSE(ROW(1:" & uppr - lwr + 1 & ")," & Join(shts, ",") & "))")

我在这里做了什么:

• 获取下限 > lwr
• 获取上限 > uppr
• 通过 .Evaluate 使用这些边界获取范围数组
• 通过使用 CHOOSE 的 .Evaluate 中前一个数组的 Join 获取值数组。

例如，如果 Sheet2!A1 = 4、Sheet3!A1 = 5、Sheet4 !A1 = 1, Sheet5!A1 = 测试

上面的代码将为vals返回以下内容:

如您所见，它包含一个具有变量值的一维数组。正是我想要的。我现在可以一次性获得一个动态数组，从不同的工作表中提取值。