Create One-Dimensional array from evaluate

Viewed 102

Background:

I'm trying to loop through a series of sheets in a workbook from an Array given certain sheets names.

Code

Instead of the more conventional:

Sub test()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
    Debug.Print ws.Name
Next ws

End Sub

I'm using a different method that makes it all a bit easier to extend the array to like 10 worksheets, or start at another number, like so:

Sub test()

Dim ws As Worksheet
Dim arr As Variant: arr = ["Sheet"&ROW(1:3)]

For Each ws In ThisWorkbook.Sheets(arr)
    Debug.Print ws.Name
Next ws

End Sub

Problem:

However ["Sheet"&ROW(1:3)] will create a two-dimensional array which will throw an Error 13 on the start of the For each... loop because the array expects a one-dimensional array.

I can addres this problem using a simple TRANSPOSE like:

Sub test()

Dim ws As Worksheet
Dim arr As Variant: arr = [TRANSPOSE("Sheet"&ROW(1:3))]

For Each ws In ThisWorkbook.Sheets(arr)
    Debug.Print ws.Name
Next ws

End Sub

Question:

Maybe my brain is tired but right now I fail to understand why ["Sheet"&ROW(1:3)] would not create a one-dimensional array. Does anybody know?

I'm afraid the answer is simple, and I'll have one of those "Ahaaaa" moments.

1 Answers

In Short:

ROW(1:3) is a Range. Ranges that contain more than one cell are two dimensional arrays. Thus you get a two dimensional return.

Related