Here is a sample of what you will find
in the downloadable Tutorial on Excel macros
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.
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:
and then if you want to hide the sheet again:
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 :
).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:
We hope you have enjoyed this tip
For more on this topic and a complete course on Excel macros download the
Tutorial on Excel Macros
To organize your discovery of Excel macros, the downloadable Tutorial on Excel Macros is divided in three sections (all 3 sections part of the single download):
Section 1: Excel Macros Programming (Chapters 1 to 10)
This section is about recording, writing, modifying and testing macros in the Visual Basic Editor. You will also learn about security and discover "events" (an event is what starts the macro).
Section 2: Excel VBA Vocabulary (Chapters 11 to 23)
Developing a macro is communicating with Excel and to do so you need to use a language called Visual Basic for Applications (VBA). In section 2 you will learn all the VBA vocabulary that is essential to work with business data (accounting, sales, production and others).
Section 3: Forms and Controls in VBA for Exce (Chapters 24 to 33)
The userform is a small or large dialog window that you create and allows the user to submit values that will be used by your macros. To these userforms you will add controls (command buttons, text boxes, list boxes and others) and program them.