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 Code  Window in the VBE of Excel

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

The Code Window is where 90% of the VBA work is done; writing VBA sentences, testing your VBA procedures (macros) and modifying them when needed.

To illustrate everything that you can do in the Code window we will start by creating a small macro in an empty workbook.

Exercise 6 (Create your first macro and use it)

Step 1: In Excel notice that cells A1, A2 and A3 of "Sheet1" are empty. Go to the Visual Basic Editor.
Step 2: Double click on "Sheet1" in the Project Window. On the right is the Code window of "Sheet1"

For the purpose of this exercise we will develop a small macro within the code window of a sheet. You will later develop the habit of creating modules and organizing your macros within them.

Step 3: Click anywhere in the Code window

Step 4: You can either copy/paste the following macro from your browser to the code window of "Sheet1" or key it in.

If you decide to key it in you will start by entering the first line and then when you press enter the VBE will add the final Line "End Sub". Enter the rest of the code within the two lines. Make sure that everything is there including all the quotation marks, periods, parentheses, equal signs and spaces.

Sub proFirst()
        Range("A1").Value = 34
        Range("A2").Value = 66
        Range("A3").Formula = "=A1+A2"
        Range("A1").Select
End Sub

VBA code example

Step 5: Click on any line of the macro, go to the menu bar at the top of the VBE screen and click "Run" then click "Run Sub/Userform".

Step 6: Go to Excel (ALT/F11) and see what has happened to cells A1, A2 and A3

Congratulations you have run and tested you first macro. Go to Excel and "Sheet1" and see that what the macro was ordering Excel to do has been done. The value of cell "A1" is 34, the value of cell "A2" is 66 and there is a formula in cell A3 that sums cells A1 and A2.

Step 7: Go to Excel and clear the cells A1, A2 and A3 of "Sheet1". On the menu bar go to "Tool" and click on "Macros". In the dialog window select "proFirst" and click on run.

You have run the macro from the menu bar of Excel. In lesson 9 on Events you will discover many other ways to start a macro.

NOTE: You cannot change the font or its color in the code window. You input appears in black, comments appear in green, reserved words in blue and when you make a mistake the font color turns to red.

NOTE: For many users of an earlier versions of Excel the wheel of the mouse wheel does not work in the code window. To enable your mouse, download and install the free fix offered in the downloadable tutorial.

There are plenty of other operations that you can execute in the code window. For example, you can test a macro line by line (step by step), go back a few lines and make corrections, use breakpoints to test only part of a macro.

In section 2 (VBA lessons 11 to 23) you will learn the VBA vocabulary to write macros.


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.