通过评估直接检索一维值数组 [英] Retrieve one-dimensional array of values directly through Evaluate

问题描述

背景:

Background:

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

In my former question I set up how I retrieved a range of worksheets in an array in order to loop through them.

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

I would like to take it a step further and instead retrieve an array of values, e.g.: {"Val1", "Val2", "Val3"}

代码:

Code:

要使用以下代码，您可以复制创建一组名为"Sheet1"，"Sheet2"等的工作表以实现的目的.然后输入一个模块:

To work with the following codes, you could replicate what I'm trying to achieve with creating a bunch of sheets, named "Sheet1", "Sheet2" etc etc. Then input a module:

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

This is how I would retrieve the worksheet array with dynamic row variables. You will see it will pull an array of sheetnames that we could use in for example: For Each ws In ThisWorkbook.Sheets(shts):

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

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

Now however, I would like to take an array of values directly from these sheets. This is how I would retrieve the cells I'm interested in (the same cell across all sheets), e.g. A1:

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

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

The cells references are rather pointless, but one example this would work with is when all values would be numeric and I want to be able to sum like this:

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

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

However I would like to pull them into an array variable directly (without a loop through sheets), no matter numeric or non-numeric, e.g: {5,3,"Val1",6}

问题:

Problem:

我已经尝试过在INDIRECT，VALUE甚至ADDRESS中进行几项操作，以首先将范围放入命名范围.所有这些尝试都是徒劳的，因为这些功能似乎都不适用于数组.例如:

I've tried several things amongst INDIRECT, VALUE, and even ADDRESS to put the range into a named range first. All attempts are futile since none of these functions seem to work with arrays. For example:

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

由于问题很可能是由于非连续范围引起的，因此会抛出带有一堆错误的数组.

Will throw an array with a bunch of errors as the problem most likely lays with non-contiguous ranges.

问题:

Question:

有什么方法可以直接通过评估成功检索一维数组?还是我正在寻找的东西根本不可能实现，在这种情况下，这使我回到了获取数组的循环中.

Any way to retrieve an one-dimensional array succesfully directly through evaluation? Or is what I'm seeking simply not possible, which in that case brings me back to a loop to get my array.

推荐答案

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

Eventually using CHOOSE in the .Evaluate did it for me:

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获取值的数组.

• Get a lower bound > lwr
• Get a upper bound > uppr
• Get an array of ranges through .Evaluate with those bounds
• Get an array of values through Join of the previous array within an .Evaluate that makes use of CHOOSE.

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

If for example Sheet2!A1 = 4, Sheet3!A1 = 5, Sheet4!A1 = 1, Sheet5!A1 = Test

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

The above code will then return the following for vals:

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

As you can see, it holds a one-dimensional array with variant values. Exactly what I wanted. I'm now able to get a dynamic array pulling values from different sheets in one go.

这篇关于通过评估直接检索一维值数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！