Excel® 2007 Power Programming with VBA

Chapter 8. VBA Programming Fundamentals

In This Chapter

In the preceding chapter, I introduce you to Visual Basic for Applications (VBA); now it’s time to get better acquainted. This chapter discusses some of the key language elements and programming concepts in VBA.

• 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

If you’ve used other programming languages, much of this information may sound familiar. VBA has a few unique wrinkles, however; so even experienced programmers may find some new information.

VBA Language Elements: An Overview

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 Integer, i As Integer
    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: ...