Understanding Parts and Functions of VBA Code

To work with VB, you need to know more about the syntax of the language—the rules that govern how the language works. Just as most human languages have widely recognized parts (such as nouns, verbs, adjectives, and so on), programming languages break down into a number of parts that you put together in statements: arrangements defined by the syntax of the language.

You put together statements by using combinations of keywords, objects, properties, methods, constants, and variables, with required arguments and optional arguments specifying information. In the next sections, we’ll look at what each of these terms mean and how you work with them.

Keywords

A keyword is a word defined as having a meaning in Visual Basic. Keywords include object names, properties, methods, and argument names. It’s possible to create variables, constants, and procedures that have the same names as Visual Basic keywords. This is called shadowing a keyword, and isn’t usually a good idea because it becomes easy to get confused. For example, there’s an object called BaselineCost, so it’s a bad idea to create a variable, constant, or procedure named BaselineCost.

As we’ll see later in this chapter, you can name your variables, constants, and procedures pretty much anything that strikes your fancy, so there’s no real reason to shadow a keyword—but there are so many keywords in Project that it’s surprisingly easy to shadow a keyword accidentally.

Collections

Groups of objects are organized into collections that provide an easy way to access the objects. For example, the ResourceGroups collection contains a collection of Group objects, each of which represents a "group" of resources. The collection TaskGroups also contains a collection of Group objects, each of which represents a "group" of tasks. Usually, the name of a collection is the plural of the object, Calendars is the collection of Calendar. The Group collection objects example is an exception, however, where the same object can be collected into two different collections (confused yet?). Collections themselves are objects, too. To reduce confusion, from now on, we’ll refer to any collection as a collection rather than a collection object.

Methods

A method is a built-in action that you can perform on an object; more technically, a method is a procedure (a set of instructions) for an object. For example, the Group object that we met a moment ago has a Delete method that deletes the specified group and a Copy method that makes a copy of the specified group. The Calendar object has methods including Period for representing a period of time, and Reset for resetting base calendar properties to their default values.

Like a property, a method appears after the name of the item it refers to, separated by a period. For example, the Delete method looks like this:

 Item.Delete

Most methods take one or more arguments: parameters that supply pieces of information necessary to the method. Some arguments are required, while others are optional.

Other methods take no arguments. For example, the Delete method takes no arguments because none is needed—Project is deleting the specified item, and that’s all the information it needs to do so.

Constants

A constant is an item in memory that keeps an unchanging piece of information while a program is executing. You can specify the appropriate constant in your code instead of the corresponding value because it provides an easy way of handling complex information or information that may change from computer to computer, such as the location of a particular Project file.

Project uses constants to signify frequently used information, such as the month of the year (for example, the constant pjApril in the pjMonth group of constants represents April) and the options available while performing actions (for example, pjDateFormat contains more ways to format the date on Project views and reports than you could possibly use). Each constant has a numeric value associated with it; for example, the constant pjDate_mm_dd_yyyy in the pjDateFormat group has the numeric value 20 associated with it. When using VBA to automate Project procedures, you can use either the descriptive constants or the numeric values to specify the constant you want to assign.

Variables

A variable is a location in memory that you set aside for storing a piece of information while a procedure is running. You can create as many variables as you need, and you can give them any name that meets Visual Basic’s naming rules. As we mentioned before, it’s a bad idea to shadow a keyword by giving a variable the same name.

The rules for creating names for variables in Visual Basic and VBA are simple:

• A name can be up to 40 characters long.

• A name must begin with a letter.

• A name can include letters, numbers, and underscores.

• A name cannot include spaces or symbols other than the underscore.

• A name must be unique within its scope. (Scope is discussed in “Creating Variables” later in this chapter.)

Mastering the Opportunities: Naming Variables

When you name a variable, you can include a prefix so that the variable name clearly indicates the type of data the variable is designed to hold. Following this convention from the beginning really pays off when you need to change or troubleshoot your code. Here are the prefixes for the commonly used data types with examples of each prefix used in a variable name:

Boolean: bln (blnOverEstimate)

Currency: cur (curVendorEstimate)

Date: dte (dteEstimatedComplDate)

Integer: int (intDaysPastEst)

Object: obj (objExcelChart)

Single: sng (sngProjInterestRate)

String: str (strLocation)

Try to make your variables names descriptive, but short enough to type accurately.

Data Types

Visual Basic and VBA include a range of variable types, called data types, designed for particular kinds of data. For example, you would store a string of text such as a word or phrase in a string variable, whereas you would store an integer (whole number) value in an integer variable. Table 26.1 lists the data types available in VBA, and the range of values available for each type. You can also create user-defined types by using the Type statement in VBA. See the Visual Basic help files for more information on creating user-defined data types.

Table 26.1: VBA Data Types

Data type	Storage size	Range
Byte	1 byte	0 to 255.
Boolean	2 bytes	True or False.
Integer	2 bytes	32,768 to 32,767.
Long (long integer)	4 bytes	2,147,483,648 to 2,147,483,647.
Single (single-precision floating-point)	4 bytes	3.402823E38 to 1.401298E45 for negative values; 1.401298E45 to 3.402823E38 for positive values.
Double (double-precision floating-point)	8 bytes	1.79769313486231E308 to 4.94065645841247E 324 for negative values; 4.94065645841247E324 to 1.79769313486232E308 for positive values.
Currency (scaled integer)	8 bytes	922,337,203,685,477.5808 to 922,337,203,685,477.5807.
Decimal	14 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; smallest non-zero number is +/0.0000000000000000000000000001.
Date	8 bytes	January 1, 100 to December 31, 9999.
Object	4 bytes	Any Object reference.
String (variable-length)	10 bytes + string length	0 to approximately 2 billion.
	Length of string	1 to approximately 65,400.
Variant (with numbers)	16 bytes	Any numeric value up to the range of a Double.
Variant (with characters)	22 bytes + string length	Same range as for variable-length String.
User-defined (using Type)	Number required by elements	The range of each element is the same as the range of its data type.

Ironically, the default data type is Variant, which is automatically used if you don’t specify a data type when you declare a constant, variable, or argument. Variables declared as the Variant data type can contain string, date, time, Boolean, or numeric values. Variant data types take up more storage space than other types, and should be avoided unless the data type is unknown.

Mastering the Opportunities: Converting a Variable to a Different Type

In a procedure, you might need to convert data to a different type in order to process it or modify it. The following table lists the functions used to convert data in VBA. (You can use all the conversion functions in calculated custom fields, too.)

Function	Returns
Abs	Absolute value of the number or expression
Asc	ASCII value of the first character in the specified string
Chr	Character representing the ASCII value entered
Cbool	True if the expression is nonzero, otherwise False
Cdate	Date representation of the expression or number
DateSerial	Date variable of the date specified by year, month, and day
DateValue	Date variable from a text expression or a string
TimeSerial	Date variable of the time specified in hours, minutes, and seconds
TimeValue	Date variable from a text expression or a string
CByte	Byte representation of the expression or number
CInt	Integer representation of the expression or number
CLng	Long representation of the expression or number
CSng	Single-precision representation of the expression or number
CDbl	Double-precision representation of the expression or number
CStr	String representation of the number; also returns a string from a Boolean, Date, or error value
Fix	For positive values: next lower whole number; for negative values: next lower whole negative number
Int	For positive values: next lower whole number; for negative values: next higher whole negative number
Sgn	1 for positive value, ñ1 for negative value (the sign of the expression or number)
Hex	String containing hexadecimal representation of the expression or number
Oct	String containing octal representation of the expression or number

Here are several examples using these conversion functions:

• Asc(strCity) returns the ASCII value of the first character in the string strCity.

• CBool(1000-900) returns True.

• DateSerial(1998, 10, 31) - DateSerial(1998, 9, 19) returns 42, the number of days between the two dates.

• Hex(16) returns 10, the hexadecimal representation of the decimal number 16.

Creating a Variable

You can create a variable in two ways: by declaring it explicitly (listing it at the beginning of a procedure), or by creating it implicitly (by simply using it in your code).

Declaring a Variable Explicitly

When you declare a variable explicitly, you can also set the variable’s scope, which determines where the variable will be available. There are three types of scope:

• Procedure level: The variable is only used in the procedure.

• Private level: The variable can be used by any procedure in the same module.

• Public level: The procedure can be used by any project open at the same time as the module that contains the procedure.

To declare a variable explicitly, use a Dim statement, a Private statement, or a Public statement. Variable declarations are usually listed at the top of a subroutine, immediately following the opening Sub line and any user documentation, so they’re available when needed in the procedure.

To declare a variable with procedure-level scope, use a Dim statement within the procedure. The following procedure asks the user for an estimate date. The second line of code declares a procedure-level variable called dteEstDate (a date variable). The third statement displays an input box and assigns its result to the dteEstDate variable.

Sub InputEstDate()   Dim dteEstDate as Date   dteEstDate = InputBox("Enter the date for this estimate.") End Sub

Private and Public scope variables are declared at the module level: code at the beginning of the module prior to any procedures. All private and public variables must be declared explicitly
with module level code.

To declare a variable with Private scope, use a Private statement prior to the procedure at the beginning of the module. The following statement creates an integer variable named intDaysPastEst with Private scope:

Private intDaysPastEst As Integer

To declare a variable with Public scope, use a Public statement prior to the procedure at the beginning of the module:

Public objExcelChart As Object

Creating a Variable Implicitly

Instead of declaring a variable explicitly, you can create it implicitly by typing the variable’s name in code. The following statement implicitly creates the variable intMyValue by assigning the value 123 to it:

intMyValue = 123

When you create a variable implicitly, it has procedure-level scope only and is assigned the Variant data type.

Mastering the Opportunities: Declaring Variables

If you’re creating variables with procedural scope, it’s still a good idea to declare them explicitly. When you declare variables implicitly, every typo becomes a new variable. It’s easy, for example, to mistype intExprDate as intExpDate. With implicit variable creation, you now have two variables. You can spend a long time trying to determine why your code isn’t working as designed.

There’s another reason to use explicit declaration: Your code is easier to change or troubleshoot when you can easily see the variables used in a procedure at the top of the procedure’s code.

To make sure that every variable used in a procedure is declared, set the Require Variable Declaration option on the Editor page of the VB Editor’s Options dialog box (Tools Ø Options):

You can also require explicit declaration on a procedure-by-procedure basis by entering Option Explicit as a module-level command prior to the first procedure at the beginning of the module.

Assigning a Value to a Variable

To assign a value to a variable, use an equal sign after the variable name, followed by the value. The following statement assigns the value 55 to the variable intDaysExpired:

intDaysExpired = 55

The following statement assigns the value “Ferndale" to the variable strLocation:

strLocation = "Ferndale"

Tip 

To assign an object to a variable, you need to use a Visual Basic Set statement rather than the equal sign.