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

Developing  Macros  in 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.

Most macros are developed in the code window of modules. For the purpose of this exercise double click on "Sheet1" in the project window

Enter sub proTest() without using  a capital "S" as the beginning of "sub". After entering the closing parenthesis click on "Enter". You get these two lines of code:

Sub proTest()

End Sub

VBE adds the line "End Sub" and capitalizes the "S" of "Sub" . The VBE capitalizes letters appropriately when the word is spelled correctly. This is one interesting feature that you should always use when writing macros. Make it tour habit never to use capital letters when writing code. In this way, whenever VBE unexpected fails to capitalize a letter, you will know that something is wrong.

Two exceptions to your otherwise consistent use of lower-case are: (1), when you declare variables (lesson 19); and (2), when you name macros (as you did above). You will see why in later lessons.

You may now write a procedure within the two lines of code above. For example your VBA procedure could look like this. You can copy/paste the macro below from your browser to the VBE Code window, or key it in. Make sure that everything is there including all the quotation marks and periods, parentheses, equal signs, and spaces.

Note: Make sure that you copy/paste this code in a NEW workbook not one created in a previous exercise.

Sub proTest()

        Sheets("Sheet1").Select
        Range("C1").Select

        Do Until Selection.Offset(0, -2).Value = ""
                Selection.Value = Selection.Offset(0, -2).Value & " " & Selection.Offset(0, -1)
                Selection.Offset(1, 0).Select
        Loop

        Range("A1").Select

End Sub

The procedure above will go down column "C" and assemble the first names of column "A" and the last names of column "B" with a space in between. It will perform this task all the way down until there are no more first names in column "A" . It will then place the cursor in cell "A1".

To test this macro (VBA procedure) follow the steps below:

Step 1: Go to Excel (ALT/F11) and enter first names in cell A1 to A5.

Step 2: Enter surnames in cells B1 to B5.

New Excel macro

Step 3: Come back to the VBE (ALT/F11) and click within the macro in the code window.

Step 4: From the menu bar select "Run/Run Sub/Userform".

Step 5: Go back to Excel and see the result.

New vba macro

You can erase everything in column C Excel and retry with more names and surnames.

Try it again removing the first name in cell A3. Notice that the macro stops on line 2.


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.