Excel VBATutorial                      Tutorial on Excel macros

 

Excel VBA Macros

Excel Macros Table of Contents

Excel Visual Basic Editor

Excel Macros Vocabulary

Excel Userforms

Excel Consulting

Excel Tutorial on Macros

Email to excel-vba.com

Here is a sample of what you will find in lesson 15 of the downloadable Tutorial on Excel macros

Lesson 15 on Excel Macros (VBA):

VBA Code for Worksheets

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 VBA lesson  on events.

Sheets

You access a worksheet named " Balance" with:
Sheets("Balance").Select
Note that the word "Sheets" is plural and always use the quotes within the parenthesis

You cannot select a sheet that is hidden so you will need to write:
Sheets("Balance").Visible= True
Sheets("Balance").Select
and then if you want to hide the sheet again:
Sheets("Balance").Visible= False

The name  of a sheet must not have more than 31 characters and should not include certain special characters like  " ? : \   /   [  ]" . If you do not 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 name  cannot be blank

You can not go directly from a sheet to a cell on another sheet. For example if the active sheet is "Balance" and you want tot go to cell A1 of a sheet named " Results" you cannot write:
Sheets("Results").Range("A1").Select

You must take two steps:
Sheets("Results").Select
Range("A1").Select


We hope you have enjoyed this introduction to lesson 15
for more on this topic and a complete course on Excel macros download the
Tutorial on Excel Macros


Next Lesson: VBA Code for Cells and Ranges

 

Excel Tutorial on Macros

Excel VBA Macros

Excel Macros Table of Contents

Excel Visual Basic Editor

Excel Macros Vocabulary

Excel Userforms

Excel Consulting