问题描述
我正试图了解worksheets与& Excel VBA中的worksheet obj.我从MSDN参考资料中了解到worksheet是worksheets&的子obj. sheets.
I'm trying to understand the difference between worksheets & worksheet obj in Excel VBA. I understand from the MSDN reference that the worksheet is a child obj of worksheets & sheets.
但是，我们使用worksheets obj而不是worksheet obj引用每个工作表.例如
However, we reference every worksheet using the worksheets obj, not the worksheet obj. e.g.
worksheets("ExcelIsCool").range("a1").value -> CORRECT
worksheet ("ExcelIsCool").range("a1").value -> INCORRECT
我的问题是两者之间有什么区别?
My question is whats the difference between the two?
worksheet仅用于声明变量(到目前为止，我唯一使用过的地方).例如
Is worksheet only used for declaring a variable (the only place where I've used so far). e.g.
dim wks as worksheet
推荐答案
工作表是工作表对象的集合.一个工作簿"具有一个或多个工作表"-集合，而集合中的特定对象是工作表".
Worksheets is a collection of Worksheet objects. A "Workbook" has one or more "Worksheets" - the collection, whilst a specific object from the collection is a "Worksheet".
在您的示例中，您试图按名称从集合中选择一个工作表，但是由于一个工作表本身不是工作表集合，因此它将无法工作.当您执行ThisWorkbook.Worksheets("MyWorksheetName")时，返回的值是一个工作表对象，因此您可以按照所述的方式与其进行交互.
In your example, you are trying to select a worksheet from the collection by name, but since one worksheet by itself isn't a worksheet collection, it will not work. When you do ThisWorkbook.Worksheets("MyWorksheetName") the returned value is a worksheet object, so you can interact with it in the way described.
用例
好吧，当您想弄清楚您正在使用工作表时，要保持功能不承担多个职责以及希望获得良好的智能支持来检查Worksheet成员时，请使用Worksheet .因此，如果您将工作表作为某个函数的参数(例如，某个函数从给定列的底部开始查找最后一行数据)，则您的函数签名应如下所示:
Well, use Worksheet when you want it to be clear you are working with a worksheet, when you want to keep your functions clean of multiple responsibilities, and where you want good intellisense support inspecting the Worksheet's members. So if you are taking a worksheet as a parameter to some function (for example, a function that finds that last row of data from the bottom in a given column), your function signature could look like this:
Public Function GetLastRow(ByRef wsTarget as Excel.Worksheet, ByVal column as Long) as Long
GetLastRow = wsTarget.Cells(wsTarget.Rows.Count, column).End(xlUp).Row
End Function
这很好，因为这意味着您正在传递要引用的实际对象，而不是字符串或数字(其名称或索引).这也意味着被调用的函数(GetLastRow)不需要知道工作表所在的工作簿(ActiveWorkbook和ThisWorkbook并不总是相同的).这使调用者有责任找到工作表并确保获得正确的工作表，并保持功能清洁.用户可以使用工作表的变量名来调用它(如VBA编辑器面板之一所示):
This is nice, because it means you are passing around the actual object you want to refer to, rather than a string or number) (its name or index). It also means the called function (GetLastRow) doesn't need to know about which workbook the sheet is in (ActiveWorkbook and ThisWorkbook are not always the same). This gives the caller the responsibility to locate the sheet and make sure you get the right one, keeping the function clean. A user could call it with a worksheet's variable name (as displayed in one of the VBA editor panels):
last_row = GetLastRow(Sheet1, 1)
或其他任何引用工作表的方式(请参见此).
Or any of the other ways of referencing worksheets (see this).
只要您只想谈论一张纸，就可以使用Worksheet对象.明确将变量声明为工作表对象将为您提供适合使用工作表的智能选项.对于读者来说很清楚，您正在使用工作表，而不是字符串.
You use the Worksheet object anytime you want to talk about only one sheet. Explicitly declaring your variable as a Worksheet object will give you the intellisense options appropriate to working with a worksheet. And it's clear to a reader that you are working with a worksheet, not a string.
这篇关于工作表之间的差异工作表obj的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！