New Post has been published on http://analystcave.com/excel-vba-worksheets-tutorial-vba-activesheet-vs-worksheets/
Excel VBA Worksheets: VBA Activesheet vs Worksheets, Select, Activate etc.
Today we are going to learn about VBA Worksheets. We will cover all the nuisances e.g. VBA Activesheet and how it compares to regular Worksheets, how to Select Worksheets, how to Activate Worksheets, Selecting vs Activating Worksheets… and everything else you need to know about the VBA Worksheet in general.
ThisWorkbook vs ActiveWorkbook
Some Excel Worksheets
Lets start with the basics. Before we start I want to stress and remind the difference between ActiveWorkbooks and ThisWorksbooks. In short:
ThisWorkbook – refers to the Workbook in which the VBA macro is running
ActiveWorkbook – refers to the Workbook which is in the topmost Excel Window
It is extra important to understand this difference and I encourage you to read my post on this topic first.
The Excel VBA Object Hierarchy
Secondly it makes sense to remind the Excel Object hierarchy.
At the top, at the root we have our Excel Application. The Excel Application represents the entire Excel process. Further down the tree we have our Workbooks. Each Application contains a collection of Workbooks. Looking into a single Workbook we will notice it contains a collection of Worksheets. Worksheets on the other hand as you know can define Ranges (not the same a single cells). Using a Range we can access its cells Values or Formulas.
Accessing VBA Worksheets
Now that we have that behind us lets explore the different ways in which we can access Worksheets in VBA:
ActiveWorkbook VBA Worksheets
The Sheets and Worksheets collections
Sheets within the ActiveWorkbook:
Dim ws as Worksheet, wsCollection as Sheets Set wsCollection = Sheets 'Get entire collection of Worksheets Set ws = Sheets(1) 'Get first Worksheet in ActiveWorkbook Set ws = Sheets("Sheet1") 'Get Worksheet named "Sheet1" in ActiveWorkbook
Similarly we can use Worksheets instead of Sheets.
Dim ws as Worksheet, wsCollection as Sheets Set wsCollection = Worksheets 'Get entire collection of Worksheets Set ws = Worksheets(1) 'Get first Worksheet in ActiveWorkbook Set ws = Worksheets("Sheet1") 'Get Worksheet named "Sheet1" in ActiveWorkbook
ThisWorkbook VBA Worksheets
The Sheets and Worksheets collections
Sheets within the ThisWorkbook:
Dim ws as Worksheet, wsCollection as Sheets Set wsCollection = Sheets 'Get entire collection of Worksheets Set ws = ThisWorkbook.Sheets(1) 'Get first Worksheet in ThisWorkbook Set ws = ThisWorkbook.Sheets("Sheet1") 'Get Worksheet named "Sheet1" in ThisWorkbook
Similarly we can use Worksheets instead of Sheets.
Dim ws as Worksheet, wsCollection as Sheets Set wsCollection = Worksheets 'Get entire collection of Worksheets Set ws = ThisWorkbook.Worksheets(1) 'Get first Worksheet in ActiveWorkbook Set ws = ThisWorkbook.Worksheets("Sheet1") 'Get Worksheet named "Sheet1" in ThisWorkbook
Worksheet VBA Name vs Excel Name
caption id=”attachment_11373″ align=”alignright” width=”213″] Worksheet VBA Name vs Excel Name[/caption]When speaking about Worksheets in ThisWorkbook, the VBA Name of a Worksheet is not the same as the Excel Name of a Worksheet. Let us understand this different. On the right we have a screen from an example Workbook. The VBAName string is the VBA Name of our Worksheet, on the other hand the Excel Name string is our Excel Name. You can use both names to refer to the same Worksheet but in different ways.
Using the VBA Name of a Worksheet
Worksheet VBA Name
We can refer to a VBA Worksheet by its VBA Name directly – just by typing it. This is very convenient and a good practice. That is because the VBA Name can’t be changed by a user by mistake from the level of Excel (not the VBE). Hence whatever name the user give from the Workbook level to your Worksheet – its VBA Name stays the same.
Using the Excel Name of a Worksheet
Referring to an Excel Worksheet from VBA is not a recommended practice. Below I am referring to the Worksheet I named Excel Name in my example above.
Dim ws as Worksheet Set ws = Worksheets("Excel Name") 'Get Worksheet named "Sheet1" in ActiveWorkbook
Not what you notice is that compared to acquiring the Worksheet by its VBA Name, when using the defaults Worksheets or Sheets object you land with the said Worsheet, but from the ActiveWorkbook. Usually this doesn’t do much of a difference. But I am highlighting that this is simply another place to make a common mistake. See both versions below:
Dim ws as Worksheet '---Sheet by Excel Name in ActiveWorkbook--- Set ws = Worksheets("Excel Name") 'Get Worksheet named "Sheet1" in ActiveWorkbook Set ws = ActiveWorkbook.Worksheets("Excel Name") 'Get Worksheet named "Sheet1" in ActiveWorkbook '---Sheet by Excel Name in ThisWorkbook--- Set ws = ThisWorkbook.Worksheets("Excel Name") 'Get Worksheet named "Sheet1" in ActiveWorkbook
The VBA ActiveSheet
As with Active vs ThisWorkbook you need to first understand the difference between Selecting a Worksheet and Activating it.
Selected vs Activated Worksheet, the differences:
Selected Worksheet – one or more Worksheets that have been selected within an Excel Window. Each Workbook has its own set of Selected Worksheets
ActiveWorksheet – the current Worksheet you are viewing and working in. The only top-most Worksheet in all Application Workbooks
From the definition above you see that there can be more than 1 Selected Worksheet, while only 1 ActiveSheet. In fact both are totally different things. Lets explore this in some examples:
Example 1: Explaining ActiveSheet
Let us say we have 2 Workbooks open. One is Book1.xlsm and there other is Book2.xlsm.
Lets open VBE in Book1.xlsm. We will add the following piece of code:
Sub TestActiveSheet() MsgBox ActiveSheet.Name End Sub
Now lets activate Book2.xlsm. Rename any Worksheet e.g. to MyActiveWorksheet. Next go to the Developer ribbon select Macros and run Books1.xlsm!TestActiveSheet. What happened? You probably got something like this: Although you ran a macro from Book1.xlsm, VBA considers always the top-most worksheet as the ActiveSheet.
Example 2: Explaining Selected vs ActiveSheet
Let us consider the same example. Open Book1.xlsm and add the following code to the VBE:
Sub SelectedVSActive() Dim res As String, ws As Worksheet res = "Selected sheets:" & vbNewLine For Each ws In ActiveWindow.SelectedSheets res = res & ws.Name & vbNewLine Next ws MsgBox res & vbNewLine & "ActiveSheet:" & vbNewLine & ActiveSheet.Name End Sub
Now do the following:
Activate workbook Book2.xlsm
Select a number of Worksheets (at least 2)
Run Book1.xlsm!SelectedVSActive macro
What happens? You should get something like this: You see that:
Only the top-most Worksheet is considered as the ActiveSheet
The ActiveSheet is ALWAYS considered as a Selected Worksheet within the ActiveWindow (this last part is important)
What happens if you change ActiveWindow to e.g. Windows(2)? You will see a different set of Selected Worksheets. Why? Because all Workbooks have Selected Worksheets. Hope you see the difference.
Activating VBA Worksheets
Activating Worksheet is easy in VBA. You do it using the Activate property of Worksheets. How to activate any Worksheet using VBA?
Dim ws as Worksheet '...Set ws to some Worksheet ws.Activate
Activate Worksheet examples
A few examples below:
Sheets(1).Activate 'Activate first Worksheet in ActiveWorkbook Sheet1.Activate 'Activate Sheet1 in ThisWorkbook Worksheets("MyNamedWorksheet").Activate 'Activate Excel named Worksheet in ActiveWorkbook
Selecting VBA Worksheets
Selecting Worksheets is a little more difficult as we may want to select a single Worksheet or more.
Selecting a single VBA Worksheet
Selecting a single VBA Worksheet is simple. Just use the Select property of a Worksheet.
Dim ws as Worksheet '...Set ws to some Worksheet ws.Select
Selecting a single Worksheet examples
A few examples below:
Sheets(1).Select 'Select first Worksheet in ActiveWorkbook Sheet1.Select 'Select Sheet1 in ThisWorkbook Worksheets("MyNamedWorksheet").Select 'Select Excel named Worksheet in ActiveWorkbook
Selecting multiple VBA Worksheets
Selecting more than 1 Worksheet is a little more complex as we need to leverage the VBA Array function:
Sheets(Array(1, 2)).Select 'Select first and second Worksheet in ActiveWorkbook Sheets(Array("Named1", "Named2")).Select 'Select 2 named Worksheets in ActiveWorkbook
This is not the most elegant way of doing it. Fortunately the Select function has an argument called Replace. Setting it to false will mean that the previously selected is not to be deselected. Thus we can extend our selections like this:
Sheets(1).Select Call Sheets(2).Select(False) Call Sheets(3).Select(False)
This will select the first three Worksheets in our ActiveWorkbook.
Deleting VBA Worksheets
To delete a VBA Worksheet we need to turn off the Application.DisplayAlerts
Application.DisplayAlerts = False Sheets(1).Delete Application.DisplayAlerts = True
You can delete multiple Worksheets in a similar fashion as how you can do a multiple Worksheet Select:
Application.DisplayAlerts = False Sheets(Array(1, 2)).Delete Application.DisplayAlerts = True
Copying VBA Worksheets
You can copy Worksheets within a Workbook or from other open Workbooks.
'Copy Sheet1 and paste before Sheet2 Worksheets("Sheet1").Copy Before:=Sheets("Sheet2") 'Copy Sheet1 and paste before Sheet2 in Workbook Book1.xlsm Worksheets("Sheet1").Copy Before:=Workbooks("Book1.xlsm").Sheets("Sheet2")
Moving VBA Worksheets
You can move Worksheets within a Workbook or from other open Workbooks.
'Move Sheet1 and paste before Sheet2 Worksheets("Sheet1").Move Before:=Sheets("Sheet2") 'Move Sheet1 and paste before Sheet2 in Workbook Book1.xlsm Worksheets("Sheet1").Move Before:=Workbooks("Book1.xlsm").Sheets("Sheet2")
Other VBA Worksheets properties
Below are other VBA Worksheet properties worth mentioning:
Worksheet property Description Example Calculate Calculates all dirty formulas on the current Worksheet
Dim ws as Worksheet '...Set ws ws.Calculate
Name Get or set the Excel Name of the Worksheet
Dim ws as Worksheet '...Set ws Debug.Print ws.Name 'Print Excel Name of Worksheet ws.Name = "New name" 'Set new name for Worksheet
Visible Display, Hide or make Worksheet VeryHidden. Possible values:
xlSheetVisible
xlSheetVeryHidden
xlSheetHidden
Dim ws as Worksheet '...Set ws ws.Visible = xlSheetHidden 'Make ws Worksheet Hidden
Related Posts
Word VBA Tutorial
Making a VBA automated survey in Word
Excel Substring and VBA Substring – Left, R...
Excel VBA Calculate distance between two addresses...
VBA Enum – using enumerations in VBA
.yuzo_related_post imgwidth:120px !important; height:110px !important; .yuzo_related_post .relatedthumbline-height:15px;background: !important;color:!important; .yuzo_related_post .relatedthumb:hoverbackground:#fcfcf4 !important; -webkit-transition: background 0.2s linear; -moz-transition: background 0.2s linear; -o-transition: background 0.2s linear; transition: background 0.2s linear;;color:!important; .yuzo_related_post .relatedthumb acolor:!important; .yuzo_related_post .relatedthumb a:hover color:!important;} .yuzo_related_post .relatedthumb:hover a color:!important; .yuzo_related_post .yuzo_text color:!important; .yuzo_related_post .relatedthumb:hover .yuzo_text color:!important; .yuzo_related_post .relatedthumb margin: 0px 0px 0px 0px; padding: 5px 5px 5px 5px; jQuery(document).ready(function( $ ) //jQuery('.yuzo_related_post').equalizer( overflow : 'relatedthumb' ); jQuery('.yuzo_related_post .yuzo_wraps').equalizer( columns : '> div' ); )