Page 334 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

Using Property Statements with
User Defined Types

If you have defined a set of property routines to manipulate a complex structure created
with a Type statement, you might run into problems when you attempt to assign a value
directly to one of the elements in the structure in a single statement. Suppose you have the
following statements in your class:

Public Type MapCoordinateType
Latitude As Single
Longitude As Single
End Type
Chapter 14
Private MyMapCoordinate As MapCoordinateType
Public Property Get MapCoordinate As MapCoordinateType

MapCoordinate = MyMapCoordinate

End Property
Public Property Let MapCoordinate (value as MapCoordinateType)
MapCoordinate = value

End Property

Now, assuming that you instantiated the class as MicrosoftWay, you can reference the
Lattitude value like this:

TempLatitude = MicrosoftWay.MapCoordinate.Latitude
Because this works, you might be tempted to use the following statements:

MicrosoftWay.MapCoordinate.Latitude = 47.63
MicrosoftWay.MapCoordinate.Longitude = 122.13

However, if you use them, you’ll find that the MicrosoftWay.MapCoordinate.Latitude is zero!
Although this seems like a bug in Visual Basic, it really isn’t. Visual Basic is working prop­
erly. When you reference the Latitude element in the first statement, Visual Basic creates
a temporary MapCoordinateType variable and sets the Latitude value to 47.63. Because
the temporary variable is filled with zeros when it’s allocated and a value isn’t explicitly
assigned to Longitude, it contains a value of zero. Thus, when the MapCoordinate Let rou­
tine is called, with the temporary variable that Visual Basic created, Latitude element will
be set to 47.63 and Longitude element will be set to zero.

308
Part 4: Advanced VBA