Microsoft Office Tutorials and References
In Depth Information
You define custom data types at the top of your module, before any procedures.
After you create a user-defined data type, you use a Dim statement to declare a variable as that
type. Usually, you define an array. For example:
Dim Customers(1 To 100) As CustomerInfo
Each of the 100 elements in this array consists of four components (as specified by the
userdefined data type, CustomerInfo ). You can refer to a particular component of the record as
Customers(1).Company = “Acme Tools”
Customers(1).Contact = “Tim Robertson”
Customers(1).RegionCode = 3
Customers(1).Sales = 150674.98
You can also work with an element in the array as a whole. For example, to copy the information
from Customers(1) to Customers(2) , use this instruction:
Customers(2) = Customers(1)
The preceding example is equivalent to the following instruction block:
Customers(2).Company = Customers(1).Company
Customers(2).Contact = Customers(1).Contact
Customers(2).RegionCode = Customers(1).RegionCode
Customers(2).Sales = Customers(1).Sales
Like most programming languages, VBA has a variety of built-in functions that simplify
calculations and operations. Many VBA functions are similar (or identical) to Excel worksheet functions.
For example, the VBA function UCase , which converts a string argument to uppercase, is
equivalent to the Excel worksheet function UPPER.
Appendix B contains a complete list of VBA functions, with a brief description of each.
All are thoroughly described in the VBA Help system.