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.
In VBA, to name a worksheet does not need any special skills. However, we need to reference which sheet name we are changing by entering the current sheet name. For example, if we want to change the “Sales” sheet, we need to call the sheet by its name using the Worksheet object.
So how do you declare a workbook and worksheet object. Set WS = WB. Worksheet(“Sheet1”)'Let's assume I want to assign the worksheet Sheet1 to the worksheet object WS. Now that you have declared and initialized the workbook and worksheet objects you can access the objects actions and properties.
Dim wss As Sheets and Set wss = ThisWorkbook. Worksheets returns a Sheets collection object that only contains the worksheets (and not charts or macro sheets). And you can use Debug. Print wss(1). Name for example.
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
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):
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)
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With