Figure 5 1 example message box if the user clicks the

This preview shows page 56 - 59 out of 490 pages.

Figure 5-1. Example message box If the user clicks the OK button, Excel will return the constant value vbOK ; otherwise it will return the value vbCancel . Thus, the If statement in the first line will distinguish between the two responses. (We will discuss the If statement in detail in Chapter 8 . Here, we are interested in the role of symbolic constants.) In case you are not yet convinced of the value of symbolic constants, consider the following enum for color constants: Enum ColorConstants vbBlack = 0 vbBlue = 16711680 vbMagenta = 16711935 vbCyan = 16776960 vbWhite = 16777215 vbRed = 255 vbGreen = 65280 vbYellow = 65535 End Enum Consider which you'd rather type, this: ATextBox.ForeColor = vbBlue or this: ATextBox.ForeColor = 16711680 Need I say more? 5.4 Variables and Data Types A variable can be thought of as a memory location that can hold values of a specific type. The value in a variable may change during the life of the program—hence the name variable. In VBA, each variable has a specific data type , which indicates which type of data it may hold. For instance, a variable that holds text strings has a String data type and is called a string variable . A variable that holds integers (whole numbers) has an Integer data type and is called an integer
43 variable . For reference, Table 5-1 shows the complete set of VBA data types, along with the amount of memory that they consume and their range of values. We will discuss a few of the more commonly used data types in a moment. Table 5-1. VBA Data Types Type Size in Memory Range of Values Byte 1 byte 0 to 255 Boolean 2 bytes True or False Integer 2 bytes -32,768 to 32,767 Long (long integer) 4 bytes -2,147,483,648 to 2,147,483,647 Single(single- precision real) 4 bytes Approximately -3.4E38 to 3.4E38 Double(double- precision real) 8 bytes Approximately -1.8E308 to 4.9E324 Currency(scaled integer) 8 bytes Approximately -922,337,203,685,477.5808 to 922,337,203,685,477.5807 Date 8 bytes 1/1/100 to 12/31/9999 Object 4 bytes Any Object reference. String Variable length:10 bytes + string length;Fixed length: string length Variable length: <= about 2 billion (65,400 for Win 3.1) Fixed length: up to 65,400 Variant 16 bytes for numbers22 bytes + string length Number: same as DoubleString: same as String User-defined Varies 5.4.1 Variable Declaration To declare a variable means to define its data type. Variables are declared with the Dim keyword (or with the keywords Private and Public , which we will discuss later in this chapter). Here are some examples: Dim Name As String Dim Holiday As Date Dim Age As Integer Dim Height As Single Dim Money As Currency Dim wbk As Workbook Dim ch As Chart The general syntax of a variable declaration is: Dim VariableName As DataType If a particular variable is used without first declaring it, or if it is declared without mentioning a data type, as in: Dim Age then VBA will treat the variable as having type Variant. As we can see from Table 5-1 , this is generally a waste of memory, since variants require more memory than most other types of variables.
44 For instance, an integer variable requires 2 bytes, whereas a variant that holds the same integer requires 16 bytes, which is a waste of 14 bytes. It is not uncommon to have hundreds or even

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture