Chapter Excel Applications

Simply put, we can define an Office application to be an Office document (for instance, an Access database, Excel workbook, Word document, Word template, or PowerPoint presentation) that contains some special customization. This customization usually takes the form of a combination of VBA procedures and menu and or toolbar customizations and is generally designed to simplify or automate certain tasks. It may provide utilities, which are programs for performing a specific task, such as printing...

Organization of This Book

Writing Excel Macros consists of 21 chapters that can informally be divided into four parts (excluding the introductory chapter). In addition, there are five appendixes. Chapter 1 examines why you might want to learn programming and provides a few examples of the kinds of problems that can best be solved through programming. Chapter 2 introduces programming and the Visual Basic for Applications language. Chapter 2 through Chapter 4 form the first part of the book. Chapter 3 and Chapter 4...

The generic As Object declaration

It is also possible to declare any Excel object using the generic object data type Object, as in the following example While you may see this declaration from time to time, it is much less efficient than a specific object declaration, such as This is because Excel cannot tell what type of object the variable chrt refers to until the program is running, so it must use some execution time to make this determination. This is referred to as late binding and can make programs run significantly more...

Providing Access to an Applications Features

I recently created an Excel application for a well-known fast food company. The company wanted to send out data on sales and other things to its field offices, in the form of a rather complicated Excel pivot table. They wanted the field personnel to be able to filter the pivot table by various means (thus creating smaller pivot tables) as well as generate a variety of charts showing different views of the data. (The complete application involved other features, but this will illustrate the...

Addins and COM Addins

Excel 2000 supports the same add-in model that is supported by Excel 97. This is the add-in model that we use to create the SRXUtils add-in. In addition, the Office 2000 suite supports a new add-in model called the COM add-in model. A COM add-in is an ActiveX DLL or executable file that can be connected to multiple Office 2000 applications. Since this type of add-in is an ActiveX DLL or executable, it must be created using a programming environment, such as Visual Basic or Visual C++, that is...

Example A Draw Sine to Generate a Dampened Sine Curve of Small Stars

' Dampened sine wave of small stars Const pi 3.1416 Dim rng As Range ' For starting point Dim n As Single ' Cycle length in inches Dim ScaleY As Single ' Vertical scaling Dim sDamp1 As Single ' Dampening factor Dim sDamp2 As Single ' Dampening factor Dim cCycles As Integer ' Number of cycles ' Starting position StartLeft ActiveCell.Left cCycles 3 sDampl 1 sDamp2 0.2 n 2 k 20 sSize Application.InchesToPoints(O.l) ' Loop for first curve with phase shift For i 1 To cCycles * k x n * i k y ScaleY *...

The Axis Object

Table 21-2 shows the properties and methods of the Axis object. As you can see, Axis objects are fairly involved in their own right. Fortunately, most of the members in Table 21-2 are self-explanatory, so we consider them only briefly. Note that most of these members correspond to the myriad check boxes and edit boxes in the five tabs of the Excel Format Axis dialog box. Table 21-2 shows the properties and methods of the Axis object. As you can see, Axis objects are fairly involved in their own...

Replace Method

This method finds and replaces specified data in all cells in a range. It has no effect on the selection or the active cell. The syntax is RangeObject.Replace(What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte) The What parameter is the data to search for, and the Replacement parameter is the replacement data. These data can be strings or any other valid Excel data types (numbers, dates, etc.). The LookAt parameter is one of the following constants that determines whether the What...

What Is the Shape Object

Each Excel sheet (chartsheet or worksheet) and each Excel chart has a drawing layer upon which we can place drawing objects. A drawing object is represented by a Shape object. As usual, the Shape objects for a sheet are stored in a Shapes collection. The Chart object and the Worksheet object both have a Shapes property that returns the collection of all Shape objects drawn on the chart or worksheet. There is also a ShapeRange object that holds a collection of selected Shape objects, much as a...

Logical Errors

There is one more type of error that we should discuss, since it is the most insidious type of all. A logical error can be defined as the production of an unexpected and incorrect result. As far as Excel is concerned, there is no error, because Excel has no way of knowing what we intend. (Thus, a logical error is not a run-time error, in the traditional sense, even though it does occur at run time.) To illustrate, the following code purports to compute the average of some numbers Ave (x(0) +...

Adding a Menu Item

It is worth mentioning now that there are a few counterintuitive wrinkles in the process of menu creation. In particular, we might think at first that adding a new menu should be done using the Add method of the CommandBars collection, specifying the name of the parent menu and the location of the new menu on the parent. After all, a menu is a CommandBar object, and this procedure would be consistent with other cases of adding objects to a collection. However, this is not how it is done....

Chapter Excel Events

During the course of using Excel, certain events happen. For instance, when a worksheet is created, that is an event. When a chart is resized, that is an event. Microsoft Excel defines a total of 63 different events. When an event occurs, programmers like to say that the event fires. The purpose of an event is simply to allow the VBA programmer to write code that will execute whenever an event fires. As we will see, this code is placed in an event procedure. The code itself is referred to as...

Example Code for the Printing Procedures

MsgBox This is the print charts utility End Sub MsgBox This is the print pivot tables utility End Sub MsgBox This is the print sheets utility End Sub Now, the Print.xls workbook is an ordinary Excel workbook, so if our add-in opens this workbook in order to call one of its procedures, the workbook will be visible to the user. This is not good. Hence, we need to create an add-in from this worksheet as well. Let us call it Print.utl. (You can save the worksheet under this name by placing the name...

Final Steps

Finally, you should follow these steps 1. Compile the project. Use the Debug menu to compile the SRXUtils.xls project. 2. Set the properties. Set the workbook and project properties as shown in Figure 10-6 and Figure 10-8, making any necessary changes based on the name you have chosen for your add-in. 3. Protect the add-in. Under the Protection tab of the project's Properties dialog, check the Lock project for viewing checkbox and enter a password. 4. Save the add-in. Save the project as an...

Example Adding a Menu Item to an Existing Menu

Private Sub Workbook Open() Dim lngPos As Long Dim objHelpMenu As CommandBar Dim objHelpMenuItem As CommandBarControl Dim objExcelAbout As CommandBarControl Set objHelpMenu ' Determine position of About Microsoft Excel Set objExcelAbout objHelpMenu.Controls(About Microsoft Excel) If Not objExcelAbout Is Nothing Then lngPos objHelpMenu.Controls.Count End If ' Add About SRXUtils menu item Set objHelpMenuItem objHelpMenuItem.Caption About & SRXUtils objHelpMenuItem.BeginGroup True...

Example Adding an Item to an Existing Menu

Of course, rather than creating a custom toolbar or adding a custom menu to Excel's menu system, you may prefer to add a button to an existing toolbar or a menu item to an existing menu. In that case, you simply need to retrieve a reference to the CommandBar object to which you wish to add the item and call the Controls collection's Add method to add an item to it. In addition, you can retrieve the Index property of the item before which you'd like to position your new menu item or toolbar...

Members that Enable Excel Features

Several Application members enable or disable certain Excel features AutoFormatAsYouTypeReplaceHyperlinks property (R W Boolean) Set to True to have Excel automatically format hyperlink text as a hyperlink. Set to False to turn off this often-annoying feature of Excel. EnableAnimations property (R W Boolean) This property determines whether animated insertion and deletion is enabled. When animation is enabled, inserted worksheet rows and columns appear slowly and deleted worksheet rows and...

Project Contents

At the level immediately below the top (project) level, as Figure 3-2 shows, there are nodes named Under the Microsoft Excel Objects node, there is a node for each worksheet and chartsheet in the workbook, as well as a special node called ThisWorkbook, which represents the workbook itself. These nodes provide access to the code windows for each of these objects, where we can write our code. Under the Forms node, there is a node for each form in the project. Forms are also called UserForms or...

The Workbook Object

A Workbook object represents an open Excel workbook. As we have discussed, Workbook objects are stored in a Workbooks collection. The Workbook object has a total of 103 properties and methods, as shown in Table 17-1. Table 17-1. Members of the Workbook object Table 17-1. Members of the Workbook object RemovePersonalInformation< v 10> 10> PasswordEncryptionKeyLength< v 10> Several of the members listed in Table 17-1 exist solely to return the children of the Workbook object. The...

Excel Object Data Types

Excel VBA has a large number of additional data types that fall under the general category of Object data type. We will see a complete list in the chapter on the Excel object model. To get the feel for the types of objects in the Excel object model, here is a partial list of the more prominent objects Thus, we can declare variables such as Dim wb As Workbook Dim wks As Worksheet Dim chrt As Chart Dim ax As axis Dim pf As PivotField We will devote much of this book to studying the objects in the...

Fill Methods

The Excel object model has four Fill methods FillDown, FillUp, FillLeft, and FillRight. As expected, these methods work similarly, so we will describe only FillDown. The FillDown method fills down from the top cell or cells in the specified range to the bottom of the range. The contents, formulas, and formatting of the cell or cells in the top row of a range are copied into the rest of the rows in the range. The syntax is RangeObject.FillDown For instance, the code will duplicate the values of...

Chapter Builtin Functions and Statements

VBA has a large number of built-in functions and statements. For possible reference, Table 7-1 shows the VBA functions, and Table 7-2 shows the statements. We will take a look at a few of the more commonly used functions and statements for programming Excel VBA in this chapter and Chapter 8. To help simplify the exposition, we will follow Microsoft's lead and use square brackets to indicate optional parameters. Thus, for instance, the second parameter in the following procedure is optional Sub...

Grouping

Excel also lets us group (and ungroup) the data for a selection of pivot items into a single new pivot item. This is done using the Group and Ungroup methods of the Range object. Note that these methods apply to the Range object, not the PivotField or PivotItem objects. The Group method has two syntaxes, but we will look at only the more flexible of the two. (For all of the details on the Group method, we refer the interested reader to the Excel help documentation.) Let us look at an example....

Excel Add Ins

An Excel add-in is a special type of workbook that is usually saved with an .xla file extension. (We will discuss how to create add-ins later in this section.) An add-in can be connected to Excel by checking its check box in the Add-Ins dialog (see Figure 10-5), which is displayed by selecting Add-Ins from the Tools menu. Once an add-in is connected, it remains so (even if Excel is closed and reopened) until the check box in the Add-Ins dialog is unchecked. When connected, an add-in's...

Additional Members of the Pivot Table Object

Let us take a look at some additional members of the PivotTable object. We have seen this method in action earlier in this chapter. 20.5.5.2 CalculatedFields method It is possible to add calculated fields to a pivot table. These are fields that are not part of the original source data, but instead are calculated from source fields using a formula. The CalculatedFields method returns the CalculatedFields collection of all calculated fields. To add a new calculated field, we use the Add method of...

The Pivot Table Wizard

Excel Vba Form Weights And Measures

Let us first walk through the PivotTable wizard to create our PivotTable. Then we will create the same PivotTable using code. The first step is to select the source data and start the wizard by selecting PivotTable Report under the Data menu. This will produce the first wizard dialog, as shown in Figure 20-1. These figures are for Excel 97 and 2000. The Excel XP wizard has a somewhat different appearance. Figure 20-1. Step 1 in the PivotTable wizard Note that this dialog allows us to select the...

Worksheet Events

The worksheet-related events are shown in Table 11-1. These events are also referred to as document events. Table 11-1. Worksheet Events DocEvents Table 11-1. Worksheet Events DocEvents Occurs when a worksheet is activated. Occurs when a worksheet is double-clicked, before the default double-click action. Occurs when a worksheet is right-clicked, before the default right-click action. Occurs after the worksheet is recalculated. Occurs when cells on the worksheet are changed by the user or by an...

The Complete Code

For reference, let us put together the code required to create the pivot table in Figure 20-6 it is shown in Example 20-3. Example 20-3. The CreatePivot Procedure ' Assumes that the source table is in sheet called Source ActiveSheet.PivotTableWizard SourceType xlDatabase, SourceData 'Company Both' R1C1 R145C7, ' Assign field orientations and data fields With xlPageField .PivotFields Year .Position 1 .PivotFields Store City .Orientation xlRowField .PivotFields Store City .Position 1 .PivotFields...

Defining a Range Object

As witness to the importance of the Range object, there are a total of 113 members properties and methods throughout the Excel object model that return a Range object. This number drops to 51 if we count only distinct member names, as shown in Table 19-2. For instance, BottomRightCell is a property of 21 different objects, as is TopLeftCell. Table 19-2. Excel Members That Return a Range Object Table 19-2. Excel Members That Return a Range Object Let us take a look at some of the more prominent...

Create the Code Behind the User Form

Now it is time to create the code behind these controls. 18.4.2.1 The Declarations section The Declarations section of the dlgPrintSheets UserForm should contain declarations of the module-level variables, as shown in Example 18-1. Example 18-1. Module-Level Variable Declarations The Cancel button code is shown in Example 18-2. Example 18-2. The cmdCancel_Click Event Handler The Print button calls the main print procedure and then unloads the form its source code is shown in Example 18-3....

Topics in Learning Excel Programming

In general, the education of an Excel programmer breaks down into a few main categories, as follows. First, you need to learn a bit about the environment in which Excel programming is done. This is the so-called Visual Basic Editor or Excel VBA Integrated Development Environment IDE for short . We take care of this in Chapter 3 and Chapter 4. Next, you need to learn a bit about the basics of the programming language that Excel uses. This language is called Visual Basic for Applications VBA ....

Create the User Form

Create the dialog shown in Figure 18-7 in the Print.xls workbook. Name the dialog dlgPrintSheets and set its Caption property to Print Sheets. Then change the PrintSheets procedure to The dlgPrintSheets dialog has two command buttons and one list box Place a list box on the form as in Figure 18-7. Using the Properties window, set the properties shown in Table 18-4. Table 18-4. Nondefault Properties of the List Box Table 18-4. Nondefault Properties of the List Box When the Cancel property of the...

Copy and Cut Methods

As applied to the Range object, the Copy method has the syntax where Destination is a Range object that specifies the new range to which the specified range will be copied. If this argument is omitted, Excel will copy the range to the Clipboard. For instance, the code Range A1 C3 .Copy Range D5 copies the range A1 C3 to a range of like size whose upper-left corner is cell D5. Note that the same rules apply here as when copying using Excel's user interface. In particular, if the destination is...

Creating a Procedure

There are two ways to create a new procedure that is, a subroutine or a function within a code module. First, after selecting the correct project in the Project Explorer, we can select the Procedure option from the Insert menu. This will produce the dialog box shown in Figure 4-1. Just type in the name of the procedure and select Sub or Function the Property choice is used with custom objects in a class module . We will discuss the issue of public versus private procedures and static variables...

VBA String Functions

Here are a handful of useful functions that apply to strings both constants and variables The Len function returns the length of a string that is, the number of characters in the string. Thus, the code These functions return an all uppercase or all lowercase version of the string argument. The syntax is MsgBox UCase Donna will display the string DONNA. The Left, Right, and Mid functions These functions return a portion of a string. In particular returns the leftmost number characters in string,...

.printout Method Preview

The PrintOut method prints the chart. This method applies to a variety of other objects, such as Charts, Worksheet s , Workbook s , and the Range object. The syntax is ChartObject .PrintOut from, To, Copies, Preview, ActivePrinter, PrintToFile, Collate Note that all of the parameters to this method are optional. The From parameter specifies the page number of the first page to print, and the To parameter specifies the last page to print. If omitted, the entire object range, worksheet, etc. is...

SaveAs Method

This method saves changes to a workbook in the specified file. The syntax is expression.SaveAs Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodePage, TextVisualLayout The Filename parameter specifies the filename to use for the newly saved disk file. If a path is not included, Excel will use the current folder. The FileFormat parameter specifies the file format to use when saving the file. Its value is one of...

Figure The Quick Watch window

1 en lp lattlJnQ BCt amp IbMOC 1 jferhYOt ih irpA Another way to quickly get values for expressions or variables is to enable Auto Data Tips on the Editor tab of Excel VBA's Options dialog box. With this feature enabled, when we place the mouse pointer over a variable or select an expression and place the mouse pointer over it, after a slight delay, a small yellow window will appear containing the value of the variable or expression. This is very useful

Naming Data Fields

We should make a few remarks about naming data fields. It is important to note that if the name of a data field has not been changed but we make a change to the aggregate function, say from Sum to Average, then Excel will automatically rename the data field, in this case from Sum of Sales to Average of Sales. However, once we set a new name for the data field, Excel will not rename it when we change the aggregate function. We can rename a data field simply by setting its Name property. However,...

Creating Embedded Charts

The Worksheet object also has a ChartObjects property that returns a ChartObjects collection, which is the collection of all ChartObjects in the worksheet. As we have mentioned, a ChartObject object is a container for a Chart object that is, an embedded chart. The ChartObjects collection has an Add method that is used to create a new embedded chart. The syntax is ChartsObjectObject.Add Left, Top, Width, Height where the required Left and Top parameters give the coordinates of the upper-left...

Example Printing Pivot Tables

Now we can implement the PrintPivotTables feature of the SRXUtils application. A complex Excel workbook may have a large number of pivot tables scattered on various worksheets. A simple utility for printing these pivot tables can be useful. I have often been asked to write such a utility in my consulting practice. Our application displays a dialog box, as shown in Figure 20-45. The list box contains a list of all pivot tables. Each entry includes the pivot table's name, followed by the name of...

Chapter The Visual Basic Editor Part I

The first step in becoming an Excel VBA programmer is to become familiar with the environment in which Excel VBA programming is done. Each of the main Office applications has a programming environment referred to as its Integrated Development Environment IDE . Microsoft also refers to this programming environment as the Visual Basic Editor. Our plan in this chapter and Chapter 4 is to describe the major components of the Excel IDE. We realize that you are probably anxious to get to some actual...

The Pivot Table Object

To understand better what must be done next, we must discuss the PivotTable object and its various child collection objects. Invoking the PivotTableWizard method has created a PivotTable object named Sales amp Trans for us. All PivotTable objects have a PivotFields collection, accessed through the PivotFields property. Thus, the code Dim pf As PivotField For Each pf In Debug.Print pf.Name Next produces the following list of pivot fields Year Period Store Code Store City Store Type Transactions...

Setting a Charts Data Point Labels

As you may know, data labels can be edited individually by clicking twice pausing in between clicks on a data label. This places the label in edit mode, as shown in Figure 1-4. Once in edit mode, we can change the text of a data label which breaks any links or set a new link to a worksheet cell. Accomplishing the same thing programmatically is also very easy. For instance, the code MyChartSheet R12C2 sets the data label for the second data point to the value of cell B12. Note that the formula...

Chart Objects and Chart Object Objects

As you probably know, Excel charts can reside in a special type of sheet called a chart sheet or they can be embedded in an ordinary worksheet. Accordingly, a Chart object can represent a chart sheet standalone chart or an embedded chart. In the latter case, the Chart object is not contained directly in a worksheet. Rather, the worksheet contains a ChartObject object that acts as a container for the Chart object. Thus, for instance, if we create a new chart using the chart wizard, the fourth...

Perspective on the Excel Object Model

To put the Excel object model in some perspective, Table 15-1 gives some statistics on various Microsoft object models for Office 97 the numbers are somewhat larger for later versions of Office . Table 15-1. Some Object Model Statistics for Office 97 Table 15-1. Some Object Model Statistics for Office 97 For reference, Table 15-2 shows all nonhidden objects in the Excel XP object model, along with the number of children for each object. Table 15-2. Excel 10 objects and their child counts Table...

Getting Help

If you are like me, you will probably make extensive use of Microsoft's Excel VBA help files while programming. The simplest way to get help on an item is to place the cursor on that item and hit the F1 key. This works not only for VBA language keywords but also for portions of the VBA IDE. Note that Microsoft provides multiple help files for Excel, the VBA language, and the Excel object model. While this is quite reasonable, occasionally the help system gets a bit confused and refuses to...

Object Model Hierarchies

The fact that one object's properties and methods can return another object, thus creating the concept of child objects, is of paramount importance, for it adds a very useful structure to the object model. It seems by looking at the literature that there is not total agreement on when one object is considered a child of another object. For our purposes, if object A has a property or method that returns object B, then we will consider object B to be a child of object A and object A to be a...

Example The cmdUndo Click Event Procedure

If LabelsForUndo i .HasDataLabel Then DataSeries.Points i .HasDataLabel True DataSeries.Points i .DataLabel.Text LabelsForUndo i .Label If bCopyFormatting Then ' Restore formatting With DataSeries.Points i .DataLabel.Font .Name LabelsForUndo i .FontName .Size LabelsForUndo i .FontSize .Color LabelsForUndo i .Color .Bold LabelsForUndo i .Bold .Italic LabelsForUndo i .Italic End With End If Else DataSeries.Points i .HasDataLabel False End If Next

Example Selecting Special Cells

The Excel user interface does not have a built-in method for selecting worksheet cells based on various criteria. For instance, there is no way to select all cells whose value is between 0 and 100, or all cells that contain a date later than January 1, 1998. There is also no way to select only those cells in a given column whose value is different from the value of the preceding cell. This can be very useful when you have a sorted column and want to extract a set of unique values, as shown in...

Properties and Methods of the Chart Object

The 91 properties and methods of the Chart object are shown in Table 21-7. Table 21-7. Members of the Chart Object Table 21-7. Members of the Chart Object Table 21-8 shows the members of the Chart object that return children of the Chart object, along with the objects that they return. Note that several members can return a single object or a collection of objects. Table 21-8 shows the members of the Chart object that return children of the Chart object, along with the objects that they return....

Auto ShowRelated Members

The AutoShow method is used to restrict the display of pivot items for a given pivot field. The syntax is PivotFieldObject.AutoShow Type, Range, Count, Field All parameters are required for this method. The Type parameter has two possible values xlAutomatic activates the remaining parameters and, thereby, causes the restrictions to take effect, and xlManual disables the remaining parameters and causes Excel to remove any restrictions caused by a previous call to this method with Type equal to...

Variable Naming Conventions

VBA programs can get very complicated, and we can use all the help we can get in trying to make them as readable as possible. In addition, as time goes on, the ideas behind the program begin to fade, and we must rely on the code itself to refresh our memory. This is why adding copious comments to a program is so important. Another way to make programs more readable is to use a consistent naming convention for constants, variables, procedure names, and other items. In general, a name should have...

The Set statement

Declaring object variables is done in the same way as declaring nonobject variables. For instance, here are two variable declarations Dim int As Integer ' nonobject variable declaration Dim chrt As Chart ' object variable declaration On the other hand, when it comes to assigning a value to variables, the syntax differs for object and nonobject variables. In particular, we must use the Set keyword when assigning a value to an object variable. For example, the following line assigns the currently...

Example The Workbooks Open and Before Close Event Handlers

Private Sub Workbook BeforeClose Cancel As Boolean This event code just calls procedures to create or delete the custom menu. These procedures should be placed in a new code module, so add a module to the SRXUtils project and name it basMenus. Next, place the CreateCustomMenuItem procedure shown in Example 10-2 in basMenus. It is not necessary to completely understand this procedure now, since we will go over the details in Chapter 12. For the moment, note that Example 10-2 creates an...

Example A Simple Program to Trace

Dim ws As Worksheet Set ws ActiveSheet ' Insert a value into cell A1 ws.Cells 1, 1 .Value sample ws.Cells 2, 1 .PasteSpecial Paste xlValues End Sub Make sure that an empty worksheet is active in Excel. Switch to the VBA IDE and place the insertion point somewhere in the code. Then hit the F8 key once, which starts the tracing process. You can also choose Step Into from the Debug menu. Continue striking the F8 key, pausing between keystrokes to view the effect of each instruction in the Excel...

OnTime method

This method is used to run a procedure at a specific time or after a specific amount of time has passed. The syntax is Application.OnTime EarliestTime, Procedure, LatestTime, Schedule Of course, the Procedure parameter is the name of the macro to run. The EarliestTime parameter is the time you want the macro to be run. To specify a time, we use the TimeValue function. For instance, the following code executes the macro test in the ThisWorkbook code module of the bookl workbook at 3 58 P.M....

Example The Declarations Section of the basData Labels Code Module

Private Type utDataLabels HasDataLabel As Boolean Label As String FontName As String FontSize As Single Color As Long Bold As Boolean Italic As Boolean End Type Public LabelsForUndo As utDataLabels Public DataSeries As Series Public cPoints As Integer Public bCopyFormatting As Boolean Public oChart As Chart Note, in particular, the user-defined type declaration. This saves the original data labels for a possible Undo operation. It can hold not only the data label's text, but also the formatting...

Calculated Items and Calculated Fields

We have seen that it is possible to add a calculated field to a pivot table. A calculated field is special type of PivotField object that is not part of the original source data, but, instead, is calculated from source fields using a formula. Note that there is no such thing as a CalculatedField object, but there is a CalculatedFields collection. As we have seen, to create a new calculated field, we use the Add method of the CalculatedFields collection of the PivotTable object. The syntax is...

Subtotals Method

This method returns or sets the display of a particular type of subtotal for the specified pivot field. It is valid for all fields other than data fields. The syntax is where the optional Index parameter indicates the type of subtotal and is a number from Table 20-4. where the optional Index parameter indicates the type of subtotal and is a number from Table 20-4. Table 20-4. Values for the Subtotals Method's Index Parameter For instance, the following code requests a display of subtotals for...

Excel Templates

The purpose of an Excel template is to provide a starting place for a new workbook, worksheet, chart, or code module. Creating a template is easy. We simply create a new workbook and save it as a template using the Save As command. For instance, suppose we start a new workbook and enter the number 123 in the first cell of the first sheet. Then we save the workbook in the templates directory more on this later as a template called test.xlt. When we next invoke the New command from the File menu,...

Global Members

Many of the properties and methods of the Application object can be used without qualifying them with the word Application. These are called global members . For instance, the Selection property is global, and so we can write To identify the global members, the Excel object model has a special object called the Global object. This object is not used directly its purpose is simply to identify the global members of the object model. Note that the members of the Global object form a proper subset...

The Chart Group Object

As you no doubt know, an Excel chart can contain more than one data series. We have remarked that each series that is, the Series object has a ChartType property that can be used to set the chart type of the series. Thus, a single chart may have one or more series with a column-type format and one or more series with a line-type format. A ChartGroup object represents one or more series that are plotted on a chart with the same chart type. Note that a ChartGroup object is not a collection. To...

The This Workbook object

Under each node in the Project Explorer labeled Microsoft Excel Objects is a node labeled ThisWorkbook. This node represents the project's workbook, along with the code component also called a code module that stores event code for the workbook. We can also place independent procedures in the code component of a workbook module, but these are generally placed in a standard module, discussed later in this chapter. Simply put, the purpose of events is to allow the VBA programmer to write code...

Constants

The VBA language has two types of constants. A literal constant also called a constant or literal is a specific value, such as a number, date, or text string, that does not change, and that is used exactly as written. Note that string constants are enclosed in double quotation marks, as in Donna Smith and date constants are enclosed between number signs, as in 1 1 96 . For instance, the following code stores a date in the variable called dt A symbolic constant also sometimes referred to simply...

List box

Place a List box on the form as in Figure 14-5. Using the Properties window, set the properties shown in Table 14-1. Note that the TabIndex property determines not only the order that the controls are visited as the user hits the Tab key, but also determines which control has the initial focus. Since we want the initial focus to be on the list box, we set its tab index to 0. Table 14-1. Nondefault Properties of the ListBox Control Table 14-1. Nondefault Properties of the ListBox Control We...

User Form objects

As you no doubt know, Excel contains a great many built-in dialog boxes. It is also possible to create custom dialog boxes, also called forms or UserForms. This is done by creating UserForm objects. Figure 3-3 shows the design environment for the Select Special UserForm that we mentioned in Chapter 1. The large window on the upper-center in Figure 3-3 contains the custom dialog box named dlgSelectSpecial in its design mode. There is a floating Toolbox window on the right that contains icons for...

Close Method

The Close method closes the workbook. Its syntax is WorkbookObject.Close SaveChanges, FileName, RouteWorkbook Note that the Close method of the Workbook object has three parameters, unlike the Close method of the Workbooks object, which has none. The optional SaveChanges parameter is used to save changes to the workbook before closing. In particular, if there are no changes to the workbook, the argument is ignored. It is also ignored if the workbook appears in other open windows. On the other...

Example Getting the Used Range

As we mentioned in Chapter 18, the UsedRange method seems to have some problems, in that it does not always return what we would consider to be the currently used range, that is the smallest rectangular region of cells that contains all cells that currently have data. In any case, if you, too, have trouble with the UsedRange method, the following function can be used in its place. Note that the function GetUsedRange does assume that Excel's UsedRange method returns a superset of the correct...

The Data Label Object

A DataLabel object represents the data label of a chart data point or trendline . We discuss the Point object later in the chapter. Each Series object has a DataLabels collection that contains one DataLabel object for each point in the series. The Data-Labels collection is returned by the DataLabels method, as in If ActiveChart.SeriesCollection l . HasDataLabels Then MsgBox ActiveChart.SeriesCollection l . DataLabels.Count Note that if there are no data labels for a given series, then the...

File Related Members

Let us take a brief look at the members that are related to file operations. 16.1.6.1 DefaultFilePath property R W String This property returns or sets the default path that Microsoft Excel uses when it opens or saves files. This setting can also be changed by the user in the General tab of the Options dialog. 16.1.6.2 DefaultSaveFormat property R W Long This property returns or sets the default format for saving files. The default for this property is xlWorkbookNormal, indicating the normal...

The Comment Object

Recall that the AddComment method of the Range object is used to add a comment to a range. Once the comment has been added, a corresponding Comment object is created. Each comment object belongs to the Comments collection of the Worksheet object. To illustrate, the following code creates a comment in cell A1 if it does not already exist. It then sets the text and makes the comment visible for approximately three seconds. Note the use of the DoEvents statement to ensure that Windows has the...

Members that Affect the Current State of Excel

The following members have an effect on the current settings of Excel CopyObjectsWithCells property R WBoolean When this property is True, objects such as embedded controls or shapes are cut, copied, extracted, and sorted along with cells. This property returns or sets the appearance of the mouse pointer. It can be one of the following XlMousePointer constants Enum XlMousePointer xlDefault -4143 xlNorthwestArrow 1 xlWait 2 xlIBeam 3 End Enum It is considered good programming practice to set the...

Hidden Items and Visible Items Properties

The HiddenItems property returns the PivotItems collection of all hidden PivotItem objects, using the syntax It can also return a single or an array of hidden PivotItem objects using the syntax PivotFieldObject.HiddenItems Index where Index is a single index or an array of indexes. Similarly, the VisibleItems property returns a PivotItems collection of all visible PivotItem objects or a single or an array of such objects. The syntax is the same as for the HiddenItems property.

Members that Affect the Display

There are several members that affect the display of certain items DisplayAlerts property R WBoolean When True, Excel displays various warning messages such as a confirmation message that precedes the deletion of a worksheet while a macro is running. If you do not want a macro to be disturbed, then set this to False The default value of this property is True. DisplayCommentIndicator property R WLong This property affects the way that Excel indicates the presence of a comment in an unselected...

Run Time Design Time and Break Mode

The VBA IDE can be in any one of three modes run mode, break mode, or design mode. When the IDE is in design mode, we can write code or design a form. Run mode occurs when a procedure is running. To run or execute a procedure, just place the cursor anywhere within the procedure code and hit the F5 key or select Run from the Run menu . If a running procedure seems to be hanging, we can usually stop the procedure by hitting Ctrl-Break hold down the Control key and hit the Break key . Break mode...

The Excel Startup Folder

When Excel loads, it automatically loads any spreadsheets stored in its startup and alternate startup folders. The default location of the startup folder is usually a subfolder of the main Excel folder named XlStart. By default, there is no alternate startup folder, although one can be defined using the General tab of the Options dialog to open it, select Options from the Tools menu. Because the contents of these folders are opened at startup as ordinary workbooks, their macros are easily...

Arrays

An array variable is a collection of variables that use the same name, but are distinguished by an index value. For instance, to store the first 100 cells in the first row of a worksheet, we could declare an array variable as follows There is no Cell object in the Excel object model a cell is a special Range object. The array variable is Cell. It has size 100. The lower bound of the array is 1 and the upper bound is 100. Each of the following variables are Range variables that is, variables of...

ActiveX Controls on Worksheets

As you may know, ActiveX controls and standard Excel controls can be placed directly on a worksheet. Care must be taken, however, not to clutter up a worksheet with controls that would be better placed on a UserForm. When only a small number of controls are required, placing these controls directly on a worksheet may be appropriate. There are some special considerations when controls are placed directly on a worksheet. In particular, each ActiveX control on a worksheet not on a UserForm is...

Default Members

In most object models, many objects have a default member property or method that is invoked when a property or method is expected but we do not specify one. For instance, in the Microsoft Word object model, the default member for the Range object is the Text property. Hence, the VBA Word code Set rng ActiveDocument.Words 1 rng Donna sets the first word in the active document to Donna, since Word applies the default property in the last line, effectively replacing it with Unfortunately, neither...

Creating Chart Sheets

The Workbook object has a Charts property that returns the Charts collection of all chart sheets in the workbook. We can use the Add method of the Charts collection to create and add a new chartsheet to the workbook. ChartsObject .Add Before, After, Count As usual, this method returns a Chart object. The Before parameter specifies the sheet before which the new sheet is added, and the After parameter specifies the sheet after which the new sheet is added. Only one of these parameters can be...

Paste Special Method

This method pastes data from the Clipboard into the specified range. The syntax is RangeObject.PasteSpecial Paste, Operation, SkipBlanks, Transpose The Paste parameter indicates what will be pasted and is one of the following XlPasteType constants xlPasteValues -4163 xlPasteComments -4144 xlPasteFormulas -4123 xlPasteFormats -4122 xlPasteAllExceptBorders 6 End Enum The optional Operation parameter specifies a paste operation and can be one of the following xlPasteSpecialOperationNone -4142 '...

Appendix B Getting the Installed Printers

As discussed in Chapter 10, the ActivePrinter property can set the active printer. This raises the issue of how to determine the installed printers on a given computer. Unfortunately, VBA does not seem to provide a way to do this. Visual Basic has a Printers collection, but Visual Basic for Applications does not. In this appendix, we describe a program for getting this printer information. As mentioned in Chapter 10, this program uses the Windows API. To use this program, just type it into your...

Chapter BuiltIn Dialog Boxes

The Excel object model contains a Dialog object for each of Excel's built-in dialog boxes. These Dialog objects are kept in the Dialogs collection and are indexed by the XlBuiltInDialog constants shown in Table 13-1 and Table 13-2. The Dialogs collection is returned by the Dialogs property of the Application object. In Table 13-1, lt vX gt indicates that the constant is new for Excel version X. Table 13-1. XlBuiltInDialog constants and values Table 13-1. XlBuiltInDialog constants and values...

Chapter The Range Object

The Range object is one of the workhorse objects in the Excel object model. Simply put, to work with a portion of an Excel worksheet, we generally need to first identify that portion as a Range object. As Microsoft puts it, a Range object Represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range. Table 19-1 shows the 158 members of the Range object. Table 19-1. Members of the Range Object Table 19-1. Members of the Range Object...

Object Variables Save Execution Time

The main reason that objec t variables are important is not to improve readability, but to save execution time. In particular, to execute each of the five lines in the first version of the previous code, VBA needs to resolve the references to the various Excel objects ActiveSheet, Rows 1 , and Font. That is, VBA needs to climb down the Excel object model. This takes time. However, in the code that uses an object variable of type Font, VBA only needs to resolve these references once. Therefore,...

Example B The Get InstalledPrinters Procedure

Public Sub GetInstalledPrinters ByRef sPrinters As String, ByRef cPrinters As Long ' Sets cPrinters to the number of installed printers. ' Sizes and fills sPrinters array with the names ' of these printers. Dim KeyHandle As Long Dim KeyName As String Dim KeyLen As Long Dim Response As Long On Error GoTo ERR_INSTALLED_PRINTERS ReDim sPrinters 1 To 5 ' Open registry key whose subkeys are installed printers Response RegOpenKeyEx HKEY_LOCAL_MACHINE, 0, KEY_ENUMERATE_SUB_KEYS, KeyHandle ' If Error...

Vba Shape Zorder

Every Shape object has an order, called its z-order , that indicates the object's relative position with respect to an imaginary z-axis that comes directly out of the monitor at right angles, towards the user, as pictured in Figure A-2. The read-only ZOrderPosition property of a Shape object reports the current z-order of the object which, incidentally, is the same as the object's index within the Shapes collection. Shape objects with a larger z-order appear on top of objects with a smaller...

The Plot Area Object

The plot area of a chart see Figure 21-1 is the area where the chart data is plotted. For a 2-D chart, it consists of the data markers, gridlines, data labels, trend lines, and optional chart items, but not the axes. For a 3-D chart, it also includes the walls, floor, axes, axis titles, and tick-mark labels in the chart. The plot area is surrounded by the chart area which does contain the axes on a 2-D chart . The PlotArea object has Border, ChartFillFormat, and Interior children used for the...

Chapter The Excel Object Model

The Excel object model is one of the most extensive object models in Microsoft's arsenal, with almost 200 objects and over 5000 properties and methods. As we have mentioned, however, many of these objects and members are included solely for backward compatibility with earlier versions of Excel. When we ignore these objects and members, the object count drops to 140 and the member count is about 3000. This makes the Excel object model second in size only to the Word object model. We will not...

Print Out Method

The PrintOut method prints an entire workbook. This method applies to a host of other objects as well, such as Range, Worksheet, and Chart. The syntax is WorkbookObject.PrintOut From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate Note that all of the parameters to this method are optional. The From parameter specifies the page number of the first page to print, and the To parameter specifies the last page to print. If omitted, the entire object range, worksheet, etc. is printed. The...

Chart Type property

The ChartType property is a read-write property that can be set to any one of the XlChartType constants in Table 21-1. Scatter with Lines and No Data Markers Scatter with Smoothed Lines and No Data Markers In Example 21-3, at the end of the chapter, we present a macro that scrolls through the chart types in Table 21-1, allowing you to determine which chart type is appropriate for a particular purpose. Note that it is possible that the return value of the ChartType property may not be one of the...

OnKey method

Where Key is the key or key combination written as a string that will execute the macro and Procedure is the name of that macro. Note that we can alter the normal behavior of Excel by assigning a key combination to the Key parameter that has a normal Excel response such as Ctrl-S for save . If we assign an empty string to the Procedure parameter, then Excel will omit its normal response so nothing will happen . If we omit the Procedure parameter, then Excel will return the key combination to...

The With Statement

In fact, VBA provides a With statement to handle just the situation in the previous example, which could be written as follows Set fnt ActiveSheet.Rows 1 .Font With fnt .Bold True .Italic True .Underline False .Size 12 .Name Arial End With The general syntax of the With statement is where the statements generally refer to the object, but do not require qualification using the object's name, as in the previous example.

Creating Shapes

An AutoShape is a Shape object that represents a built-in drawing. To add a new AutoShape object, we use the AddShape method, whose syntax is ShapesObject.AddShape Type, Left, Top, Width, Height The parameter Type is the type of AutoShape to create. It can be any one of the MsoAutoShapeType constants in Table A-1. The required parameters Left and Top specify the position in points as a Single of the upper-left corner of the bounding box for the AutoShape object, measured relative to the...

Creating a Chart

We have seen that a PivotTable is created and added to the PivotTables collection by invoking the PivotTableWizard method. On the other hand, creating a new chart requires a different approach, since it depends upon whether the chart is standalone a chart sheet or embedded in a worksheet and thus contained in a ChartObject object . Also, unlike the PivotTableWizard method, the ChartWizard method does not create a chart it merely formats an existing chart. Accordingly, there are three steps...

Copy FromRecordset Method

For those readers familiar with DAO, CopyFromRecordset is a very powerful method that copies the contents of a DAO Recordset object onto a worksheet, beginning at the upper-left corner of the specified range. Note that if the Recordset object contains fields with OLE objects in them, this method fails. To illustrate, consider the following code, which requires that a reference to Microsoft DAO is set in the References dialog box in the Excel VBA Tools menu OpenRecordset Objects 10, 10 This code...

Excels Standard Controls

Figure 14-3 shows two toolboxes, each of which provides access to one type of control. Below each toolbox is a control created using that toolbox. The controls on the Control Toolbox on the left in Figure 14-3 are ActiveX controls. These controls can be placed either on a UserForm or directly on a worksheet but not a chartsheet . They are the same as the controls that are accessible from the VB editor's Toolbox when designing a UserForm. ActiveX controls are...

Modularity

Another major issue that relates to readability is that of modular programming. In the early days of PC programming in BASIC , most programs were written as a single code unit, sometimes with many hundreds or even thousands of lines of code. It is not easy to follow such a program, especially six months after it was written. Also, these programs tended to contain the same code segments over and over, which is a waste of time and space. The following BASIC example will illustrate the point. Line...

The Pivot TableWizard Method

To create a PivotTable through code, we use the PivotTableWizard method of the Worksheet object or the PivotTable object. Contrary to what you might assume, the PivotTableWizard method does not start the PivotTable wizard. Rather, it is used to create a PivotTable when applied to the Worksheet object or to modify an existing PivotTable when applied to the PivotTable object. SourceData, TableDestination, TableName, RowGrand, ColumnGrand, SaveData, HasAutoFormat, AutoPage, Reserved,...