Referring to a Control on a Worksheet

Fortunately, Excel lets us refer to an ActiveX control on a worksheet by using its name, without reference to the OLEObjects collection. For instance, if we place a command button on a worksheet, Excel will give it the default name CommandButton1. Both of the following lines set the height of this command button to 20 points 20 ActiveSheet.CommandButtonl.Height 20 Unfortunately, however, the properties and methods that we access in this manner are the properties and methods of the OLEObject,...

Column Differences and Row Differences Methods

The ColumnDifferences method returns a Range object that represents all the cells in the range whose contents are different from certain comparison cells there is one comparison cell in each column . The syntax is where ComparisonCell is a range object that represents a single cell. The purpose of ComparisonCell is simply to identify the row whose cells contain the comparison values. To illustrate, consider the following code, whose results are shown in Figure 19-6 Dim rng As Range, rng2 As...

Pivot Select and Pivot Selection

The PivotSelect method selects part of a PivotTable. The syntax is PivotTableObject .PivotSelect Name, Mode The Mode parameter specifies the selection mode and can be one of the following Enum XlPTSelectionMode xlDataAndLabel 0 xlLabelOnly 1 xlDataOnly 2 xlOrigin 3 xlBlanks 4 xlButton 15 xlFirstRow 256 ' Excel 9 only The Name parameter specifies the selection in what Microsoft refers to as standard PivotTable selection format. Unfortunately, the documentation does not tell us what this means,...

Creating a New Menu Bar or Toolbar

As we have said, one way in which menu bars and toolbars differ from menus and submenus is in their creation. To create a new menu bar or shortcut menu, we use the Add method of the CommandBars collection. The syntax for the Add method is CommandBarsObject.Add Name, Position, MenuBar, Temporary The optional Name parameter is the name of the new command bar. If this argument is omitted, Excel VBA assigns a default name such as Custom 1 to the command bar. The optional Position parameter gives...

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 ....

Indent Level Property and Insertlndent Method

The IndentLevel property returns or sets the left indent for each cell in the range and can be any integer between 0 and 15. All other settings cause an error. Presumably, indents are useful for aligning the contents of cells or for formatting text. For instance, to set the indent level of cell A1 to 10, we can write Range A1 .IndentLevel 10 Unfortunately, the documentation does not specify how big an indent unit is, but we can still use indent units in a relative way. Presumably, an indent...

Marker Size and Marker Style

Marker Size Excel

The MarkerSize property returns or sets the size of a data point in points as a Long . The property also applies to the Series object, in which case it sets all markers in the series at once. The MarkerStyle property determines the style of the data point and can be one of the following values xlMarkerStyleX -4168 xlMarkerStylePicture -4147 xlMarkerStyleNone -4142 xlMarkerStyleDot -4118 xlMarkerStyleDash -4115 xlMarkerStyleAutomatic -4105 xlMarkerStyleSquare 1 xlMarkerStyleDiamond 2...

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 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...

The Allow EditRange Object

The AllowEditRange object allows a specified range of cells on a worksheet to be password protected from editing. Once a range has been protected in this way, and the entire worksheet has been protected, any attempt at editing cells in that range will require the password. Here is some code that assigns a password to a range on the active worksheet. It also demonstrates the use of the AllowEditRanges collection. Dim ws As Worksheet Dim i As Integer ' Delete all current protection ranges 'MsgBox...

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...

Procedure and Full Module Views

Generally, a code module (standard, class, or UserForm) contains more than one procedure. The IDE offers the choice between viewing one procedure at a time (called procedure view) or all procedures at one time (called full-module view), with a horizontal line separating the procedures. Each view has its advantages and disadvantages, and you will probably want to use both views at different times. Unfortunately, Microsoft has not supplied a menu choice for selecting the view. To change views, we...

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...

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,...

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....

Example Printing Embedded Charts

We can now implement the PrintCharts feature of our SRXUtils application. This is designed to provide a list of the embedded charts in the active workbook, so the user can select from this list and print the selected charts. To print a chart sheet, use the PrintSheets utility. Implementing the PrintCharts utility is similar to implementing the PrintSheets and PrintPivotTables utilities, which we did earlier in the book. At the present time, this print utility, located in the Print.utl add-in,...

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...

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...

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...

The Legend Entry Object

Figure 21-1 shows a chart legend with two legend entries. Legend entries are represented by LegendEntry objects. The LegendEntry objects for a legend are kept in the LegendEntries collection object for the Legend object. This collection is accessed using the LegendEntries property of the Chart object. Each legend entry has two parts the text of the legend entry is the name of the series associated with that entry and the entry key also called an entry marker is a small copy of the associated...

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...

Example Listing Excels Command Bar Objects

Dim sType as string, cbar as CommandBar For Each cbar In Application.CommandBars Select Case cbar.Type Case msoBarTypeNormal ' A toolbar sType Normal Case msoBarTypeMenuBar ' A menu bar sType Menu bar Case msoBarTypePopup ' Menu, submenu sType Popup End Select Debug.Print cbar.Name amp , amp sType amp , amp cbar.Visible Next End Sub If you execute this code, you should get the following entries, among many others Worksheet Menu Bar,Menu bar,True Chart Menu Bar,Menu bar,False This indicates that...

Creating a New Command Bar Control

To create and add a command-bar control to a command bar, use the Add method of the CommandBarControls collection. This method returns a CommandBarButton, CommandBarComboBox, or CommandBarPopup object, depending on the value of the Type parameter. The syntax is CommandBarControlsObject.Add Type, Id, Parameter, Before, Temporary Type is the type of control to be added to the specified command bar. Table 12-1 shows the possible values for this parameter, along with the corresponding control and...