Program by Levels

The 33 lessons and the 25 spreadsheets
are only available from the desktop tutorial

Table of Contents: VBA for Excel

Help and Tips on Excel Macros
Introduction

Introduction to VBA for Excel
Elements of history to tell you where VBA comes from and who should learn VBA..

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.

Help and Tips on Excel Macros
Section 1: Programming in VBA for Excel

Lesson 1-1: 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.

Lesson 1-2: The Project  Window in the Visual Basic Editor of Excel
Where you see all the open workbooks, where you add modules and userforms

Lesson 1-3: 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.

Lesson 1-4: The Code  Window in the Visual Basic Editor of Excel
Where you write and test all your code

Lesson 1-5: 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.

Lesson 1-6: 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.

Lesson 1-7: 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.

Lesson 1-8: 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.

Lesson 1-9: VBA Security and Protection
Protect you code, your sheets and your computer.

Lesson 1-10: 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.

Help and Tips on Excel Macros
Section 2: VBA for Excel Code

Lesson 2-1: Code in VBA for Excel
A few tips to make things easier when you start coding.

Lesson 2-2: 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.

VBA Lesson 2-3: VBA for Excel Code for the Application
(Level: Intermediate and Advanced)
The Application is EXCEL ITSELF. Master 15 new VBA words: Application, Calculation, Cursor, CutCopyMode, Dialogs, DisplayAlerts, EnableEvents, GoTo, Quit, ScreenUpdating, Timer, ChDir, ChDrive

Lesson 2-4: Code for the Workbooks
Working with one workbook, with THISWORKBOOK, with many workbooks and even with all the workbooks in a directory.

Lesson 2-5: Code for 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.

VBA Lesson 2-6: VBA for Excel Code for Range, Cells, Rows and Columns
(Level: Intermediate and Advanced)
Improve your VBA vocabulary with 30 more words  to work witin the worksheet: ActiveCell, ArrayFormula, Borders, Cells, ClearContents, ColorIndex,Column, Columns, Copy, Count, CurrentRegion, Delete, End, EntireRow, EntireColumn, False, Font, Formula, FormulaR1C1, Hidden, Insert, Interior, NumberFormat, Offset, PasteSpecial, Range, Rows, Row, Select, Selection, True, Value

Lesson 2-7: Code for Message and Input Boxes
Interacting with the user with minimal effort.

Lesson 2-8: Code for Excel Database and Filters
Most of your data in the form of a database. Learn how to sort and filter your data.

Lesson 2-9: Code for Statements
They are the KILL, the IF_THEN_ELSE_END, the SELECT-CASE, the DO_LOOP, the FOR_NEXT....

Lesson 2-10: Code for Functions
Creating new Excel functions, using the Excel functions in VBA and a few VBA functions.

Lesson 2-11: Code for 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.

Lesson 2-12: Code for 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.

Lesson 2-13: Code to talk 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

Help and Tips on Excel Macros
Section 3: Userforms and Controls In VBA for Excel

VBA Lesson 3-1: Forms (Userforms) in VBA for Excel
(Level: Intermediate and Advanced)
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.

Lesson 3-2: Userforms Properties and VBA Code
Then you set the properties including the important ones "On Activate" and "On Close"

Lesson 3-3: 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.

Lesson 3-4: Properties and VBA code for Command Buttons
The "CLICK " control where most of the code resides in a userform

Lesson 3-5: Properties and VBA code for Labels
The user just sees it and cannot submit any information with it. YOU are talking to the user

Lesson 3-6: 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.

Lesson 3-7: Properties and VBA code for Combo Boxes
The BIG control. It is a drop-Down list.

Lesson 3-8: Properties and VBA code for List Boxes
When multiple choice is a necessity

Lesson 3-9: Properties and VBA code for Check Boxes, Option Buttons and Frames
The "True/False" controls to be used as a group with a frame

Lesson 3-10: 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.