Excel 2019 Power Programming with VBA. Michael Alexander

Чтение книги онлайн.

Читать онлайн книгу Excel 2019 Power Programming with VBA - Michael Alexander страница 38

Excel 2019 Power Programming with VBA - Michael Alexander

Скачать книгу

Decimal 12 bytes +/–79,228,162,514,264,337,593,543, 950,335 with no decimal point; +/–7.9228162514264337593543950335 with 28 places to the right of the decimal. Date 8 bytes January 1, 0100 to December 31, 9999. Object 4 bytes Any object reference. String (variable length) 10 bytes + string length 0 to approximately 2 billion characters. String (fixed length) Length of string 1 to approximately 65,400 characters. Variant (with numbers) 16 bytes Any numeric value up to the range of a double data type. It can also hold special values, such as Empty, Error, Nothing, and Null. Variant (with characters) 22 bytes + string length 0 to approximately 2 billion. User-defined Varies Varies by element.

      NOTE

      The Decimal data type is unusual because you can't declare it. In fact, it is a subtype of a variant. You need to use the VBA CDec function to convert a variant to the Decimal data type.

      Generally, it's best to use the data type that uses the smallest number of bytes yet still can handle all the data that will be assigned to it. When VBA works with data, execution speed is partially a function of the number of bytes that VBA has at its disposal. In other words, the fewer the bytes used by the data, the faster that VBA can access and manipulate the data.

      Declaring variables

      If you don't declare the data type for a variable that you use in a VBA routine, VBA uses the default data type, Variant. Data stored as a Variant acts like a chameleon: it changes type, depending on what you do with it.

      Sub VariantDemo() MyVar = True MyVar = MyVar * 100 MyVar = MyVar / 4 MyVar = "Answer: " & MyVar MsgBox MyVar End Sub

      In the VariantDemo procedure, MyVar starts as a Boolean. The multiplication operation converts it to an Integer. The division operation converts it to a Double. Finally, it's concatenated with text to make it a String. The MsgBox statement displays the final string: Answer: -25.

      To demonstrate further the potential problems in dealing with Variant data types, try executing this procedure:

      Sub VariantDemo2() MyVar = "123" MyVar = MyVar + MyVar MyVar = "Answer: " & MyVar MsgBox MyVar End Sub

      The message box displays Answer: 123123. This is probably not what you wanted. When dealing with variants that contain text strings, the + operator will join (concatenate) the strings together rather than perform addition.

      Determining a data type

      You can use the VBA TypeName function to determine the data type of a variable. Here's a modified version of the VariantDemo procedure. This version displays the data type of MyVar at each step.

      Sub VariantDemo3() MyVar = True MsgBox TypeName(MyVar) MyVar = MyVar * 100 MsgBox TypeName(MyVar) MyVar = MyVar / 4 MsgBox TypeName(MyVar) MyVar = "Answer: " & MyVar MsgBox TypeName(MyVar) MsgBox MyVar End Sub

      Thanks to VBA, the data type conversion of undeclared variables is automatic. This process may seem like an easy way out, but remember that you sacrifice speed and memory—and you run the risk of errors that you may not even know about.

       Your programs run faster and use memory more efficiently. The default data type, Variant, causes VBA to perform time-consuming checks repeatedly and reserve more memory than necessary. If VBA knows the data type, it doesn't have to investigate, and it can reserve just enough memory to store the data.

       You avoid problems involving misspelled variable names. This benefit assumes that you use Option Explicit to force yourself to declare all variables (see the next section). Say that you use an undeclared variable named CurrentRate. At some point in your routine, however, you insert the statement CurentRate = .075. This misspelled variable name, which is difficult to spot, will likely cause your routine to give incorrect results.

      Forcing yourself to declare all variables

      To force yourself to declare all the variables that you use, include the following as the first instruction in your VBA module:

      Option Explicit

      TIP

      To ensure that the Option Explicit statement is inserted automatically whenever you insert a new VBA module, enable the Require Variable Declaration option on the Editor tab of the VBE Options dialog box (choose Tools ➪ Options). It is generally considered a best

Скачать книгу