Program by Levels

 

VBA for Excel Beginners Level

 

VBA for Excel Intermediate Level

 

VBA for Excel Advanced Level

 

Tutorial on VBA for Excel (Macros)

bringing you the simplest and most efficient ways
to do things in VBA for Excel

VBA Lesson 2-5: VBA for Excel for the Worksheets
(Levels: Intermediate, Advanced)

To develop a VBA procedure that is triggered by an event relating to the worksheet (when you select it, when you leave it...) see the lesson on events.

In alphabetical order here are the 14 VBA words that you need to learn to work efficiently with workooks:

Activate, Add, Copy, Delete, False, Paste, Select, Sheets, True, Visible, Worksheets, xlHidden, xlVeryHidden, xlVisible

A sheet has two "Name" properties. When you click on a sheet's name in the VBAProject window of the Visual Basic Editor (VBE) each sheet has two properties called Name. One with parentheses and one without. A worksheet has two names the one that appears on its tab in Excel (let's call this property "caption") and the one it has as VBA object in the VBAProject window of the Visual Basic Editor (VBE) (by default:  Sheet1, Sheet2....). So you can activate Sheet1 whose caption is "Balance" with both:

Sheets("Balance").Select
Worksheets("Balance").Select
not forgetting the parentheses and the double quotes.

Or you can select Sheet1 with:
Sheet1.Select

There are 2 reasons to prefer the third method.

First there is less to key in and secondly if ever you or your user change the caption of the sheet there is no need to review and correct your code accordingly.

To make your code clear rename the sheets in the Visual Basic Editor. Select the sheet in the VBAProject Window and modify the name (the one with parentheses) in the Property Window. In the example below the name of the sheet (as VBA object)  is "shBalance" as for the caption, it can be anything else. Notice the "sh" prefix and the capital B. Again the prefix is important because you can have a variable called Balance (varBalance) a named field called Balance (nfBalance) so the "sh" makes it clear that you are working with a sheet. As for the capital "B" it is a protection against typos. When you write the code you don't capitalise any letter. If it is written right VBA will automatically capitalise the right letter. So if you make a typo there will be no capital letter and you will know that the name is wrong. Then you can write:
shBalance.Select

NOTE: An error message will be generated if while on sheet2 you write:
Sheets("Sheet2").Range("A1").Select
You need to write 2 lines of code one to select the sheet and another one to select the cell:
Sheets("Sheet2").Select
Range("A1").Select

Methods and Properties of the Worksheet

When a sheet is selected in the VBAProject window you can see 11 properties of the worksheet in the Properties window of the VBE, properties for which you can set a default value to begin with and that you can modify through the VBA procedure whenever you whish.

There are 3 properties that you will use frequently: the name (name within parentheses), the name (without parentheses) that is in fact  the caption appearing on the sheet's tab in Excel and the visible property.

As explained above you can change the (Name) if you are developing a workbook for others that might modify it in Excel. You cannot change the (Name) of a sheet programmatically.

To change the caption you can either do it in the property window of the VBE or in Excel by right clicking on the tab then selecting "Rename" . Programmatically you can change the caption of a sheet with the following code:
Sheets("Sheet1").Name= "Balance"

NOTE: The caption of a sheet must not have more than 31 characters and cannot include certain special characters like  ? : \  /  [  ] . If you don't respect these rules your procedure will crash. The following lines of code will generate an error message:
Sheets("Sheet1").Name= "Balance and Introduction to Numbers" because there are more than 31 characters including the spaces
Sheets("Sheet1").Name= "Balance: Introduction" because of the special character :
Sheets("Sheet1").Name= "" because the caption cannot be blank

You can programmatically hide or unhide a sheet with the following code:
Sheets("Sheet1").Visible= True
Sheets("Sheet1").Visible= False

Remember that formulas in cells are calculated even if the sheet is hidden but that before you can do anything programmatically on the sheet  you must unhide it:
Sheets("Sheet1").Visible= True
Sheets("Sheet1").Select
Range("A1").Value=6
Sheets("Sheet1").Visible= False

The third value that the property "Visible" can take is very interesting. A sheet can be very hidden "Sheets("Sheet1").Visible= xlVeryHidden" . In this state not only the sheet is hidden but you can't see its name when in Excel you go to " Format/Sheets/Unhide" . The value xlVeryHidden can only be changed in the Properties Window of the VBE. That means that only users that have access to the VBA ode can unhide this sheet. If your code is protected by a password only users with the password can access the code and modify the " xlVeryHidden" value. You can use this value of the property " Visible" to hide confidential information like salaries and prices or to hide parameters that you don't want modified by the user.

Remember that formulas in cells are calculated even if the sheet is very hidden but that before you can do anything programmatically on the sheet  you must unhide it:
Sheets("Sheet1").Visible= True
Sheets("Sheet1").Select
Range("A1").Value=6
Sheets("Sheet1").Visible= xlVeryHidden

Remember also that formulas on other sheets referring to cells of  a hidden or very hidden sheet work even if the sheet is hidden or very hidden.

If you want to hide many sheets at the same time you will use the following code:
Sheets(Array("Sheet1" , "Sheet2")).Select
ActiveWindow.SelectedSheets.Visible = False
Note that the names used in the array are the captions (the name on the tabs) and not the VBA name.

You might want to delete sheets. Here is the code to do so:
Sheets("Balance").Delete or
shBalance.Delete

You might also want to add one sheet. If you use the following code VBA will add a new sheet before the active worksheet.
Sheets.Add

Inserting one  sheet after the sheet which caption  is "Balance" and which name is shBalance:
 Sheets.Add before:=Sheets("Balance") or
Sheets.Add before:=shBalance

Inserting three sheets after the sheet which caption  is "Balance" :
Sheets.Add after:=Sheets("Balance"), Count:=3

Inserting one  sheet at the beginning of the workbook. Notice the absence of double quotes when using the rank of the sheet:
 Sheets.Add before:=Sheets(1)

Inserting one  sheet at the end of the workbook:
Sheets.Add after:=Sheets(Sheets.Count)

Sometimes you want to send a single worksheet from a workbook to somebody but you don't want all the formulas to follow. Here is the code to copy a sheet out of a workbook into a new workbook, replace the formulas by values and save the new workbook:
Sheets("Sheet3").Select
Sheets("Sheet3").Copy
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWorkbook.SaveAs " newBook.xls"

If you want to do the same thing for many sheets you repeat the procedure or you write:
Sheets(Array("Sheet1" , "Sheet2")).Select
Sheets(Array("Sheet1" , "Sheet2")).Copy
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode=False
Range("A1").Select
Sheets("Sheet2").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode=False
Range("A1").Select
ActiveWorkbook.SaveAs " newBook.xls"
Activeworkbook.Close
Sheets("Sheet1").Select

See the lesson on workbooks to manage the directory (path) when using the " SaveAs" method.

ActiveSheet

The ActiveSheet is the worksheet that has last been selected. So you can write:
ActiveSheet.Visible=True
ActiveSheet.Copy

Remember that when you have copied a cell or a group of cells or any other object from a sheet you ALWAYS paste it to the ActiveSheet:
ActiveSheet.Paste
unless you are doing a PasteSpecial in which case the object is " Selection" :
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

If you want to check it the autofilters are on you need to use the object ActiveSheet also like in the following procedure where I check if the autofilters are on to remove them or exit the procedure:
Range("A2").Select
        If ActiveSheet.AutoFilterMode = True Then
                Selection.AutoFilter
        Else
                Exit Sub
End If

You can also do something on each of the sheets in a workbook with the following code. In this example I set the value of cell A1 to 22 in each worksheet. Notice that I first declare  a variable of the Variant type Dim varSheet As Variant and then the procedure can run.
Sub proTest()
Dim varSheet As Variant
          For Each varSheet In Worksheets
                    Range("A1").Value = 22
        Next
End Sub

You can also use this approach to unhide all the hidden sheets:
Sub proTest()
Dim varSheet As Variant
          For Each varSheet In Worksheets
                    Sheets(varSheet.Name ).Visible = True
        Next
End Sub

This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple
                 
       

 

Developed and Presented by PLI Consultant Inc