[excel] Declaring variable workbook / Worksheet vba

I know this might come off as a trivial question, but I can't seem to declare a workbook or a worksheet as 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 Integer etc.

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 missmatch error.

This question is related to excel vba variables

The answer is


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.


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 Worksheet object is to use its code name:

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

You can set the code name to 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 Properties toolwindow (F4):

Sheet1 properties

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

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

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


If the worksheet is created at run-time (inside ThisWorkbook or not), then you need to declare & assign a Worksheet variable for it.

Use the Worksheets property of a Workbook object to retrieve it:

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 Sheets collection contains Worksheet objects, but can also contain Chart instances, and a half-dozen more legacy sheet types that are not worksheets. Assigning a Worksheet reference from whatever Sheets(nameOrIndex) returns, risks throwing a type mismatch run-time error for that reason.

  • Not qualifying the Worksheets collection is an implicit ActiveWorkbook reference - meaning the Worksheets collection 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 activate a specific sheet, you never need to call ws.Activate in order to do 99% of what you want to do with a worksheet. Just use your ws variable instead.


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


Use Sheets rather than Sheet and activate them sequentially:

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

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

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

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

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

I had the same issue. I used Worksheet instead of Worksheets and it was resolved. Not sure what the difference is between them.


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


Examples related to excel

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel?

Examples related to vba

Copy filtered data to another sheet using VBA Better way to find last used row Check if a value is in an array or not with Excel VBA Creating an Array from a Range in VBA Excel: macro to export worksheet as CSV file without leaving my current Excel sheet VBA: Convert Text to Number What's the difference between "end" and "exit sub" in VBA? Rename Excel Sheet with VBA Macro Extract Data from PDF and Add to Worksheet Quicker way to get all unique values of a column in VBA?

Examples related to variables

When to create variables (memory management) How to print a Groovy variable in Jenkins? What does ${} (dollar sign and curly braces) mean in a string in Javascript? How to access global variables How to initialize a variable of date type in java? How to define a variable in a Dockerfile? Why does foo = filter(...) return a <filter object>, not a list? How can I pass variable to ansible playbook in the command line? How do I use this JavaScript variable in HTML? Static vs class functions/variables in Swift classes?