Time and Expense Billing: Part II

The first installment of this two-part series began by explaining how to install and use a Time and Expense Billing application, a program intended for consultants and others who bill for their services. The application uses custom Outlook Journal items to store information about hours worked and expenses incurred for a client, and prints Word invoices containing the slip data.

Notably, the Time and Expense Billing application doesn't use Access. While there's definitely a trade off when it comes to database functionality (see Part I), the upside is that almost all Office users have Word and Outlook, and can therefore benefit from the application without having to add another program to their systems. In this installment, I'll describe the VBScript and Visual Basic for Applications (VBA) code that makes the application come to life. If you aren't interested in coding, Part I is all you need to put the program to use. Of course, you'll need to download it as well (see the end of this article for details).

How the Application Works

The Time and Expense Billing application uses two dialects of Visual Basic: VBScript is used for code behind the Outlook custom slip form, while VBA is used in the Word template's ThisDocument class module, behind the Office UserForm triggered by the template's New event and macros in a standard module in the template project.

There are significant differences between Outlook VBScript and Office VBA that must be taken into account when coding in these dialects, as shown in FIGURE 1.

Office VBA
Outlook VBScript

Runs from application-wide code in class modules, macro procedures in standard modules, and code behind Office UserForms
Runs from Outlook forms only

Has the powerful VBE developer's environment
Has the limited Script Editor development environment

Has a full-featured Object Browser, listing objects belonging to other object models as well as Outlook objects
Has a limited Object Browser, listing only Outlook objects

Supports data typing for variables
Doesn't support data typing; all variables are of the Variant type

Supports named constants for parameter and value settings
Doesn't support most named constants for parameter and value settings; numeric values must be used instead

Works with application-wide events, such as selecting an Outlook folder or creating a new Word document from a template
Works only with Outlook form events, such as changing the value of an item field

FIGURE 1: General differences between VBA and VBScript.

The Outlook Form

The Outlook custom form used for recording Time and Expense data (slip) is based on a Journal form. This is because the standard Journal item on which it's based has some built-in features that lend themselves to working with time data.

FIGURE 2 shows the declarations section of the code behind the forms module, with variables declared without data type, as they must be in VBScript. There are several constant declarations at the end of the variable declarations used to simulate named constants for two folders that are extensively referenced in the code.

SAMPLE ONE: (below)

FIGURE 2: Declarations section of the VBScript form module.

The form's Item_Open event handler (see FIGURE 3) calls functions to fill the lists of several comboboxes on the form with arrays. This is necessary because there's no way to bind a combobox or listbox to data on an Outlook form. It also makes certain controls visible only for billed slips.

SAMPLE TWO:(below)

FIGURE 3: The Outlook form's Item_Open event handler.

Since there are no control events for controls on Outlook forms (except for the Click event, primarily useful for command buttons), a Select Case statement in the CustomPropertyChange form event - see Listing One - is used instead of the Change or AfterUpdate events you might use in VBA code for an Access form or Office UserForm. The strPropName variable represents the name of the custom property that has changed, and each Case represents the actions to perform when that custom property changes. The following list contains comments on various Case clauses in this event handler:

-SlipType: If the slip is changed from Time to Expense (or vice versa), two functions are called to make changes to the slip, primarily making certain controls visible and others invisible.

-Rate, TotalTime: If the slip is a Time slip, and the timer method is not FlatRate, the TotalCost field is calculated using regular rate and cost fields.

-FlatRate, FlatRateQuantity: If the timer method is FlatRate and the FlatRate amount is greater than zero, the TotalCost field is calculated using flat rate fields.

-EndTime: If the timer method is TextBoxes, and the EndTime property is not blank, this lengthy Case clause performs the calculations needed to add time to the slip's TotalTime field from the Start Time and End Time text boxes. Checking for a date being equal to #1/1/4501# is required, because in Outlook, that date is the equivalent of a blank date.

-ChangeDate: The spin button control on the form is bound to this numeric field; a change in its value (up or down) is used to increment or decrement the date in the SlipDate field. This roundabout method is needed since the usual events of this control (SpinUp and SpinDown) don't work on an Outlook form.

-Billed: The value of this custom field is copied to the built-in Mileage field, so that it can be used in a Restrict clause elsewhere in the code. The Mileage and BillingInformation fields are useful for this purpose, since they can be used in both Restrict and Find clauses.

The AddTimeFromTextBox function (see FIGURE 4) illustrates use of the custom Journal item's Start, End, and Duration fields, which are linked so that they perform the necessary calculations automatically. Duration is the difference between Start and End, in minutes. Using these fields eliminates the need to write code to do the calculations. The code also illustrates the rather involved method of referencing controls on an Outlook form; you have to work through the item's Inspector, the ModifiedFormPages collection, and then the specific page's Controls collection. Breaking the reference into its components allows the ctls variable to be used for setting references to several controls on the same form page.

SAMPLE THREE: (below)

TO SEE COMPLETE ARTICLE: http://msdn.microsoft.com/library/default.asp?URL=/library/periodic/period01/ExpenseBillingPartII.htm