Declare and Set Worksheet
To be able to operate on a worksheet using VBA you have to specify which worksheet. Below discusses the many ways you can do this. The sample code declares and sets variables for the worksheet as a best practice. If you use the Code VBA add-in using Set will add the declaration automatically. The below sections give an overview of the ways to Set a worksheet.
|It is possible to not use a variable in your code, but to operate directly on available methods that return a worksheet.
As an example |
Set a Worksheet you just added
You can add a new worksheet and directly start working on it. Code VBA has builders which make it easy to tweak the resulting code.
Set Worksheet in a specified workbook - by name
If you write macros for a specific line of business, your worksheet actions will generally assume a specific (type of) workbook as it's context. As an example, below code could be included in an Excel file with a worksheet called Locations. If your macro would involve more worksheets, you would probably name the variable wsLocations.
Dim wb As Workbook: Set wb = ThisWorkbook Dim ws As Worksheet Set ws = wb.Sheets("Locations") If ws Is Nothing Then Exit Sub 'possible way of handing no worksheet was set End If
Returns Nothing if no sheet exists with that name. In fact any approach that tries to return a worksheet may return Nothing, so you should always check if a valid worksheet was set.
|ws and wb are commonly used short names and prefixes for Worksheet and Workbook|
Set Worksheet in general purpose macros
If you want your macro to be usable in any workbook, you probably want it to work on any active sheet (= the one on top) - for example, clear its contents.
Dim wsActiveSheet As Worksheet Set wsActiveSheet = Application.ActiveSheet wsActiveSheet.UsedRange.Clear
ActiveSheet returns the active sheet (the sheet on top) in the active workbook or active window. Above code shows the use of the default use:
ActiveSheet in Workbook
If your macro only is to be used in a specific workbook, for example one that it has just opened, use the more specific
Dim wb As Workbook: Set wb = Dim wsActiveSheet As Worksheet Set wsActiveSheet = wb.ActiveSheet
ActiveSheet in Window
If the workbook appears in more than one window,
wb.ActiveSheet may be different in different windows. In that case use:
Dim wnd As Window: Set wnd = Dim wsActiveSheet As Worksheet Set wsActiveSheet = wnd.ActiveSheet
Obtaining the worksheet from a property of another object
Dim rng As Range: Set rng = Dim wsWorksheet As Worksheet Set wsWorksheet = rng.Worksheet
Next and Previous Worksheet
This way of setting a worksheet is available for both Worksheet and Chart objects
Dim ws As Worksheet: Set ws = Dim wsNext As Worksheet Set wsNext = ws.Next
Dim ws As Worksheet: Set ws = Dim wsPrevious As Worksheet Set wsPrevious = ws.Previous
Parent property of a PageBreak object
Dim hpgbr As HPageBreak: Set hpgbr = Dim wsParent As Worksheet Set wsParent = hpgbr.Parent