#activesheet

Im YouTube-Video am Ende des Beitrags bekommen Sie von dem Excel-Experten Kai Weissmann demonstriert wie Sie Tabellenblätter in Excel mit VBA ansprechen können. Des Weiteren bekommen Sie aufgezeigt, wie Sie hierbei die häufigsten Fehler vermeiden können. Die folgenden Punkte werden unter anderem behandelt : Überblick zu Tabellenblättern in Excel und VBA Aktives Tabellenblatt mit ActiveSheet…

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