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.

• Show the Developer Tab on the Ribbon
• Write a VBA macro
• Start macro in vb editor
• Save excel workbook with macro

Writing procedures in VBA

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

• Creating Modules
• Standard Modules versus Class Modules
• Creating and Calling Procedures
• Using Variables and Constants to Store Values
• Writing a Function or Sub Procedure - Introduction
• Visual Basic Naming Rules
• Writing Visual Basic Statements - Three types of statements: Declaration Statements Dim x As Integer, Assignment Statements - x = 4, Executable Statements - MsgBox "Hello World". Continuing a Statement over Multiple Lines, Adding Comments.
• Start your Sub and Function with quality code using the Code VBA Procedure Builder

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.

• Declaring Variables - an introduction
• Variables and their Data Types - Example code for each type
• VBA naming conventions
• Understanding the Lifetime of Variables - Initial values, use of Static
• Understanding Scope and Visibility - Procedure versus Module-level, Private and Public
• VBA Traps: Working with Variables
• Declaring Arrays
• Start your Sub and Function with quality code using the Code VBA Procedure Builder

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:

• For i=1 to 10...Next: Use a counter to run statements a specified number of times.
• For Each...Next: Repeat a group of statements for each object in a collection
• Do...Loop: Looping while or until a condition is True

Conditional processing using If or Select

• Using If...Then...Else Statements
• Using Select Case Statements

Calling procedures

• Calling Sub and Function Procedures
• Using Parentheses in Code
• Understanding Named Arguments and Optional Arguments
• Understanding Parameter Arrays
• Passing Arguments Efficiently - ByRef versus ByVal, advanced

Interacting with the user

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

• Message box

Working with classes and objects

• Understanding Objects, Properties, Methods, and Events
• Executing code when setting properties
• Writing a Property Procedure
• Creating Object Variables
• Calling Property Procedures
• Using With Statements - Executing a series of statements on the same object
• Using Code VBA to create and use classes
• Using Collections in VBA

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.

• Working Across Applications
• Understanding Automation
• Check or Add an Object Library Reference
• Set Reference to a Type Library

Using the VB Editor

• Use the Project Explorer - Code VBA includes a powerful replacement called the Code Explorer which makes it again much easier to switch between procedures.
• Use the Object Browser - allows you to browse through all available objects in your project and see their properties, methods and events. In addition, you can see the procedures and constants that are available from object libraries in your project
• Use the Properties Window - lists the design-timeproperties for selected objects and their current settings.
• Find a Procedure - In the Code window Use the Object Box, else use the Code Explorer
• Find a Variable Definition - Select and SHIFT+F2. Use CTRL+SHIFT+F2 to return to the Last Position.
• Search for Text in Code
• Replace Text in Code
• Set Project Properties
• Split the Code Window

Debugging your code

• Debugging macros - tutorial by WiseOwl
• Ways to Step Through Code in VBA - tutorial by WiseOwl
• Use debug.print to trail what is happening
• Setting Breakpoints and the VBA Stop Statement - tutorial by WiseOwl
• Other Useful Debugging Tools - tutorial by WiseOwl
• Try VBA Code using the immediate window screencast