The Fundamental Properties
The PivotField object has a handful of basic properties that you will almost always want to set. 20.7.3.1 Function property This property applies only to data fields and returns or sets the aggregate function used to summarize the pivot field. It can be one of the following XlConsolidationFunction constants xlVarP -4165 xlVar -4164 xlSum -4157 xlStDevP -4156 xlStDev -4155 xlProduct -4149 xlMin -4139 xlMax -4136 xlCountNums -4113 xlCount -4112 xlAverage -4106 xlUnknown 1000 ' Excel 9 only This...
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...
TotalsRelated Members
The PivotTable object has two properties that affect the display of totals. When this property is True, the PivotTable shows grand column totals. When this property is True, the PivotTable shows grand row totals. To illustrate, referring to the pivot table in Figure 20-6, the code ColumnGrand False RowGrand False produces the pivot table in Figure 20-9, with no grand totals. We can also suppress the display of individual pivot-field totals, such as the totals for Store City in Figure 20-9. This...
Create the Code Behind the UserForm 1
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 UserForm 1
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...
The Range Object as a Collection
The Range object is rather unusual in that it often acts like a collection object as well as a noncollection object. For instance, it has an Item method and a Count property. On the other hand, the Range object has many more noncollection-type members than is typical of collection objects. In particular, the average member count among all other collection objects is 19, whereas the Range object has 158 members. Indeed, the Range object should be thought of as a collection object that can hold...
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...
Table A Members of the DiagramNodeChildren object
We can use the Children property of the DiagramNode object to return this collection object. For instance, to get the last child of a node, we can write Unfortunately, the programmatic aspects of the Diagram-related objects do not seem to be functioning correctly as of Service Pack 1 of Excel XP , as we will see shortly. To create a diagram programmatically, we use the AddDiagram method of the Shapes collection of the worksheet. The following code creates the diagram in Figure A-6. Note,...
Range Property
The Range property applies to the Application, Range, and Worksheet objects. Note that When Range is used without qualification within the code module of a worksheet, then it is applied to that sheet. When Range is used without qualification in a code module for a workbook, then it applies to the active worksheet in that workbook. Thus, for example, if the following code appears in the code module for Sheet2 Worksheets 1 .Activate Range D1 .Value test then its execution first activates Sheet1,...
CurrentRegion Property
This useful property returns a Range object that represents the current region, which is the region bound by the closest empty rows and columns. To illustrate, the following code, when applied to the sheet in Figure 19-11, selects the rectangular region A2 C4 Figure 19-11. Illustrating CurrentRegion
Selecting Ranges
The PivotField object has two properties related to selecting portions of the pivot table related to the field. This property returns a Range object representing the value area associated with the given PivotField. To illustrate, the code PivotFields Store Type . DataRange.Select Figure 20-26. DataRange for Store Type The LabelRange property returns a Range object that represents the label cells for the PivotField. To illustrate, the code PivotFields Store Type . LabelRange.Select will select...
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...
Miscellaneous Members
Here are some additional members of the Application object. 16.1.9.1 CellFormat, FindFormat and ReplaceFormat object The CellFormat object works in conjunction with the FindFormat and ReplaceFormat properties of the Application object to programmatically find and replace cell formatting. Specifically, the new FindFormat and ReplaceFormat properties of the Application object each return a unique CellFormat object. We can set the formatting properties of either of these CellFormat objects and...
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 Ojc
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...
ShowAllItems Property
This read-write Boolean property is True if all items in the PivotTable are displayed. The default value is False, in which case the pivot items that do not contribute to the data fields are not displayed. Note that this property corresponds to the Show items with no data check box on Excel's PivotTable Field dialog box. To illustrate, suppose we add a single row to our source table 1998 1 BO-1 BOSTON AStoreType 1000 10000.00 This row creates a new store type but adds data only for Boston. The...
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...
The Styles Collection and the Style Object
A Style object represents a set of formatting options for a range. Each workbook has a Styles collection containing all Style objects for the workbook. To apply a style to a range, we simply write where StyleName is the name of a style. To create a Style object, use the Add method, whose syntax is Note that the Add method returns the newly created Style object. The Name parameter specifies the name of the style, and the optional BasedOn parameter specifies a Range object that refers to a cell...
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,...
PivotTable PivotField and PivotItem Properties
The PivotTable property returns the PivotTable object associated with the PivotCell. The PivotField property returns the PivotField object associated with the PivotCell and the Pivotltem property returns the Pivotltem associated with the PivotCell. Referring to Figure 20-6, the following code illustrates these properties Dim rng As Range Dim ws As Worksheet Debug.Print rng.PivotCell.PivotTable.Name ' returns Debug.Print rng.PivotCell.PivotField ' returns Debug.Print rng.PivotCell.PivotItem.Name...
Option Explicit
To avoid the problem described in the previous example, we need a way to make Excel refuse to run a program if it contains any variables that we have not explicitly declared. This is done simply by placing the line in the Declarations section of each code module. Since it is easy to forget to do this, VBA provides an option called Require Variable Declaration in its Options dialog box. When this option is selected, VBA automatically inserts the Option Explicit line for us. Therefore, I strongly...
Example The IsBookOpen Function
Private Function IsBookOpen sWkbName As Boolean ' Check to see if workbook is open ' Note that an add-in workbook does not appear in ' the Workbooks collection, so we need another method. ' However, an add-in can be referenced by name, so we simply ' access its Name property. If an error occurs, then On Error GoTo WkbNotOpen IsBookOpen True
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...
SmartTagAction Object
The SmartTagActions property returns the SmartTagActions collection of SmartTagAction objects. A SmartTagAction object represents an action that can be taken for a smart tag. As an example, referring to Figure 22-2, the code Set st For i 1 To st.SmartTagActions.Count Debug.Print st.SmartTagActions i .Name Next which corresponds to the four actions in Figure 22-2. Incidentally, the code does not print anything, nor does it produce an error message The SmartTagAction object has an Execute method...
Address Property ReadOnly String
The Address property returns the range reference of the Range object as a string. The syntax is RangeObject.Address RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo RowAbsolute is set to True the default to return the row part of the reference as an absolute reference. ColumnAbsolute is set to True the default to return the column part of the reference as an absolute reference. ReferenceStyle can be one of the XlReferenceStyle constants xlA1 or xlR1C1. The default value is...
End Property
This property returns a Range object that represents the cell at the end of the region that contains the source range by mimicking a keystroke combination see the following code . The syntax is where RangeObject should be a reference to a single cell and Direction is one of the following constants. The keystroke combination is also given in the following code. selects the rightmost cell in Row 4 for which all cells between that cell and cell C4 are nonempty that is, the cell immediately to the...
SmartTag Object
The Range object has a SmartTags property that returns the collection of all SmartTag objects. Of course, a SmartTag object represents a smart tag. Let us discuss the more important members of the SmartTag object, listed next. The Delete method is self-explanatory. The Name property of the SmartTag object returns a fully qualified name for the smart tag. A fully qualified name consists of a XML namespace URI followed by a number sign and then the tag name. For instance, referring to the smart...
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...
Named Arguments
Some VBA procedures can contain a large number of parameters. For example, one form of the Excel SaveAs function has the declaration SaveAs Filename As string, FileFormat As VARIANT, Password As VARIANT, _ WriteResPassword As VARIANT, ReadOnlyRecommended As VARIANT, _ CreateBackup As VARIANT, AddToMru As VARIANT, TextCodepage As _ VARIANT, TextVisualLayout As VARIANT where all of the parameters are optional. Here is an example of a call to this procedure SaveAs c temp test.xls, , , , , True , ,...
The Immediate If Function
The Immediate If function has the syntax IIf Expression, TruePart, FalsePart If Expression is True, then the function returns TruePart. If Expression is False, the function returns FalsePart. For instance, the following code displays a dialog indicating whether or not the first row in the active worksheet is empty MsgBox IIf IsEmpty ActiveSheet.Cells 1, 1 , Cell is empty, Cell is not empty It is very important to note that the Immediate If function always evaluates both TruePart and FalsePart,...
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 PivotTable 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 ChartObject 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...
Children of the Worksheet Object
Let us discuss a few of the children of the Worksheet object. Others will be discussed in later chapters. The Comments property returns the Comments collection, which consists of all Comment objects comments in the worksheet. We will discuss the Comment object in Chapter 19. We discussed the Names collection and Name objects in Chapter 16, and so we refer the reader to that earlier discussion. To illustrate Excel outlining using code, consider the worksheet shown in Figure 18-3. Our goal is to...
A 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...
AutoFill Method
This important method performs an autofill on the cells in the range. Its syntax is RangeObject.AutoFill Destination, Type Here Destination is the Range object whose cells are to be filled. The destination must include the source range that is, the range that contains the data to use for the autofill. The optional Type parameter specifies the fill type. It can be one of the following XlAutoFillType constants note that the Excel documentation refers to a nonexistent Enum XlAutoFillType...
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...
UpBars and DownBars
UpBars and DownBars are shown in Figure 21-17. These bars give a quick indication of the difference between data values in two different data series of line type. The UpBars are in white and DownBars are in black. The code to generate these bars is With ActiveChart.LineGroups 1 .HasUpDownBars True .UpBars.Interior.Color RGB 255, 255, 255 .DownBars.Interior.Color RGB 0, 0, 0 End With Note that UpBars and DownBars apply only to 2-D line-type charts. Figure 21-17. UpBars are in white and DownBars...
Width Height Top and Left Properties
These properties return values for the entire range, in points. For instance, the Top property returns the distance, in points, from the top of row 1 to the top of the first leftmost area of the range. Note that when applied to a column, the Width property returns the width, in points, of the column. However, the relationship between Width and ColumnWidth can seem a bit strange at first. For instance, the following code shows that a column of ColumnWidth 1 has Width 9.6 but a column of...
VBA Operators
VBA uses a handful of simple operators and relations, the most common of which are shown in Table 5-5. Table 5-5. VBA Operators and Relations The Mod operator returns the remainder after division. For example returns 2, since the remainder after dividing 8 by 3 is 2. To illustrate string concatenation, the expression To be or amp not to be is equivalent to
Example The CreateCustomMenuItem Procedure
' Check for custom menu. If it exists then exit. Set cbcpop Application.CommandBars Worksheet menu bar . FindControl Type msoControlPopup, If Not cbcpop Is Nothing Then Exit Sub ' Control does not exist -- create it. Set cbcpop Application.CommandBars Worksheet menu bar . Controls.Add Type msoControlPopup, Temporary True cbcpop.Caption Cu amp stom ' Set tag property to find it later for deletion cbcpop.Tag SRXUtilsCustomMenu With Temporary True .Caption amp ActivateSheet .OnAction ActivateSheet...
The Border Object
The most interesting properties and methods of the Border object are described in this section. 19.4.3.1 Color property This property returns or sets the primary color of the border. It can also be applied to the Borders collection to set all vertical and horizontal lines for the borders at the same time. The property also applies to Font objects and Interior objects. For instance, the following code has the effect shown in Figure 19-22 With Range a1 b4, d2 e2 .Interior.Color RGB 196, 196, 196...
Offset Property
The Offset property is used to return a range that is offset from a given range by a certain number of rows and or columns. The syntax is RangeObject.Offset RowOffset, ColumnOffset where RowOffset is the number of rows and ColumnOffset is the number of columns by which the range is to be offset. Note that both of these parameters are optional with default value 0, and both can be either positive, negative, or 0. For instance, the following code searches the first 100 cells to the immediate...
Example The ActivateSelectedSheet Procedure
End If Unload Me End Sub This code demonstrates some list box properties. First, the ListIndex property returns the index number starting at 0 of the currently selected item in the list box. Thus, the following code checks to see if an item is selected otherwise ListIndex -1 If lstSheets.ListIndex gt -1 Then The code returns the ith item in the list box as a string . Thus is the currently selected item that is, the currently selected sheet name. Finally, the code activates that worksheet by...
GridlineRelated Properties and the Gridline Object
The Axis object has several properties that relate to gridlines they are described in the following list Set this read-write property to True to show major gridlines for the axis, or False to hide gridlines. Applies only to primary axes not secondary axes . Set this read-write property to True to show minor gridlines for the axis, or False to hide gridlines. Applies only to primary axes not secondary axes . This read-only property returns a Gridlines object that represents the major gridlines...
The Do Loop
The Do loop has several variations. To describe these variations, we use the notation to represent either the word While or the word Until, but not both. With this in mind, here are the possible syntaxes for the Do loop ' code block here Loop While Until condition Actually, there is a fifth possibility, because we can dispense with condition completely and write Some of these variations are actually quite subtle. For instance, the following code cycles through the cells in the first row of the...
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...