VBA dim ws 作为工作表（不是工作表）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39677645/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA dim ws as worksheets (not worksheet)
提问by jamheadart
OK so, I know I can do this:
好的，我知道我可以这样做：
Dim ws as worksheet
Set ws = thisworkbook.worksheets("Sheet1")
and then do my fancy stuff with the wsworksheet object
然后用ws工作表对象做我喜欢的事情
I also know I can Dim wss as worksheetsand that using worksheets("Sheet1")returns the worksheet object. So why doesn't the following work?
我也知道我可以Dim wss as worksheets并且使用worksheets("Sheet1")返回工作表对象。那么为什么以下不起作用？
Dim wss as worksheets
Dim ws as worksheet
Set wss = thisworkbook.worksheets
Set ws = wss("Sheet1")
I've also tried:
我也试过：
Dim wss as worksheets
Dim ws as worksheet
Set ws = thisworkbook.wss("Sheet1")
but the latter just looks like I'm trying to rename/shorten "worksheets" which seems totally wrong. I'm trying to get the worksheets of a workbook in to one worksheets object called wss. This is more about trying to understand the heirachy than anything but for functional purposes I'm trying to get wss to encompass all worksheets from workbook x so I could just do ws = wss(1)instead of saying set ws = wb.worksheets(1)
但后者看起来就像我试图重命名/缩短“工作表”，这似乎完全错误。我正在尝试将工作簿的工作表放入一个名为 wss 的工作表对象中。这更多的是试图理解 heirachy 而不是出于功能目的，我试图让 wss 包含工作簿 x 中的所有工作表，这样我就可以做ws = wss(1)而不是说set ws = wb.worksheets(1)
Is that even possible or am I misunderstanding the worksheets/ worksheet relationship?
这是可能的还是我误解了工作表/工作表的关系？
回答by user3598756
you must declare wssas a Sheetsobject
您必须声明wss为Sheets对象
Dim wss As Sheets
Dim ws As Worksheet
Set wss = ThisWorkbook.Worksheets
Set ws = wss("Sheet1")
this is because Worksheetsproperty of Workbookobject returns a Sheetscollection, i.e. a collection that contains both Worksheetsand Chartsobject of the workbook
这是因为对象的Worksheets属性Workbook返回一个Sheets集合，即包含工作簿的Worksheets和Charts对象的集合
Should you need a collection of your WorkbookWorksheetsonly (not Charts) to be called like ws = wss(1) or the likes then you could adopt the following workaround with Collectionobject
如果您需要像 ws = wss(1) 或类似名称一样调用您WorkbookWorksheets唯一的 (not Charts)集合，那么您可以对Collectionobject采取以下解决方法
Option Explicit
Sub main()
Dim wss As Collection
Dim ws As Worksheet
Set wss = GetWorkSheets
Set ws = wss("Sheet1")
Set ws = wss(1)
End Sub
Function GetWorkSheets() As Collection
Dim wss As New Collection
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
wss.add ws, ws.Name
Next ws
Set GetWorkSheets = wss
End Function
