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

Question

背景：

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

我想更进一步，取而代之的是检索一个值数组，例如：{"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""))")

将抛出一个带有一堆错误的数组，因为问题很可能在于不连续的范围。

问题：

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

标签： arraysexcelvba

Answer 1

最终使用CHOOSE为.Evaluate我做了：

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通过这些边界获取范围数组
• 通过使用. Join_.EvaluateCHOOSE

如果例如Sheet2!A1= 4, Sheet3!A1= 5, Sheet4!A1= 1, Sheet5!A1=Test

然后，上面的代码将返回以下内容vals：

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