VariabLEs Data Types and Constants

Since this book focuses on a spreadsheet application, it's only natural that I introduce variables by asking you to think about the following: what types of values can be entered into a spreadsheet cell and how you might use them? You know that you can enter numbers and text in any spreadsheet cell in Excel. Also, you may or may not know that the format of a spreadsheet cell can be changed to one of several possibilities. For example, a number can be formatted such that the value is displayed with or without digits to the right of the decimal point. Numbers can also be formatted as currency or as a percentage (along with a few other options). Text can be displayed as entered or be automatically converted to a date or time. The content or value of a spreadsheet cell can be changed or deleted at any time.

From this point forward, the contents of a spreadsheet cell (text or numbers) in Excel will be referred to as its value. You have already seen in the Chapter 1 project and will continue to see throughout this book, the use of the Value property to access or change the contents of a spreadsheet cell.

In essence, spreadsheet cells are temporary storage containers for numbers and text that can be displayed and used in a number of different formats. This also describes a variable in any programming language. You can use variables in programs for temporary storage of data. For example, any data input by a user (possibly from a Text Box Control), can be stored in a variable and used later in the program. In the Colorful Stats project from Chapter 1, the following line of code acts a lot like a variable.

.range("C6").Value = "Average:"

Here the text "Average" is copied to spreadsheet cell C6. I could have just as easily copied the text into a program variable first and then copied the contents of the variable to the cell C6. I didn't use an additional program variable because I wanted to save a couple of steps and because, as discussed earlier, spreadsheet cells already act a lot like variables. To accomplish this same task using a program variable, use the following:

Dim myString as String myString = "Average:" .range("C6").Value = myString

The variable myString is first declared (declaration is discussed in the next section) and then assigned the string literal "Average:". The value of spreadsheet cell C6 is then assigned the value stored in the variable myString.

Declaring Variables

To declare a variable is to tell the computer to reserve space in memory for later use. To declare a variable use a Dim (short for Dimension) statement.

Dim myVar As Integer

The name of the variable is myVar. The name must begin with an alphabetic character and cannot exceed 255 characters or contain any spaces. You should avoid the use of punctuation marks or other unusual characters in the variable name, as many of them are not allowed; however, the underscore character is allowed and works well for separating multiple words contained within a single variable name (for example, First_Name). Avoid using reserved VBA keywords and don't repeat variable names within the same scope (discussed later in this chapter). As a convention, the variable name should be descriptive of the value it will hold. For example, if you use a variable to hold someone's first name, then a good name for that variable might be firstName or FirstName. My preference is to begin a variable name with a lowercase letter and then capitalize the first letter of any subsequent words appearing in the name. I try to keep the length to a minimum (fewer than 12 characters)

only because I don't like typing long names. Of course, you can adopt your own conventions as long as they don't contradict rules established by VBA.

Use Option Explicit in the general declarations section of a module window to force explicit variable declarations (see Figures 2.2 and 2.3). Otherwise variables can be dimensioned implicitly (without a Dim statement) as they are required in code. In other words, you can begin using a new variable without ever declaring it with a Dim statement if you don't use the Option Explicit statement. This is not good programming practice as it makes your code harder to interpret, and subsequently more difficult to debug. You can automatically have Option Explicit typed into each module window by checking the Require Variable Declaration option in the Tools/Options menu item of the VBA IDE.

Following the variable name, the data type is specified for the variable. In the example above, the variable is declared as an integer data type. This tells VBA what kind of data can be stored in this variable and how much memory must be reserved for the variable. I will discuss data types in detail later in this chapter.

Object and Standard Modules

Modules refer to a related set of declarations and procedures. Each module will have a separate window in the VBA IDE and, depending on the origination of the module, it will have different behavior with regard to variable declarations. I will refer to the module window shown in Figure 2.2 as an object module because it is associated with an object (the Worksheet object in this example).

The object module for an Excel worksheet.

The object module for an Excel worksheet.

This module will automatically contain all event procedures associated with the worksheet Sheetl, and any ActiveX controls added to this worksheet. Object modules may also contain programmer-defined procedures (I will cover procedures in Chapter 3, "Procedures and Conditions"). Each worksheet will have a separate code window as will the workbook.

A standard module must be added to the project via the Insert menu of the VBA IDE, as shown in Figure 2.3.

The Module menu item

Inserting a standard module.

The Module menu item

Inserting a standard module.

Standar Programming Modules

Standard modules are contained within a separate folder in the Project Explorer and may be renamed in the Properties window (see Figure 2.3). Standard modules contain variable declarations and programmer-defined procedures.

In the Real World

Modularized code aids in the compartmentalization of program code. Compartmentalization is the process of breaking a large programming problem into several smaller problems and then solving each of these smaller problems separately. Compartmentalization is vital in the development of software applications.

Variable Scope

Scope, in the context of variables, refers to the time when a variable is visible or available to the program. When a variable is in its scope, it can be accessed and/or manipulated. When a variable is out of scope, it is unavailable—essentially invisible to the program.

A variable declared within the code block of a procedure (such as the Click() event procedure of the Command Button control), is a procedural level variable. Procedural level variables are only available while program execution occurs within the procedure that the variable was declared. In Figure 2.2, the variable myVar4 is only visible to the program while the code in the Activate() event procedure of the worksheet executes. When program execution is triggered by the Activate() event, the variable myVar4 is dimensioned in memory. Program execution proceeds through the event procedure until reaching the End Sub line of code, after which the variable is released from memory and is no longer available. Each time the procedure executes, the variable is created and destroyed. Thus, myVar4 will not retain its value between calls to the procedure. If necessary, the Static keyword can be used to tell VBA to remember the value of the variable between calls to a procedure. Consider the following example:

Private Sub Worksheet_Activate() Static myVar4 As Integer myVar4 = myVar4 + 1

In this procedure the variable myVar4 will increment its value by one with each call to the procedure. If you replace the Static keyword with Dim, myVar4 will never exceed a value of 1.

Declaring a variable outside of a procedure with a Dim statement makes it a module level variable. The scope of a module level variable depends on the keyword used in the declaration. For example in Figure 2.2 the variables myVar, myVar2, and myVar3 are declared outside all procedures.

End Sub

Integer variables are initialized to a value of 0 at declaration.

The area outside of any defined procedure is known as the general declarations section of a module (object or standard). This area can only be used for declarations.

These three variables are declared with the Dim, Private, and Public keywords. The Private and Public keywords are only allowed for variable declaration in the general declarations section of a module. Each of the three variables, myVar, myVar2, and myVar3 are visible to any procedure within this module. In addition, the variable myVar3 is visible to any procedure in any module of this project. Variables declared in the general declarations section of a module (object or standard) with the Public keyword are commonly referred to as global.

When declaring a variable with the Public keyword in the general declarations section of an object module, it must be referenced in other modules of the project by first identifying the name of the object module. For example, to reference and assign a value to the variable myVar3 in Figure 2.2 in any other module in that project, you must use code similar to the following:

Sheet1.myVar3 = 5

You do not have to reference the name of the module for variables declared with the Public keyword in the general declarations section of a standard module.

To summarize: the keywords Dim and Private have the same function in variable declarations when used in the general declarations section of any module; the Public keyword can be used to declare global variables in a standard or object module.

Data Types

Data types define the kind of value that may be stored within the memory allocated for a variable. As with spreadsheet cells, there are numerous data types; the most common are defined in Table 2.1.

Numerical Data Types

The numerical data types listed in Table 2.1 are integer, long, single, and double. A variable declared as an integer or long data type can hold whole numbers or non-fractional values within the specified ranges. If you need a variable to hold fractional or "floating point" values, then use a single or double data type. Pay attention to the value of the number that might have to be stored within the variable. If the value gets too large for the data type, your program will crash. For example, the following code will generate an overflow error because the value 50000 is outside the allowed range for an integer data type:

Dim myNum As Integer myNum=50000

Table 2.1 Common VBA Data Types r

Data type

Boolean

Integer

Long

Single (floating-point)

Date Object

String (variable-length) String (fixed-length) Variant (with numbers)

Variant (with characters) User-defined (using Type)

Storage size

2 bytes 2 bytes 4 bytes 4 bytes

Double (floating-point) 8 bytes

8 bytes 4 bytes

10 bytes + string length Length of string 16 bytes

Range

True or False

-32,768 to 32,767

-2,147,483,648 to 2,147483,647

-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values

-1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values

January 1, 100 to December 31, 9999

Any Object reference

0 to approximately 2 billion

1 to approximately 65,400

Any numeric value up to the range of a Double

22 bytes + string length Same range as for variable-length String

Number required by elements

The range of each element is the same as the range of its data type.

You must also be careful about mixing numerical data types because you may not get the desired result. The following code will execute without errors, but the variable answer will hold the value 32 after execution of this block, not 31.8 as you might want.

Dim answer As Integer Dim numl As Single Dim num2 As Integer numl = 5.3 num2 = 6

answer = num1 * num2

Changing the variable answer to a single data type will correct the problem. Using the code as shown above is a good way to ensure an integer is stored within a variable that receives its value from a computation involving floating point numbers. Notice that the value stored in answer is rounded to the nearest whole integer.

By using variables with numerical data types, you can carry out mathematical operations as you normally would using just the numbers the variables contained. You can add, subtract, multiply, and divide variables; you can square and cube numerical variables or raise them to any desired power. See Table 2.2 for a list of the operators used for common mathematical operations in VBA.

Table 2.2 Common Mathematical Operators Used in VBA

Operation Operator

Addition +

Subtraction -

Multiplication *

Division /

Exponential A

Basically, any mathematical operation that can be performed on a number can be performed on a numerical variable. The following are a few examples:

Dim numl As Integer Dim num2 As Integer Dim answer As Integer numl = 10 num2 = 5

answer

= numl

+

num2

' answer

Holds

l5

answer

= numl

-

num2

' answer

Holds

5

answer

= numl

*

num2

' answer

Holds

50

answer

= numl

/

num2

' answer

Holds

2

answer

= numl

A

2

' answer

Holds

l00

answer

= 2 A

num2

' answer

Holds

32

After declaring the variables numl, num2, and answer, a few mathematical operations are carried out over several lines of code. The result of each line is given as a comment within the same line of code. In the code above, the equal sign (=) does not designate equality; instead it works as an assignment operator. For example, the variable answer gets the result of adding the two variables numl and num2.

Next, I will look at a fairly simple spreadsheet that uses integer variables and some simple math.

Although it is not required, it is a good idea to place all variable declarations for a procedure at the start of your code. With variable declarations at the beginning of your code, you will be able to find them quickly when you need to debug.

Magic Squares

I believe I was first introduced to magic squares in sixth or seventh grade math. The idea is to fill a square grid with numbers such that the sum of all rows, columns, and diagonals add up to the same value. The number of columns/rows in the grid is an odd number and you can only use each value once. For example, a 3 x 3 grid must be filled with the numbers 1 through 9 so that everything sums up to 15. A 5 x 5 grid uses 1 through 25 and all rows, columns, and diagonals add up to 65. The 3 x 3 is pretty easy even if you don't know or see the pattern.

Figure 2.4 shows the spreadsheet containing the 3 x 3 grid. The Magic Squares spreadsheet is available on the CD-ROM that accompanies this book.

A 3 x 3 Magic Square.

Figure 2.4 shows the spreadsheet containing the 3 x 3 grid. The Magic Squares spreadsheet is available on the CD-ROM that accompanies this book.

A 3 x 3 Magic Square.

The Magic Squares spreadsheet is preformatted for colors, borders, and font size. The program will be contained entirely within the SelectionChange() event procedure of the worksheet.To get to the SelectionChange() event procedure, double click the worksheet name in the VBA

Project Explorer window to open its code module. Select Worksheet from the object's dropdown list, then select SelectionChange from the procedure dropdown list. The program will simply calculate the sum of all rows, columns, and diagonals in the magic square and display the result in adjacent cells. The program code is listed below. The SelectionChange() event procedure triggers every time the user selects a new cell in the worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Dimension variables to hold sums

Dim rowl As Integer

Dim row2 As Integer, row3 As Integer

Dim coll As Integer, col2 As Integer, col3 As Integer

Dim diagonall As Integer, diagonal2 As Integer

'Sum the rows, cols, and diagonals and store result in variables.

rowl = Range("B3").Value + Range("C3").Value + Range("D3").Value row2 = Range("B4").Value + Range("C4").Value + Range("D4").Value row3 = Range("B5").Value + Range("C5").Value + Range("D5").Value coll = Range("B3").Value + Range("B4").Value + Range("B5").Value col2 = Range("C3").Value + Range("C4").Value + Range("C5").Value col3 = Range("D3").Value + Range("D4").Value + Range("D5").Value diagonall = Range("B3").Value + Range("C4").Value + Range("D5").Value diagonal2 = Range("B5").Value + Range("C4").Value + Range("D3").Value

'Copy results to the worksheet.

Range("B6").Value = coll

Range("C6").Value = col2

Range("D6").Value = col3

Range("E3").Value = rowl

Range("E4").Value = row2

Range("E5").Value = row3

Range("E6").Value = diagonall Range("E2").Value = diagonal2 End Sub

First, variables are declared for holding the summations of the rows, columns, and diagonals in the magic square. I am using integer data types because I know that I will not be working with floating point values, and the numbers used will be small.

Next, the values of three cells are added and stored in the previously dimensioned variables. The values of the individual spreadsheet cells are obtained in what should now be a familiar way. Notice that within a row, the row index does not change in the sum of the three values. Similarly, the column index does not change in the sum of the three values within a column. Finally, both row and column indices change in the sum over the diagonals.

Next the contents of these summations are copied to the spreadsheet cells in the corresponding row or column.

As the user enters in the numbers to the cells in the Magic Squares worksheet, the procedure above is triggered and the values of the summations are updated as shown in Figure 2.5.

Magic Squares in action.

Magic Squares in action.

I could have bypassed using variables and simply copied the summation of the three cells directly to the appropriate spreadsheet cell, but using variables with descriptive names makes it a little easier to understand the function of the program.

You have probably recognized that the Magic Squares worksheet isn't anything you couldn't do with formatting and formulas directly in the Excel application; however, with a program, you can show the spreadsheet to a friend or colleague who knows Excel. He or she will wonder how you did it, as there aren't any formulas in the spreadsheet cells that hold the summations of the rows and columns. Your friend might even be impressed. You can also try a 5 x 5, or any size grid as long as the number of rows and columns is odd and equal. The median value of the number set multiplied by the grid dimension will tell you the sum that the values in all rows, columns, and diagonals should equal (for example, a 5 x 5 grid uses the numbers 1 to 25 with a median of 13. So the rows, columns, and diagonals should sum to 5 x 13 = 65).

As you may have realized by now, VBA is not case sensitive; that is, it does not matter if you type your code with upper or lower case letters. However, VBA does preserve capitalization wherever it's used. This is helpful with variable definitions. If you use uppercase letters when declaring a variable, any additional references to that variable within the same scope will automatically follow the same capitalization scheme. So after a variable is defined with a Dim statement, you can type additional references to that variable using all lowercase letters and VBA will automatically convert the capitalization for you. This is a handy feature to ensure you are spelling your variable names correctly as you type them in your code.

String Data Types

Variables with string data types are used to hold characters as text. The characters can be numbers, letters, or special symbols (for example, punctuation marks). Basically, just about anything you can type on your keyboard can be held within a string variable. To declare a variable with the string data type, use the String keyword. To initialize a string variable, place the string value within double quotation marks.

Dim myText As String myText = "VBA is fun"

There are two types of string variables, variable length and fixed length. The example above is that of a variable length string because myText can hold just about any length of text (see Table 2.1). Following is an example of a declaration for a fixed length string:

Dim myString As String * 8 myString = "ABCDEFGHIJKL"

In the example above, the string variable myString can hold a maximum of eight characters. You can try to initialize the variable with more characters (as was done above), but only the first eight characters in this example will be stored in the variable. The value of myString is then "ABCDEFGH". Fixed length strings are more commonly used as a part of a user-defined data type discussed in a later chapter. In most cases, you will not know the length of the string to be stored in a variable so you should use the variable length type.

In the Real World

A lot of what programmers do with strings revolves around extracting desirable information out of them. For example, a search engine on the Internet will look for certain keywords on a Web page and store them in a database. The search engine may load the entire textual content of a Web page into a string variable and then extract various keywords from that variable. Then, when a user searches that database by entering in various keywords, the user's keywords are stored in string variables and compared to database content.

I will discuss string manipulation a little later in this chapter. Next, I will finish my discussion on data types by looking at variants and a few less common data types.

Variant Data Types

Variant data types are analogous to the General category in the number format of a spreadsheet cell in the Excel application. Variables are declared as variants by using the keyword Variant, or by not specifying a data type.

Dim myVar

Dim myVar2 As Variant

Variant type variables can hold any type of data except a fixed length string. Variant data types relax the restrictions on the value a particular variable can hold and thus give the programmer more flexibility; however, variant data types can also be dangerous if overused— they can slow down program execution—and programs with a large number of variant data types can be very difficult to debug. So while I don't recommend using them, I do recognize that many programmers do use variants, and the on-line help is filled with examples using variants, so I will offer a brief example here:

Dim myVar As Integer myVar = 10

myVar = "Testing"

The example above will generate a type mismatch error because an attempt is made to enter the string "Testing" into an integer variable; however, if you change the variable myVar to a variant, the code will execute and myVar will hold the string value "Testing" when all is complete. The following code will run without error.

Dim myVar myVar = 10 myVar = "Testing"

Using variants allows you to use the same variable to hold multiple data types (one at a time). The variable myVar holds the integer value 10 (albeit briefly) before being assigned the string value "Testing".

You are probably starting to see the danger of using variant data types. Imagine a large program with numerous procedures and variables. Within this program are two variables of type variant that initially hold numerical values and will need to be used within the same mathematical operation before the program is finished executing. If one variable is mistakenly reinitialized with a string before the mathematical operation, an error will result and may crash the program (or at least taint the result). Debugging this program may present problems that depend on how hard it is to find the string initialization of the variant variable, and additional problems associated with the string variant. So even though it may be tempting to use variants as a way to prevent errors that crash your program (as in the example above), in actuality the use of variants make your code "loose," and may result in logic errors that are difficult to find.

Logic errors are the result of a mistake in a programming algorithm. They may or may not cause your program to crash, depending on the specific nature of the error. Trying to multiply variables of a string and integer data type would crash program execution, making the error relatively easy to find. Adding when you should have multiplied is a type of logic error that will not crash a program, but will certainly taint the result. Logic errors can be very serious because you may never find them or even know they exist.

Other Data Types

There are just a couple more data types that need to be mentioned. You will see them in action in subsequent chapters.

The Boolean data type holds the value true or false. You can also represent true as a 1 and false as a 0. Boolean variables will be very useful when dealing with programming structures that use conditions, as you will see in the next chapter. Declare and initialize a Boolean variable as follows:

Dim rollDice As Boolean rollDice = False

You can also specify variables of type date. Variables of type date are actually stored as floating point numbers with the integer portion representing a date between 1 January, 100 and 31 December 9999, and the decimal portion representing a time between 0:00:00 to 23:59:59. The date data type is mostly a convenience when you need to work with dates or times.

There are a handful of VBA functions that use variables of type date that add to this convenience. You will see a couple of examples of date functions in the chapter project.

Constants

Constants allow you to assign a meaningful name to a number or string that will make your code easier to read. This is analogous to using named ranges in your spreadsheet formulas. There are numerous mathematical constants for which it makes sense to use constant data types. A constant string might be used when you need frequent use of a particular spreadsheet label. Constants are declared using the Const keyword as shown below.

Const PI = 3.14159 Dim circumference As Single Dim diameter As Single diameter = 10.32 circumference = PI* diameter

The declaration and initialization of a constant occur in the same line of code. The value of a constant can never change, so it is a good idea to use constants when you need the same value throughout the life of your program. Constant names are uppercase as a convention only; it is not required by VBA.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Responses

  • Ted
    How to use constant data type in vba?
    5 days ago

Post a comment