3

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.

7
  • 6
    ROW(1:3) is a range, a range is a two dimensional array of values. Commented Oct 22, 2019 at 13:46
  • 2
    To add onto Scott's comment, Row(1:3) is similar to Range(Cells(1,"A"),Cells(3,"XFD")). Commented Oct 22, 2019 at 13:53
  • 1
    @ScottCraner, this is as easy as "ABC" and I feel stupid now, truely having a "Ahaaaa" moment =). Care to answer the question below? Commented Oct 22, 2019 at 13:55
  • 1
    @JvdV that question format looks nice... i swear i've seen it somewhere lol. we all have those moments! Commented Oct 22, 2019 at 13:56
  • 1
    @JvdV was making a bit of a joke as it matches all of my questions as well, and at one point i was discussing on meta about having a more standardized format for SO to help ensure that there was a space for each "background", "issue", "question", and "related code", where the final answer was that it might seem appropriate for SO but not all sites in SE, so not going to happen... every time I see a post well constructed in similar sections it just makes me smile a little. Commented Oct 22, 2019 at 14:05

1 Answer 1

4

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.

Sign up to request clarification or add additional context in comments.

2 Comments

I just want to point that you are 99,999999% right and I totally upvoted. Just want to add that if the range is just 1 single cell, then it's not two dimensional array. Even more, you can't take the value into an array with something like Dim MyArray As Variant MyArray = Range("A1").Value. This will not create an array, even if the object is a range one because it's a single cell.
@FoxfireAndBurnsAndBurns not at all, need to make it correct. Do not want to mislead any future visitors.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.