Creating Custom Data Types

---

VBA provides all of the simple data types that you will ever need when writing macros for Excel. In some instances, you might need a data type that is more complex. For example, if you wanted to create a custom data type that would hold both the horizontal and vertical 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 followed by the name of the property that you want to set. The following procedure, for example, 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).

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

---