Sunday, 17 June 2012

Lesson 15: VBA for Excel 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 (Lesson 9).

You access a worksheet named " Balance" with:
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
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:

You must take two steps:

No comments:

Post a Comment