声明变量工作簿/工作表 vba

Declaring variable workbook / Worksheet vba

提问by Mads ?strup Christensen

I know this might come off as a trivial question, but I can't seem to declare a workbookor a worksheetas a variable in VBA. I have the following code, but I can't figure out what I am doing wrong, it should be straight forward. Normally I don't have any problems declaring variables such as Dim i As Integeretc.

我知道这可能是一个微不足道的问题，但我似乎无法将 aworkbook或 a声明worksheet为VBA. 我有以下代码，但我无法弄清楚我做错了什么，它应该是直截了当的。通常我在声明诸如Dim i As Integer等变量时没有任何问题。

sub kl() 

    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ActiveWorkbook
    Set ws = Sheet("name")

    wb.ws.Select

End Sub

When I run the above code, I receive a type missmatcherror.

当我运行上面的代码时，我收到一个type missmatch错误。

回答by Gary's Student

Use Sheetsrather than Sheetand activate them sequentially:

使用Sheets而不是Sheet并按顺序激活它们：

Sub kl()
    Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = ActiveWorkbook
    Set ws = Sheets("Sheet1")
    wb.Activate
    ws.Select
End Sub

回答by Mathieu Guindon

If the worksheet you want to retrieve exists at compile-time in ThisWorkbook(i.e. the workbook that contains the VBA code you're looking at), then the simplest and most consistently reliable way to refer to that Worksheetobject is to use its code name:

如果要检索的工作表在编译时存在于其中ThisWorkbook（即包含您正在查看的 VBA 代码的工作簿），那么引用该Worksheet对象的最简单且最可靠的方法是使用其代号：

Debug.Print Sheet1.Range("A1").Value

You can set the code nameto anything you need (as long as it's a valid VBA identifier), independently of its "tab name" (which the user can modify at any time), by changing the (Name)property in the Propertiestoolwindow (F4):

通过更改“属性”工具窗口 (F4)中的属性，您可以将代码名称设置为您需要的任何名称（只要它是有效的 VBA 标识符），而与其“选项卡名称”（用户可以随时修改）无关：(Name)

The Nameproperty refers to the "tab name" that the user can change on a whim; the (Name)property refers to the code nameof the worksheet, and the user can't change it without accessing the Visual Basic Editor.

该Name属性指的是用户可以随心所欲更改的“选项卡名称”；该(Name)属性是指工作表的代码名称，用户不能在不访问 Visual Basic 编辑器的情况下更改它。

VBA uses this code nameto automatically declare a global-scope Worksheetobject variable that your code gets to use anywhere to refer to that sheet, for free.

VBA 使用此代码名称自动声明一个全局范围的Worksheet对象变量，您的代码可以在任何地方免费使用该变量来引用该工作表。

In other words, if the sheet exists in ThisWorkbookat compile-time, there's never a need to declare a variable for it - the variable is already there!

换句话说，如果工作表ThisWorkbook在编译时存在，则永远不需要为其声明变量 - 变量已经存在！

---

---

If the worksheet is created at run-time (inside ThisWorkbookor not), thenyou need to declare & assign a Worksheetvariable for it.

如果工作表是在运行时（内部ThisWorkbook或外部）创建的，那么您需要为其声明并分配一个Worksheet变量。

Use the Worksheetsproperty of a Workbookobject to retrieve it:

使用对象的Worksheets属性Workbook来检索它：

Dim wb As Workbook
Set wb = Application.Workbooks.Open(path)

Dim ws As Worksheet
Set ws = wb.Worksheets(nameOrIndex)

---

---

Important notes...

重要笔记...

• Both the name and index of a worksheet can easily be modified by the user (accidentally or not), unless workbook structure is protected. If workbook isn't protected, you simply cannot assume that the name or index alone will give you the specific worksheet you're after - it's always a good idea to validate the format of the sheet (e.g. verify that cell A1 contains some specific text, or that there's a table with a specific name, that contains some specific column headings).

• Using the Sheetscollection contains Worksheetobjects, but can also contain Chartinstances, and a half-dozen more legacy sheet types that are not worksheets. Assigning a Worksheetreference from whatever Sheets(nameOrIndex)returns, risks throwing a type mismatchrun-time error for that reason.

• Not qualifyingthe Worksheetscollection is an implicit ActiveWorkbook reference- meaning the Worksheetscollection is pulling from whatever workbook is active at the moment the instruction is executing. Such implicit references make the code frail and bug-prone, especially if the user can navigate and interact with the Excel UI while code is running.

• Unless you mean to activatea specific sheet, you never need to call ws.Activatein order to do 99% of what you want to do with a worksheet. Just use your wsvariable instead.

• 除非工作簿结构受保护，否则用户可以轻松修改工作表的名称和索引（无论是否无意）。如果工作簿不受保护，您根本不能假设名称或索引本身就能为您提供所需的特定工作表 - 验证工作表的格式总是一个好主意（例如，验证单元格 A1 是否包含某些特定文本，或者有一个具有特定名称的表，其中包含一些特定的列标题）。

• 使用Sheets集合包含Worksheet对象，但也可以包含Chart实例，以及六种非工作表的遗留工作表类型。由于这个原因，Worksheet从任何Sheets(nameOrIndex)返回值中分配一个引用，都有引发类型不匹配运行时错误的风险。

• 不出线的Worksheets集合是一个隐含的ActiveWorkbook参考-这意味着Worksheets集合无论从任何工作簿是活跃在指令执行的瞬间拉动。此类隐式引用使代码脆弱且容易出错，尤其是当用户可以在代码运行时导航并与 Excel UI 交互时。

• 除非您想激活特定的工作表，否则您永远不需要调用ws.Activate来完成您想要对工作表执行的 99% 的操作。只需使用您的ws变量即可。

回答by DrMarbuse

Third solution: I would set wsto a sheet of workbook wbas the use of Sheet("name")always refers to the active workbook, which might change as your code develops.

第三种解决方案：我将设置ws为一张工作簿，wb因为使用Sheet("name")始终指的是活动工作簿，这可能会随着您的代码的发展而改变。

sub kl() 

    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ActiveWorkbook
    'be aware as this might produce an error, if Shet "name" does not exist
    Set ws = wb.Sheets("name")
    ' if wb is other than the active workbook
    wb.activate
    ws.Select

End Sub

回答by P K Prabhakar

to your surprise, you do need to declare variable for workbook and worksheet in excel 2007 or later version. Just add single line expression.

令您惊讶的是，您确实需要在 excel 2007 或更高版本中为工作簿和工作表声明变量。只需添加单行表达式。

Sub kl()

    Set ws = ThisWorkbook.Sheets("name")
    ws.select
End Sub

Remove everything else and enjoy. But why to select a sheet? selection of sheets is now old fashioned for calculation and manipulation. Just add formula like this

删除所有其他东西并享受。但为什么要选择一张纸呢？工作表的选择现在是用于计算和操作的老式。只需添加这样的公式

Sub kl()

    Set ws = ThisWorkbook.Sheets("name")
    ws.range("cell reference").formula = "your formula"
'OR in case you are using copy paste formula, just use 'insert or formula method instead of ActiveSheet.paste e.g.:
   ws.range("your cell").formula
'or
   ws.colums("your col: one col e.g. "A:A").insert
'if you need to clear the previous value, just add the following above insert line
   ws.columns("your column").delete

End Sub

回答by DannyBland

Try changing the name of the variable as sometimes it clashes with other modules/subs

尝试更改变量的名称，因为它有时会与其他模块/子程序发生冲突

 Dim Workbk As Workbook
 Dim Worksh As Worksheet

But also, try

但也，试试

 Set ws = wb.Sheets("name")

I can't remember if it works with Sheet

我不记得它是否适用 Sheet

回答by J Amick

I had the same issue. I used Worksheetinstead of Worksheetsand it was resolved. Not sure what the difference is between them.

我遇到过同样的问题。我用Worksheet而不是，Worksheets它解决了。不确定它们之间有什么区别。

回答by SG-Phil

Dim ws as Object

Set ws = Worksheets("name")

when declaring the worksheet as worksheet instead of an ojbect I had issues working with OptionButtons (Active X) in this worksheet (I guess the same will be with any Active-X element. When declared as object everything works fine.

当将工作表声明为工作表而不是 ojbect 时，我在使用此工作表中的 OptionButtons (Active X) 时遇到了问题（我想任何 Active-X 元素都会如此。当声明为对象时，一切正常。

回答by user1

Lots of answers above! here is my take:

楼上很多答案！这是我的看法：

Sub kl()

    Dim wb As Workbook
    Dim ws As Worksheet

    Set ws = Sheets("name")
    Set wb = ThisWorkbook

With ws
    .Select
End With

End Sub

your first (perhaps accidental) mistake as we have all mentioned is "Sheet"... should be "Sheets"

正如我们所提到的，您的第一个（可能是偶然的）错误是“工作表”......应该是“工作表”

The with block is useful because if you set wb to anything other than the current workbook, it will ececute properly

with 块很有用，因为如果您将 wb 设置为当前工作簿以外的任何内容，它将正确执行