|
[ Pobierz całość w formacie PDF ]
.Table 24-1 lists VBA s assortment of built-in data types. 4800-x Ch24.F 8/27/01 11:59 AM Page 624624 Part VI: Developing Custom Worksheet FunctionsTABLE 24-1 VBA S DATA TYPESData Type Bytes Used Range of ValuesByte 1 byte 0 to 255Boolean 2 bytes TRUE or FALSEInteger 2 bytes 32,768 to 32,767Long 4 bytes 2,147,483,648 to 2,147,483,647Single 4 bytes 3.402823E38 to 1.401298E 45 (fornegative values); 1.401298E 45 to3.402823E38 (for positive values)Double 8 bytes 1.79769313486231E308 to 4.94065645841247E 324 (negativevalues); 4.94065645841247E 324 to1.79769313486232E308 (positive values)Currency 8 bytes 922,337,203,685,477.5808 to922,337,203,685,477.5807Decimal 14 bytes +/ 79,228,162,514,264,337,593,543,950,335 with no decimal point;+/ 7.9228162514264337593543950335with 28 places to the right of the decimalDate 8 bytes January 1, 0100 to December 31, 9999Object 4 bytes Any object referenceString 10 bytes + string length 0 to approximately 2 billion(variable-length)String Length of string 1 to approximately 65,400(fixed-length)Variant 16 bytes Any numeric value up to the range of a(with numbers) double data typeVariant 22 bytes + string length 0 to approximately 2 billion(with characters)Declaring VariablesBefore you use a variable in a procedure, you may want to declare it.Declaring avariable tells VBA its name and data type.Declaring variables provides two mainbenefits: 4800-x Ch24.F 8/27/01 11:59 AM Page 625Chapter 24: VBA Programming Concepts 625Your procedures run faster and use memory more efficiently.The defaultdata type variant causes VBA to repeatedly perform time-consumingchecks and reserve more memory than necessary.If VBA knows the datatype for a variable, it does not have to investigate; it can reserve justenough memory to store the data.If you use an Option Explicit statement, you avoid problems involvingmisspelled variable names.Suppose that you use an undeclared variablenamed CurrentRate.At some point in your procedure, however, you insertthe statement CurentRate =.075.This misspelled variable name, which isvery difficult to spot, will likely cause your function to return an incorrectresult.See the sidebar, Forcing Yourself to Declare all Variables. You declare a variable by using the Dim keyword.For example, the followingstatement declares a variable named Count to be an integer.Dim Count As IntegerYou also can declare several variables with a single Dim statement.For example,Dim x As Integer, y As Integer, z As IntegerDim First As Long, Last As DoubleUnlike some languages, VBA does not permit you to declare a group of vari-ables to be a particular data type by separating the variables with commas.For example, the following statement although valid does not declareall the variables as integers:Dim i, j, k As IntegerIn the preceding statement, only k is declared to be an integer.To declare allvariables as integers, use this statement:Dim i As Integer, j As Integer, k As IntegerIf you don t declare the data type for a variable that you use, VBA uses thedefault data type variant.Data stored as a variant acts like a chameleon: Itchanges type depending on what you do with it.The following procedure demon-strates how a variable can assume different data types.Function VARIANT_DEMO()MyVar = 123 MyVar = MyVar / 2MyVar = Answer: & MyVarVARIANT_DEMO = MyVarEnd Function 4800-x Ch24.F 8/27/01 11:59 AM Page 626626 Part VI: Developing Custom Worksheet FunctionsForcing Yourself to Declare All VariablesTo force yourself to declare all the variables that you use, include the following as thefirst instruction in your VBA module:Option ExplicitThis statement causes your procedure to stop whenever VBA encounters anundeclared variable name.VBA issues an error message (Compile error: Variable notdefined), and you must declare the variable before you can proceed
[ Pobierz całość w formacie PDF ]
zanotowane.pldoc.pisz.plpdf.pisz.plhanula1950.keep.pl
|