What are VBA’s data types ?

When I talk about data type, I’m referring to the manner in which a program stores data in memory — for example, as integers, real numbers, or strings. 
Although VBA can take care of these details automatically, it does so at a cost. (There’s no free lunch.) Letting VBA handle your data typing results in slower execution and inefficient memory use. 
For small applications, this usually doesn’t present much of a problem. But for large or complex applications, which may be slow or need to conserve every last byte of memory, you need to be on familiar terms with data types. 

VBA automatically handles all the data details, which makes life easier for programmers. Not all programming languages provide this luxury. 
For example, some languages are strictly typed, which means the programmer must explicitly define the data type for every variable used.

VBA does not require that you declare the variables that you use, but it’s definitely a good practice. You’ll see why later in this chapter.

VBA has a variety of built-in data types. Table 7-1 lists the most common types of data that VBA can handle.

Table 7-1 VBA’s Built-In Data Types
Data Type	Bytes Used	Range of Values
Boolean	2	True or False
Integer	2	–32,768 to 32,767
Long	4	–2,147,483,648 to 2,147,483,647
Single	4	–3.402823E38 to 1.401298E45
Double (negative)	8	–1.79769313486232E308 to –4.94065645841247E-324
Double (positive)	8	4.94065645841247E–324 to 1.79769313486232E308
Currency	8	–922,337,203,685,477.5808 to 922,337,203,685,477.5807
Date	8	1/1/100 to 12/31/9999
String	1	per char Varies
Object	4	Any defined object
Variant	Varies	Any data type
User defined	Varies	Varies

In general, choose the data type that uses the smallest number of bytes but can still handle all the data you want to store in the variable.

Loop counters are often declared as integers. If you use the counter in order to loop through rows in the worksheet, your program might just error out! Why? Integers cannot be larger than 32,767. 
Beginning with Excel 2007, worksheets have many more rows (1,048,576 to be exact). Instead, declare such loop counters as Long.

Visual Basic for Applications Excel Terminology

Explanation Have you ever wanted to customize Excel? For example, you might want to display a dialog box asking a user to enter specific data for a worksheet. 
Maybe you want to perform a decision-based task, such as displaying a message when a user activates a worksheet. You can do these things, and more, by programming in Visual Basic for Applications (VBA).

VBA is a programming language that’s part of the Microsoft Office suite. You can use VBA to create programs that work within Microsoft Office applications, such as Microsoft Excel and Microsoft Word. 
VBA is based on the concept of Object-Oriented Programming (OOP). OOP is a programming concept in which all the elements of a program are considered as objects.

VBA makes decision-based tasks easier, because its code can execute automatically in response to an action. For example, you can write VBA code to accept values and prompt the user to enter values if mandatory fields are skipped, ensuring the integrity of your data. 

VBA terminology

Using VBA, you can directly access objects to control and manipulate the behavior of the application. However, before you start coding in VBA, you need to be familiar with some key terms associated with it.

The following table describes some of these terms:

Term and Description

Object 

Any element of an application with specific characteristics and behavior. It’s a component that combines code and data. For example, workbooks, worksheets, ranges, and charts are all objects.

Property 

A characteristic or named attribute of an object. For example, Name and StandardWidth are the properties of the Worksheet object.

Method 

A behavior or action that’s performed by an object. For example, Calculate is a method that updates the values in a Worksheet object containing formulas.

Procedure 

A named sequence of instructions that performs a specific task. For example, you can create a procedure to save the changes made before closing a workbook.

Comment 

A line of text within a procedure, which you use to describe each line of code or the entire procedure. Comments always start with an apostrophe.

Module 

A file in which you can write and edit procedures and other VBA codes.

Collection and container objects

Objects can be either collection objects or container objects. 

A collection object 

is a set of related objects having the same properties. For example, the Worksheets collection object represents all the worksheets in a workbook.

A container object 

contains one or more objects, which may or may not be related. For example, Workbook is a container object that contains the Worksheet objects. The outermost container object is the Application object that contains all other Excel objects, such as Worksheets and Workbooks.

VBA and macros

Macros that you create and record using the Excel interface are written by Excel using VBA code. But there’s a difference between macros and VBA procedures that you write from scratch. 

A macro performs a set of instructions, then stops. For this reason, macros are very useful for automating repetitive tasks.

But macros follow a single execution path. A procedure written in VBA code can evaluate conditions, make decisions based on those conditions, and then alter the flow of execution.

Do it! 

A-1: Discussing VBAQuestions and answers

1 What’s the main advantage of VBA compared to macros?

VBA code can evaluate conditions, make decisions, and then execute automatically in response to those decisions. Recorded macros can only follow a single execution path.

2 Lets say you want to purchase a car. You can select a yellow, red, or black one. Identify the object and its property in this case.

•The car is the object 

•The color is the property

3 What’s a procedure?

A procedure is a named set of instructions that performs specified actions. For example, you can write a procedure that saves changes made before closing an Excel workbook.

4 You want a worksheet name to change automatically when text is entered in a specific cell. Would you record a macro or use VBA? Why?

You’d use VBA, because the code used to change the name would run automatically when the user enters text in a cell.