11.3 Data Processing in VBA Code

Once you have understood how Excel's database function can be used interactively, programming will present few additional problems. It is necessary only that you know what you are doing in working with cells and cell ranges. This topic was discussed quite thoroughly, without reference to database applications, in the first section of Chapter 5.

Programming Techniques

Elementary Database Management in Excel

Most database commands assume that the affected range of cells has been selected or that the cell pointer is located inside the database. Setting out from this cell, the associated table or list can be determined with CurrentRegion . (The requirement is that the table have no empty rows or columns , since such would be interpreted as the end of the database.)

With the property ListHeaderRows it can be determined how many header rows a table contains. The property attempts to determine whether and how many header lines there are that differ from the remaining structure of the table. (The on-line help, however, contains no information on how headers are recognized. It is thus not certain whether this property can be relied on to function correctly.)

Data Search

With the method Find you can search for any text in a range of cells. This method returns a Range object with the first cell that contains the search text. If Find finds nothing, then the method returns Nothing as its result. Here is the syntax of Find :

 rng.Find what, after, lookIn, lookAt, searchOrder, searchDirection, _          matchCase, matchByte

Find is thus applied to a range of cells. The first parameter contains the search text. All the other parameters are optional. The parameter after specifies the cell after which the search is to begin. If after is not specified, then the search begins in the first cell of the range. The parameter lookIn specifies where the search is to take place: in the cell content ( xlValues ), in a formula ( xlFormulas ), or in cell comments ( xlComments ). The parameter lookAt determines whether the entire cell content should agree with the search text or whether it suffices if the search text is only part of the character string. The parameter searchOrder determines whether the range is searched row by row or column by column, while searchDirection specifies whether the search runs forwards or backwards . The parameter matchcase determines whether the search is case sensitive.

Tip	Please observe that you must not rely on a default setting for any of the optional parameters. The settings that were last used are the ones that will be used again until they are changed (regardless of whether the search was via VBA code or EditFind.

If you wish to repeat the search to find the next matching cell, you can call Find once again and specify the last result cell in the parameter after . More convenient , however, are the methods FindNext and FindPrevious , where you need to supply only the one parameter after .

Caution

As long as there is one cell in the search range that satisfied the search criterion, this will be found, even if this cell is above the parameter after. For this reason the following lines lead to an infinite loop if the search range contains a single cell that contains the character string "xyz".

 Dim obj As Object     Set obj = [a1].CurrentRegion.Find("xyz")      Do Until obj Is Nothing        obj.Interior.Color = RGB(196, 196, 196) 'gray background        Set obj = [a1].CurrentRegion.FindNext(obj) ' search next cell      Loop

If you wish to search all of the cells once, you need to keep track of the addresses of the already encountered cells. The following lines give an example of this.

 Dim obj As Object, cellsDone$ Set obj = [a1].CurrentRegion.Find("xyz") Do Until obj Is Nothing   If InStr(cellsDone, "[" + obj.Address + "]") Then Exit Do   obj.Interior.Color = RGB(196, 196, 196)   cellsDone = cellsDone + " [" + obj.Address + "]"   Set obj = [a1].CurrentRegion.FindNext(obj) Loop

Sorting Data

When using Sort you must note that this method normally includes in its sort the first row of the given range. Since this is often a header row, and therefore intended to remain fixed in place, in most cases the optional parameter Header:=xlNo must be specified.

Filtering Data

The filter functions are governed by AutoFilter (to activate an autofilter) and AdvancedFilter (to activate an advanced filter). In the case of an autofilter (there can only be one autofilter active at any given moment) the property Filters results in several Filter objects that describe the filter criteria for each column of the database. The application of the Filter methods causes few problems, and the code can usually be created with the macro recorder.

Displaying the Database Form

ShowDataForm , for calling the predefined database form in Excel, does not, alas, function optimally. The command assumes that the database begins with cell A1, regardless of where the cell pointer is located. You can get around this if you give the range in which the database is located the name "database" and then execute ShowDataForm . The example below assumes that A5 is a cell of the database.

 ActiveSheet.Range("A5").CurrentRegion.Name = "database" ActiveSheet.ShowDataForm

Inserting, Editing, and Deleting Data

Excel provides no command to insert records into an Excel table, or to edit or delete data records. The database form can be called only for the user of the program to take over these tasks . The actions that can be carried out in the database form cannot be executed in a macro program. Changes in the content of the database must therefore be carried out in the traditional way (see the first section of Chapter 5): Select cells with the Range and Cells methods, change the content of the cells with the Value or Formula property, and so on.

Tip	If you have decided to manage the data not within Excel but in an external database, then you have significantly better programming options in the form of the ADO library. More on that in the next chapter.

Example: A Form Letter in Microsoft Word

Fundamentals

To convert a Word document into a form letter, execute in Word the command ToolsMail Merge and in step one use CreateForm Letters to transform your document into a so-called main document. Then select in step 2 as data source an Excel file (Get DataOpen Data Source). You must pay attention to the following rules:

In the Excel file the addresses should be indicated by a named range.
If you don't use named ranges, make sure that the sheet with the addresses is the active sheet of the workbook at the time you save the workbook. Furthermore, the address sheet should contain one header row and the data, and nothing else. Data further in the table can annoy Word. (In version 2002, Word somehow fails to offer the possibility of choosing one from among several worksheets, as if multiple worksheets was something entirely new in Excel!)
The entries in the header row are considered by Word to be "mail merge fields" set in the text as placeholders for the data.
Any active autofilter criteria for the database in Excel are not considered by Word. Word always reads all of the data that are in the active worksheet. However, you can define similar criteria as in Excel with the Word mail merge manager with the button Query Options. However, the dialog for setting filter criteria is not so convenient or powerful as that in Excel.

Preparing an Excel Table for a Word Form Letter

At the beginning of this chapter the file Staff.xls was presented as an example of a small database. To make it possible to send a form letter only to members of a particular group , we can select certain criteria in the example file. By clicking on the button Prepare Microsoft Word mail merge the selected addresses will be copied to a separate worksheet. The range will be named with WinWordAddresses .

The code required to make this happen is quite short and is based on elementary Excel methods. The only special feature is the method SpecialCells(xlVisible) , with which it is achieved that only the visible data records are copied. (If filters are used in the employee database, these filters should also be used for the Word address list.)

 ' Staff.xls, Module "Sheet2" Private Sub  btnWinWord_Click  ()   Application.ScreenUpdating = False   ' clear all cells in sheet "DataForWinword"   Sheets("DataForWinword").Cells.ClearContents   ' copy visible data   Sheets("database").[a2].CurrentRegion.SpecialCells(xlVisible) _     .Copy Sheets("DataForWinword").[a1]   ThisWorkbook.Names.Add "WinwordAddresses", "=" & _     Sheets("DataForWinword").[a1].CurrentRegion. _     Address(ReferenceStyle:=xlA1, external:=True)   Application.ScreenUpdating = True End Sub

With Names.Add the name "WinWordAddresses" is defined. The cell range of the address list is determined with CurrentRegion and is transformed with Address into a character string of the form "='DataForWinword'!$A$1:$Q$5" . ("DataForWinword" is the name of the worksheet in which the addresses are stored.)

Creating the Word Form Letter

The Word file Staff.doc contains a simple scheme for a form letter (see Figure 11-9) that is constructed using the data in Staff.xls . (The Excel file must be located in the same folder; otherwise , the data source must be specified in Word anew. To control the mail merge functions, you must first make the relevant toolbar visible with ViewToolbarsMail Merge.)

Figure 11-9: A form letter in Word

The only special feature in Staff.doc is the use of conditional text. Conditional text is inserted via InsertField, category Mail Merge, field name IF. In the dialog that then appears you can input one condition and two texts. The condition must affect the content of a data field. Of the two texts , one is displayed or printed when the condition is met, the other if the condition is not met. In the example below a conditional field for the salutation (Mr. or Ms.) is used based on the content of the database field m/f , which for some reason is addressed in the Word formula as f :

 {IF f = "m" "Mr" "Mrs" }

Tip

Excel cannot respond to requests for data from Word as long as you are editing a cell. If during editing you change from Excel to Word and there you wish to select another data record (by clicking on an arrow in the mail merge toolbar), then Word is blocked, since it is waiting for Excel. The impression that Word has crashed is fortunatetly a false impression . After about half a minute Word replies with an error message. If you do not wish to wait so long, return to Excel and finish your cell input.

Pointer

Further code examples of data management from within Excel can be found in Chapter 1 (introductory database example) as well as further along in this chapter. The methods for using tables and ranges, which are an elementary requirement for writing your own database programs, are considered in the first two sections of Chapter 5.

Syntax Summary

In the following tables wsh stands for the worksheet containing the database ( WorkSheet object), rng for the range of cells of the database ( Range object), and " " for the input of various parameters that are not described here (see the online help).

DATABASE MANAGEMENT IN EXCEL (SORTING, GROUPING, ETC.)
rng.Name = "database"	names range for ShowDataForm
wsh.ShowDataForm	displays the database form
rng.Sort	sorts the database
rng.Find	searches for data
rng.FindNext	search again
rng.FindPrevious	search backwards
rng.Replace	search and replace
rng.Consolidate	consolidate several tables

FILTER
rng.AutoFilter	activate autofilter
rng.AdvancedFilter	activate advanced filter
wsh.FilterMode	tells whether the table contains filtered data
wsh.AutoFilter	refers to the AutoFilter object
wsh.AutoFilter.Filters( )	refers to the Filter objects (with filter criteria)
wsh.AutoFilterMode	tells whether autofilter is active
wsh.AutoFilterMode = False	deactivates autofilter
wsh.ShowAllData	deletes filter criteria