Working with Data Types

Problem

You are unfamiliar with VBA data types and their syntax.

Solution

Visual Basic supports the usual data types you'd expect to see in any programming language. Table 2-1 summarizes some of the VBA data types that I use most often for the sorts of calculations covered in this book.

Table 2-1. VBA data types

Data type	Storage	Values
Boolean	2 bytes	TRue or False
Byte	1 byte	0 to 255
Integer	2 bytes	-32, 768 to 32,767
Double	8 bytes	-1.79769313486231 E 308 to +1.79769313486231 E 308
String	Variable	ASCII and special characters

 

Discussion

There are other data types, including user-defined data types, supported by Visual Basic. For the most part, the types shown in Table 2-1 are sufficient for the sorts of programming tasks discussed throughout this book.

I mentioned in Chapter 1 that Excel performs calculations using double precision by default. To ensure double-precision calculations in Visual Basic, be sure to use the Double data type. When writing expressions using literal numeric values, you can use Visual Basic type-declaration characters to let Visual Basic know what data type you want it to consider when evaluating your expression. For example, if you have an expression that includes an operation like 2*3, Visual Basic will interpret these numbers as integers. However, if you write 2#*3#, then Visual Basic will treat each number as a Double. The # character is the type-declaration character for Double data types. In fact, the VBA IDE will help you out here. If you type a number like 3.0, VBA will replace it with 3# automatically. I say this now so you're not surprised when your expressions change before your eyes as you type them.

There are other type-declaration characters recognized by Visual Basic. For example, the % character indicates Integer, and the $ character indicates a string.

Speaking of strings, while this book focuses mainly on scientific and engineering calculations where doubles and integers are more prevalent, strings are sometimes useful for formatting output. Such output may go to spreadsheet cells in the form of text or datafiles written directly to disk.