Writing macros using VBA, an introduction

On this page you will find information to get you started with VBA (Visual Basic for Applications).

A few screencasts to get you started

The screencasts were done in Excel, but are applicable to other Office applications also.

Writing procedures in VBA

A procedure is a named series of program statements that can be started independently.

Declaring variables and constants

Variables are used to manage the data in computer memory. Most variables you declare represent the data you are processing, the programs input or output. In addition you will use internediary variables to control the current state of the process for eample is as part of the program you asked if he wants to save data, the reply will be stored in a variable which will then determine what will happen next, depending.

It is good to declare and use constants

  • Declaring Constants - it is good practice to give fixed values you need a name and use that instead of the value. It is better to use constant cstPi instead of 3.14 and use cstProgramName instead of 'my beatiful program'. By using constants you are able to make changes (e.g. to the ProgramName) in one location.
  • Using Constants - In addition to the constants you declared yourself you will use system-defined constants which are part of VBA (e.g. vbYesNo as result of the MsgBox procedure) or defined in the application you are working with (e.g. xlFormulas as possible argument for the SpecialCells method).

Controlling Program Flow

Read for an introduction on controlling program flow

Loop to repeat actions

Looping allows you to run a group of statements repeatedly. In VBA there are three types loops:

Conditional processing using If or Select

Calling procedures

Interacting with the user

The easiest way to communicate with a user is using built-in dialogs:

Working with classes and objects

Automating one application from another

A common requirement is to create documents (Word, PDF, ...) and email using data stored in MS Access or Excel. Such a working together of several application is called Office automation.

Using the VB Editor

Debugging your code