Coding in VBA for Excel

This section is about coding (writing sentences so that Excel understand). The rules are not as severe as in other programming languages and VBA is very forgiving usually telling you that there is something wrong in your code as soon as you click on enter. In these lessons you will learn a lot of tips to make things easier in "General Coding Tips" and then you will discover the VBA vocabulary of Excel.

An ordinary sentence for Excel is more like "Me.Hungry, Me.Thirsty" than "My stomach is telling me quite clearly that it is time for me to start thinking about eating and drinking".

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.

Code for the Application

Among the objects that you will be working with is Excel itself. It is called the APPLICATION.

Code for the Workbooks

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

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.

Range and Cells

Working with cells (Range)

Offset

Moving around from cell to cell

Rows and Columns

Inserting and deleting

Formulas and Values

Entering values and formulas in cells

Activecell, CurrentRegion and other special addresses

There are important cells and groups of cells

Pasting and handling errors

Paste and the useful PasteSpecial

Using Named Fields, Variables and other Advanced Features

The address can be "A1" but also Sheets("B2").Value or varAddress or nfAddress

Code for Message and Input Boxes

Interacting with the user with minimal effort.

Code for Excel Database and Filters

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

Code for Statements

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

Code for Functions

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

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.

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.

Code for Windows, other Microsoft Programs and APIs

With VBA for Excel you can develop procedures (macros) to work in Excel. Using the API functions you can also program things in WINDOWS from Excel.