Page 99 - Microsoft Office Excel 2003 Programming Inside Out

VBA Programming Starter Kit

coordinates for a point on a map, you could do so and avoid storing the values in separate
variables. Using the Type statement, you can define a new data type to hold both coordinates.

Private Type MapLocation
sglHorizontal as Single
sglVertical as Single
End Type
With the new type defined, you then use it as you would any other variable type, using a
period (.) to reference the subelements of your new type.

Dim myMapLocation as MapLocation
myMapPoint.sglHorizontal = 29.57
myMapPoint.sglVertical = 90
Custom data types have to be defined within the declarations section of a module. They can
be marked as Public or Private.

With...End With Command

One useful shorthand notation you can use to make your code more readable, and shorter, is
the With…End With command. The With…End With command defines an object that the
VBA compiler will assume is being referenced by every property, method, and event called in
the procedure. Once you define the object in the With line of code, you can use a period fol­
lowed by the name of the property that you want to set. The following procedure, for exam­
ple, changes the top and bottom margins of a worksheet to two inches, and changes the
orientation of the worksheet from portrait (with the column headers running parallel to the
short edge of the paper) to landscape (with the column headers running parallel to the long
edge of the paper). Chapter 4

Sub PageSetup()
With ActiveSheet.PageSetup
.TopMargin = Application.InchesToPoints(2)
.BottomMargin = Application.InchesToPoints(2)
.Orientation = xlLandscape
End With
End Sub

Controlling Program Flow
VBA, as a derivative of Visual Basic, is an event-driven language, which means that the code
you write is executed as a response to something that has happened, such as a button being
clicked or a workbook being opened. Program execution normally flows from the first line of
code down to the last line within a procedure, but there are times when this top-down flow of
execution needs to be broken. VBA provides several methods for repeating certain sections of
code, skipping some sections of code, and making decisions about which sections of code to
execute.

73
Part 2: Visual Basic for Applications