Excel 2019 Power Programming with VBA. Michael Alexander

Byte 1 byte 0 to 255. Boolean 2 bytes True or False. Integer 2 bytes –32,768 to 32,767. Long 4 bytes –2,147,483,648 to 2,147,483,647. Single 4 bytes –3.402823E38 to –1.401298E-45 (for negative values); 1.401298E-45 to 3.402823E38 (for positive values). Double 8 bytes –1.79769313486232E308 to –4.94065645841247E-324 (negative values); 4.94065645841247E-324 to 1.79769313486232E308 (for positive values). Currency 8 bytes –922,337,203,685,477.5808 to 922,337,203,685,477.5807. 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.

For worksheet calculation, Excel uses the Double data type, so that's a good choice for processing numbers in VBA when you don't want to lose any precision. For integer calculations, you can use the Integer type (which is limited to values less than or equal to 32,767). Otherwise, use the Long data type. In fact, using the Long data type even for values less than 32,767 is recommended because this data type may be a bit faster than using the Integer type. When dealing with Excel worksheet row numbers, you want to use the Long data type because the number of rows in a worksheet exceeds the maximum value for the Integer data type.

      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.

The following procedure demonstrates how a variable can assume different data types:

      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.

Declaring each variable in a procedure before you use it is an excellent habit. Declaring a variable tells VBA its name and data type. Declaring variables provides two main benefits.

       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