Data Types

1. Data Types

2. What is a Data Type? • A data type is a standardized representation for a particular kind of data • We’ll look at the most common ones • For each one we care about • What it’s typically used for in a program • The potential range of values we can store • What kinds of operations we can do on it

3. The Information Content in Bits • One bit can have two values, 0 and 1. You cannot find a way to represent more than two different values with one bit. • Two bits can represent 4 values: 00, 01, 10, 11 • Three bits gives 8 values: 0 followed by each of the 4 values for 2 bits, or 1 followed by each of the four values for 2 bits • In general, adding a bit doubles the number of values. With n bits you can represent 2n values

4. Storing Text • One kind of data we often want to store is text • Text is made up of characters. To let computers of different kinds interact with each other, standard character sets are used • The ASCII character set (the old standard) uses 8 bits per character • With 8 bits we can encode AT MOST 28 = 256 different characters

5. Text (2) • 256 characters is enough for upper and lower case English letters, punctuation, the 10 digits, space and new line, and some odd characters like letters with umlauts • But there are many other alphabets in the world, not to mention Chinese characters! • The new standard, called Unicode, uses 16 bits per character. With 216 possible characters, there are plenty to go around

6. Data type String • VBA uses data type String for strings of text, including single characters. A string can have a huge number of characters up to about 2 billion • Each type, including String, has appropriate operations associated with it

7. Operations on Types (Strings) • With the string type, most operations are done using built-in functions. But there is one operator, &, that concatenates two strings. That means it joins them together into one string. varA = “Hello ” [note the space at the end] varB = “There” varC = varA & varB gives varC the value “Hello There” • We’ll look at the string functions later

8. Numbers • Computer hardware is usually set up to handle two distinct types of numbers, integer and floating point • There is often separate hardware for doing arithmetic on each type, and they are usually used in different ways

9. Integers • Integers are used when we want to count things; they are whole numbers • Excel/VBA has two integer types, called Integer and Long. Integer is 2 bytes and Long is 4 bytes • Integer numbers go from -32768 to 32767. Since there can be more than 32767 rows in a worksheet, it’s best to always use Long unless you know for sure your numbers are smaller

10. Floating Point Numbers • This is the kind of number we use for general computations, which can have a decimal point in them • Internally they are stored in a form that uses a mantissa and an exponent, similar to scientific notation (3.14159 E 23) • Excel/VBA has two floating point types, Single (4 bytes) and Double (8 bytes) • Double has more precision and we will use it in this class

11. Operations on Types (Numbers) • Numbers have the usual arithmetic operations, with the usual precedence. So 2 + 3 * 4 means 2 + (3 * 4), not (2 + 3) * 4 • If you want the last version, use parentheses to change the precedence. You can always use parentheses to be sure the expression means what you want it to • Most arithmetic operations use the same notation when applied to integers or floating point numbers

12. Mixing Integers and Floating Point Numbers • If you write an expression that uses both integers and floating point numbers, the result will typically be a floating point number • Operations like exponentiation or division also yield a floating point result when applied to two integers

13. Logical (Boolean) Values • Another data type is for logical values, called Boolean in honor of logician George Boole • Booleans have one of two values:True or False • Booleans in VBA Excel are two bytes • Why two bytes for information that could be encoded in one bit? Probably for speed since it would take longer for a computer to extract the value of a single bit from a group and the wasted bits are cheap.

14. Operations on Types (Boolean) • Boolean values have several very interesting operations such as AND, OR, and NOT • We’ll look at these closely in our module on conditionals

15. Dates • Dates are their own data type in VBA/Excel • They take up 8 bytes, and can range from January 1, 0100 to December 31, 9999 • The basic display format for dates is MM/DD/YYYY, though you can choose other formats • Internally, starting with Jan. 1, 1900, dates are represented by a number which is 1 on 1/1/1900 and goes up from there.

16. Operations on Types (Dates) • You can also add and subtract numbers from dates, as long as you are using dates after January 1, 1900 . So 2/12/12 + 7 is 2/12/19 • Excel is smart enough to go into the next month or year if need be

17. Other Data Types • VBA has plenty of other data types, but the ones we discussed are the basic ones we’ll use • There are types like Range that refer to a range of cells. These can be very useful in programming; we’ll introduce them as we need them • There is also a Variant data type that is kind of a grab-bag of all types together. It uses a lot of extra space and we will avoid it when we can

18. Declaring Variables • When we start to use variables, we will make it a practice to declare them: state up front what their data type is • The alternative, which might seem attractive, is to let Excel deduce their data type. • The problem with this is that it can make a typo in a variable name into a serious, hard to find error as Excel just thinks it is a new variable • We will use Option Explicit as the first line in every VBA module we write to avoid this problem

19. The Dim Statement • To declare a variable we use a statement such as DimvarAAs Long • Dim is a keyword that tells VBA that we are getting ready to declare a variable • varA is the name of the variable (we made this name up) • You must use the word As, also a keyword, between the variable name and the name of the type • Use Const instead of Dim to declare a constant, as in ConstvarAAs Long = 1 • A constant can’t be changed by the code

20. Names for Variables • We’ll sometimes use fairly generic names (like varA) in our initial examples; when you’re writing a real macro, it’s very important to use meaningful names for your variables • You cannot use a key word as a variable name • The name must start with a letter and is made up of letters, numbers and some punctuation such as underscores (no . though) • It can be up to 255 characters in length

21. Names for Variables (2) • In our class, we will start all variable names with a lower case letter • An example of a long descriptive name would be primaryInterestRate or primary_interest_rate • VBA does not distinguish by case, so varA and vara are the same name to VBA

22. A Final Caution • Things are not always exactly the same in VBA as in Excel. Each has their own built-in functions, precedence rules, etc. Even when things have the same name, they may not work exactly the same way.