Chapter 8: VBA Programming Fundamentals


Understanding VBA language elements, including variables, data types, constants, and arrays

Using VBA built-in functions

Manipulating objects and collections

Controlling the execution of your procedures

VBA Language Elements: An Overview

If you've used other programming languages, much of the information in this chapter may sound familiar. However, VBA has a few unique wrinkles, so even experienced programmers may find some new information.

In Chapter 7, I present an overview of objects, properties, and methods, but I don't tell you much about how to manipulate objects so that they do meaningful things. This chapter gently nudges you in that direction by exploring the VBA language elements, which are the keywords and control structures that you use to write VBA routines.

To get the ball rolling, I start by presenting a simple VBA Sub procedure. The following code, which is stored in a VBA module, calculates the sum of the first 100 positive integers. When the code finishes executing, the procedure displays a message with the result.

Sub VBA_Demo()

‘ This is a simple VBA Example

Dim Total As Long, i As Long

Total = 0

For i = 1 To 100

Total = Total + i

Next i

MsgBox Total

End Sub

This procedure uses some common VBA language elements, including:

A comment (the line that begins with an apostrophe)

A variable declaration statement (the line that begins with Dim)

Two variables (Total and i)

Two assignment ...

Get Excel® 2010 Power Programming with VBA now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.