Retrieve one-dimensional array of values directly

Question

Background:

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

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

Code:

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

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 & "))")

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, ",") & ")")

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:

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.

Answer 1

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, ",") & "))")

What I did here:

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

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

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.