通过评估创建一维数组 [英] Create One-Dimensional array from evaluate

问题描述

背景:

Background:

我正在尝试从给定某些工作表名称的Array遍历工作簿中的一系列工作表.

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

代码

Code

而不是更常规的:

Sub test()

Dim ws As Worksheet

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

End Sub

我正在使用一种不同的方法，这使得将数组扩展到10个工作表或从另一个数字开始都变得更加容易，就像这样:

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:

但是["Sheet"&ROW(1:3)]将创建一个二维数组，该数组将在For each...循环的开头抛出Error 13，因为该数组需要一维数组.

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.

我可以使用简单的TRANSPOSE来解决此问题:

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:

也许我的大脑很累，但是现在我不明白为什么["Sheet"&ROW(1:3)]不能创建一维数组.有人知道吗?

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?

恐怕答案很简单，我会遇到"Ahaaaa"时刻之一.

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

推荐答案

简而言之:

ROW(1:3)是一个范围.包含多个单元格的范围是二维数组.这样就得到了二维收益.

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

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