Tutorial on Excel Macros (VBA)      

Excel VBA Consulting

VBA Excel help

Excel functions and formulas

 

VBA for Excel Tutorial

 

VBA for Excel Sitemap

 

VBA for Excel Solutions

 

 

Introduction to VBA for Excel

 

History of VBA for Excel

 

 

 

 

 

Tutorial on Excel Macros (VBA)


The tutorial includes a set of 33 lessons that you can surf and search easily on your own computer and that you can print.

It also includes 25 Excel spreadsheets with useful examples and templates. You can use the spreadsheets or copy/paste everything from them into your own spreadsheets. Nothing is hidden, nothing is protected. You will find many macros that you need already built for you.

You will learn at your own pace at home, at work or even while travelling. Make copies of the download for all your personal computers.


Table of contents

Introduction to VBA for Excel
Elements of history to tell you where macros comes from and who should learn VBA the language that allows the Macro Recorder and you to develop macros.

Excel Macros Vocabulary
In English you have nouns, verbs, adjectives...in VBA you have objects, properties, methods and events. Learning about these terms helps you understand how VBA works.


Building macros in Excel

Visual Basic Editor (VBE) in Excel

The most user friendly programming environment. You will create your procedures (macros) and userforms in the VBE. You will modify and test these components and even make changes while you are testing your procedures step by step.

The Project  Window in the Visual Basic Editor of Excel

Where you see all the open workbooks, where you add modules and userforms

The Properties  Window in the Visual Basic Editor of Excel

The properties of the workbooks, the names of the modules and the properties of all the components of the userforms.

The Code  Window in the Visual Basic Editor of Excel

Where you write and test all your code

Developing Macros in the VBE

You are becoming so good that you don't need the macro recorder sometimes. Here is how you create a new VBA procedure from scratch.

Testing  Macros in the VBE

Split your screen, use the F8* key and see what you procedure does in Excel step by step. Back up, correct and re-test.

Excel Macro Recorder

You can use the recorder to develop macros rapidly. If you want to do more the Macro Recorder is the best teacher and will be a great assistant even when you become an expert at programming in VBA. It WRITES the code for you. In this chapter you will learn about and try the Macro Recorder. You will run the macro that you have recorded.

Modifying Macros in the VBE

The macro recorder is a great helper and teacher but it sometimes makes it complicated. Here is a recorded macro simplified.

VBA Security and Protection

Protect you code, your sheets and your computer.

VBA Events

For a procedure to start it must be triggered, you click on a button, you close a workbook, the value of a cell changes. Find the right event.


Words and Sentences to Build Macros in Excel

Code in VBA for Excel

A few tips to make things easier when you start coding.

Dealing with Errors

VBA tells you when the code is wrong but what if the logic is wrong or what if the user gives a wrong answer.

Working with the Application

The Application is EXCEL ITSELF. Master 15 new VBA words: Application, Calculation, Cursor, CutCopyMode, Dialogs, DisplayAlerts, EnableEvents, GoTo, Quit, ScreenUpdating, Timer, ChDir, ChDrive

Working with the Workbooks

Working with one workbook, with THISWORKBOOK, with many workbooks and even with all the workbooks in a directory.

Working with the Worksheets

Moving from one to the other, copy/paste from one to the other, adding and deleting worksheets and even doing something on all of them.

Moving aroud the worksheet

Improve your VBA vocabulary with 30 more words to work within the worksheet:

Range or Cells?
The most important method: Offset
VBA code to work with rows and columns in Excel
Entering formulas and values with VBA for Excel
Special Addresses in VBA for Excel
Paste and PAsteSpecial in VBA for Excel
Dynamic addresses in VBA for Excel

Working with Message and Input Boxes

Interacting with the user with minimal effort.

Working with Excel Database and Filters

Most of your data in the form of a database. Learn how to sort and filter your data.

Working with Statements

They are the KILL, the IF_THEN_ELSE_END, the SELECT-CASE, the DO_LOOP, the FOR_NEXT....

Working with Functions

Creating new Excel functions, using the Excel functions in VBA and a few VBA functions.

Working with Variables

When you don't want to hard code things and when the value of something changes put it in a variable. Discover the VARIANT type variable that allows you to execute 1,000,000 calculations in less than 5 seconds.

Working with external data and SQL

When you connect to outside sources of data (large databases, text files, other Excel workbooks,  Access, etc.) the computer  is using SQL (Structured Query Language) a specialized language to work with data. You can use Excel's functionalities to connect and extract data but you can also use directly the SQL language. It is much faster.

Working with Windows and other Microsoft Programs FROM Excel

With VBA for Excel you can develop VBA procedures (macros) to work within in Excel while calling other Microsoft programs like Access, Notepad, Word, Project and even Windows


UserForms and Controls (Command Buttons, Combo Boxes...) in Excel

Forms (Userforms) in VBA for Excel

To require information from the user or tell him something you have used message boxes and input boxes. When these can't do the job anymore you need to develop useforms.

Userforms Properties and VBA Code

Then you set the properties including the important ones "On Activate" and "On Close"

Excel VBA Controls' Properties and VBA Code

They are the command buttons, the list boxes, the combo boxes, the text boxes...and they all have properties. Here they are. What must happen when you click on a command button, when you change the value in a combo box? Here is the code to tell Excel about it.

Properties and VBA code for Command Buttons

The "CLICK " control where most of the code resides in a userform

Properties and VBA code for Labels

The user just sees it and cannot submit any information with it. YOU are talking to the user

Properties and VBA code for Text Boxes

The user is now talking to you. I have rarely seen a userform without text boxes. The format is the challenge.

Properties and VBA code for Combo Boxes

The BIG control. It is a drop-Down list.

Properties and VBA code for List Boxes

When multiple choice is a necessity

Properties and VBA code for Check Boxes, Option Buttons and Frames

The "True/False" controls to be used as a group with a frame

Properties and VBA code for Spin Buttons

They are sexier than text boxes and sometimes make a difference in the clients' opinion of your talent.

 
 

Excel VBA Consulting

Excel macros help

Exxcel functions and formulas