Start Programming Excel: An Introduction to Macros

A Macro is a computer program that can duplicate actions you take in a spreadsheet. Macros perform key tasks faster. They can have a short-cut key combination to run them easily.

Creating Excel and Word Macros is easier than most other methods of programming. You simply record the actions you took in the program. You can then “play back” the recording without even understanding the code. Or you can take it a step further and change the code. This requires some understanding of the programming language.

Microsoft Office Macros are written in Microsoft Visual Basic and stored in a special type of file. To edit them you open a Visual Basic Editor window from Excel (or Word, Outlook, etc).

Technically speaking, the language is Visual Basic for Applications (VBA). The VBA used in Excel to date is pretty much identical to that in the old stand-alone Visual Basic 6.0.

We generally refer to VB (Visual Basic) except when pointing out differences between VB and VBA.

The stand-alone language, Visual Basic 6.0 (from 1998), was superseded by Visual Basic .NET, a part of Visual Studio.NET. VB.NET is a complete rewrite of the language; many things have changed. We mention them when it is relevant so that you are ready for when (or if, ever!) Microsoft changes Excel Visual Basic to match VB.NET.

Visual Basic code in Excel, Word, etc. is also referred to as Macros – we will use the terms interchangeably.

There are four main areas we will look at in posts on this site:

1. How does VB work as a programming language (language structure and syntax)?
2. How does VB communicate with an Excel spreadsheet (the Excel Object Model)?
3. How does VB communicate with the user (input/output and controls)?
4. Other Topics of interest.

1.     The Sequential Program vs. Event-driven Programming

The traditional “top-down” program proceeds in the following sequence

For those who used this model, there is a major culture shock in store with Windows programming: There is no Main Program!

Windows uses the Event-driven model: 

Each control has its own code and responds to its own events. When each routine finishes, control returns to the interrupt-handler. (That’s the part that waits for the user.)

2.     Macro Procedures: Subprograms and Functions

Two different types of macros occur in Excel:

Every macro is a Subprogram procedure, and should start with Sub name and end with End Sub. 

You can also create Function procedures.  The form of a function is:

Function FnName(Parameters)
FnName = some calculation using Parameters
End Function

In a worksheet cell, you would then enter the function name with parameters (or empty parentheses), e.g. =FnName(B6,12)or =MyFunction()

3.     Program Structure

The general program structure is:

Declarations Section, e.g.
Option Explicit
Option Base 1
Option Compare Text

Sub Sub1
Contents of Sub1
End Sub

Function Fn1
Contents of Fn1
End Function

Other Procedures (Subs and Functions)…

In our next post, we will look at the easiest way to create a macro: Using the Macro Recorder.