Excel VBATutorial           


Excel VBA Macros

Email to excel-vba.com

Excel Tutorial on Macros

 Excel Consulting

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

The Properties Window in the VBE of Excel

Note: Print this page, open Excel and a open a new workbook. Use ALT/F11 to open the Visual Basic Editor as you learned in lesson 1.

The Properties window shows you the properties of the component that is selected in the Project Window (single click).  For example in the new workbook if you single click on "Sheet1" in the Project Window you see the properties of sheet1 in the Properties Window like in the image below.

Sheets Properties

As you can see, a worksheet has 12 properties that you can change in this Properties window. Notice that there are 2 "Name" properties. On the first line there is the programmatical name of the sheet (Sheet1). You will discover later the advantages and disadvantages of changing this property. The second "Name" property (9th line) is the name (or caption) that appears on the tab of the sheet in Excel.

Changing the "Name" Property

Exercise 3 (Create your first macro and use it)

Step 1: Go to Excel (ALT/F11) and notice the names on the three tabs of "Sheet1" as in the image below.

Sheets Tabs

Step 2:  We will change the name (Caption) on the tab of "sheet1" to "Introduction". To do so right-click on the tab of the sheet and the following dialog window appears:

Rename sheets

Step 3: Select "Rename". The menu disappears and the name of Sheet1 is highlighted. Enter "Introduction" and this new name will replace "Sheet1" when you click "Enter". The end result is illustrated in the image below.

Renaming Sheets

Step 4: Come back to the Visual Basic Editor (ALT/F11) and notice in the Properties window that the property "Name" (the ninth property, the one without the parentheses) has changed to "Introduction

Visual Basic Editor Properties window

As you have now learned the name of the sheet can be changed from Excel. We will now complete another smal exercise to change the name from the VBE Properties window.

Exercise 4 (Create your first macro and use it)

Step 1: In the VBE select "Sheet2" in the Project window. On line 9 of the Properties window double-cllck on "Sheet2" and enter the name Spreadsheet. Click "Enter"

Step 2: Go to Excel and notice that you now have a sheet named "Spreadsheet" .

Properties Window Workbook

Setting and modifying properties of objects in the Properties Windows is something that you will have to do a lot when you start developing userforms (see lessons 24 to 33).

Until then you will change a small number of properties including the very important "Visible" property of the sheets to one of its three values. To see the equivalent of the image below, select Sheet2(Spreadsheet) in the Project window. Click on the word "Visible" on the 12th line of the Properties window. A dropdown arrow appears in the cell to the right. Click on the arrow and you can select one of the three properties.

Sheets Property visible

In lesson 3 of the downloadable the tutorial on VBA for Excel you will discover how useful the "xlSheetVeryHidden" property can be. This property of a sheet can be used -- for example, to hide salaries in a budgeting application or prices in an estimation application --  making sensitive data inaccessible to the unauthorized users of your workbooks.

You will also learn how to name your modules and work with a few other properties of the objects appearing in the Project Window.

Close the VBE and close Excel without saving anything.

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.