Starting Up

There is very little difference in Excel 2007 between a normal workbook and an Add-in. The code and UserForms can be modified in the same manner, and they both offer the same level of protection (locking the Project from view). The two advantages of using an Add-in to hold your tools are that it is invisible within the Excel User Interface, and that it can be loaded using Excel's Add-ins dialog (Office Menu O Excel Options O Add-Ins O Manage Excel Add-Ins O Go). This chapter uses the term...

The Designer Object

Some VBComponents (such as UserForms) present both code and a graphical interface to the developer. Whereas the code is accessed through the CodeModule and CodePane objects, the Designer object gives you access to the graphical part. In the standard versions of Office, UserForms are the only components with a graphical interface for you to control. However, the Developer Editions included in Office 2000 and XP included a number of other items (such as the Data Connection Designer) that have...

Characters Object Example

This example formats all the capital letters in the active cell in red with 16-point bold text 'Is this character a capital letter If Asc(Mid(sText, i, 1)) > 64 And Asc(Mid(sText, i, 1)) Set oChars ActiveCell.Characters(i, 1) 'Format the Characters object in Red, 16pt Bold.

Common Properties and Methods

Most of the objects in the VBE object library have the following common properties. To avoid redundancy, these properties will be listed for each object, but will not be explained. Collection Read-only. Returns the collection to which an object belongs. For example, a Reference object belongs to the References collection. The Collection property is used for objects that belong to collections. Collection Read-only. Returns the collection to which an object belongs. For example, a Reference...

Charts

In this chapter, you see how you can use the macro recorder to discover what objects, methods, and properties are required to manipulate charts. You will then improve and extend that code to make it more flexible and efficient. This chapter is designed to show you how to gain access to Chart objects in VBA code so that you can start to program the vast number of objects that Excel charts contain. You can find more information on these objects in Appendix A. Specifically, this chapter examines...

Working with XML and the Open XML File Formats

XML (Extensible Markup Language) functionality has been available in various forms since Office 2000. It made its debut in the Office suite of applications in 1999 with relatively little fanfare, waiting there quietly until the release of Office 2003, where it was touted as one of the most significant improvements in Excel. Office 2003 came with many new XML capabilities and the promise of major changes in the way businesses would work with data. In addition to seamless exchange of data, XML...

OnTime

You can use the OnTime method to schedule a macro to run sometime in the future. You need to specify the date and time for the macro to run, and the name of the macro. If you use the Wait method of the Application object to pause a macro, all Excel activity, including manual interaction, is suspended. The advantage of OnTime is that it allows you to return to normal Excel interaction, including running other macros, while you wait for the scheduled macro to run. Say you have an open workbook...

The Workbooks Collection

The Workbooks collection consists of all the currently open Workbook objects in memory. Members can be added to the Workbooks collection in a number of ways. You can create a new empty workbook based on the default properties of the Workbook object, or you can create a new workbook based on a template file. Finally, you can open an existing workbook file. To create a new empty workbook based on the default workbook, use the Add method of the Workbooks collection The new workbook will be the...

Editing Data Series

The SetSourceData method of the Chart object is the quickest way to define a completely new set of data for a chart. You can also manipulate individual series using the Series object, which is a member of the chart's SeriesCollection object. The following example is designed to show you how to access individual series. The code will take the MangoesChart and delete all the series from it, and then replace them with four new series, one at a time. The new chart will contain product information...

Worksheet Methods

Name Returns Parameters Description Calculates all the formulas in the worksheet Returns either a chart object (ChartObject) or a collection of chart objects (ChartObjects) in a worksheet CustomDictionary , IgnoreUppercase , AlwaysSuggest , SpellLang Checks the spelling of the text in the worksheet. A custom dictionary can be specified (CustomDictionary), all uppercase words can be ignored (IgnoreUppercase), and Excel can be set to display a list of suggestions (AlwaysSuggest) Circles the...

Sending Data from the Client to the Server Application

Two mechanisms can be used to send information to a web server. You can either include the information as part of the URL string or send it as a separate section of the HTTP request. Parameters can be included within the URL string by appending them to the end of the URL, with a question mark ( ) between the URL and the first parameter and an ampersand (& ) between each parameter This has the advantage that the parameters form part of the URL and hence can be stored in the user's Favorites...

Application Object

The Application object is the root object of the Excel object model. All the other objects in the Excel object model can only be accessed through the Application object. Many objects, however, are globally available. For example, the ActiveSheet property of the Application object is also available globally. That means that the active worksheet can be accessed in at least two ways Application.ActiveSheet and ActiveSheet. The Application object holds most of the application-level attributes that...

Chart Object and the Charts Collection

The Charts collection holds the collection of chart sheets in a workbook. The Workbook object is always the parent of the Charts collection. The Charts collection only holds the chart sheets. Individual charts can also be embedded in worksheets and dialog sheets. The Chart objects in the Charts collection can be accessed using the Item property. The name of the chart can be specified either as a parameter to the Item property's parameter or an index number describing the position of the chart...

The sNumToUS Function

This function converts a number, date, or Boolean variable to a U.S.-formatted string. There is an additional parameter that can be used to return a string using Excel's DATE function, which would typically be used when constructing .Formula strings Function sNumToUS(vValue As Variant, Optional bUseDATEFunction) As String 'Don't accept strings or arrays as input If TypeName(vValue) String Then Exit Function If Right(TypeName(vValue), 2) () Then Exit Function If IsMissing(bUseDATEFunction) Then...

Querying Microsoft Excel Workbooks

When using ADO to access data from Excel 2007 workbooks, you use the same OLE DB provider that you used earlier in this chapter to access data from Microsoft Access 2007. In addition to Access, this provider also supports most ISAM data sources (data sources that are laid out in a tabular, row and column format). You will use the Sales.xlsx workbook, shown in Figure 20-7, as the data source for the Excel examples. When using ADO to work with Excel, the workbook file takes the place of the...

Excel Vba Formulaarray

Experimenting in the Immediate Window 29 Calling Functions and Sub Procedures 35 Parentheses and Argument Lists 37 Scope and Lifetime of Variables 40 Chapter 2 The Application Object_63 Chapter 3 Workbooks and Worksheets_77 Getting a Filename from a Path 78 Overwriting an Existing Workbook 81 Ranges on Inactive Worksheets 96 Range Property of a Range Object 97 Ranges of Inactive Worksheets 99 More on the Cells Property of the Range Object 99 Single-Parameter Range Reference 101 Referring to...

The Recorded Macro

The recorded macro should look like the following The recorded macro should look like the following ActiveChart.SetSourceData ActiveChart.ChartType xlColumnClustered ActiveChart.ChartTitle.Text Mangoes Although you inserted a new chart sheet, the recorded macro uses the AddChart method of the Shapes object to create an embedded chart in the Sales worksheet. (Note that the recorder prefers to refer to a ChartObject as a Shape object, which is an alternative pointed out at the beginning of this...

Access Excel and Outlook

As another example of integrating different Office applications, you will extract some data from Access, chart it using Excel, and e-mail the chart using Outlook. The code has been set up as four procedures. The first procedure is a sub procedure named EmailChart that establishes the operating parameters and executes the other three procedures. Note that the code uses early binding, and you need to create references to the ADO and Outlook object libraries 'Gets data from Access using SQL...

Interpreting CStyle Declarations

The MSDN library is the best source for information about the functions in the Windows API, but it's primarily targeted toward C and C programmers and displays the function declarations using C notation. The API viewer mentioned in the previous section contains many of the declarations for the core Windows functions in VBA notation, but if you encounter a function that it does not include, it is usually possible to convert the C notation to a VBA Declare statement, using the following method....

The Basics of Using XML Data in Excel

As intimidating as an XML document may seem, it's really nothing more than a text file that contains data wrapped in markup tags that denote structure and meaning . These tags essentially make the text file machine-readable. The term machine-readable essentially means that any application or web-based solution designed to read XML files will be able to discern the structure and content of your file. Because XML is text-based, it is not platform-dependent. That is to say, XML is not dependent on...

Programmatically Changing XML Map Properties

Once an XML map exists, there may be cause to change a few of its properties programmatically. In the following code snippet, you pass the name of your map the XMLMaps collection, and then adjust each available property. These properties coincide with those discussed in the section called Consuming XML Data Directly With .ShowImportExportValidationErrors False 'Data formatting and layout properties .AdjustColumnWidth True .PreserveColumnFilter True .PreserveNumberFormatting True 'Overwrite or...

Using the Internet for Storing Workbooks

The simplest way of sharing information is to store workbooks on a web server. Though Excel 97 introduced the ability to download workbooks from web sites, Excel 2000 and 2007 extended that to allow you to save workbooks as well. They do this by using the FrontPage Server Extensions, which must be running on the server. To open and save a workbook from or to a web site, use the URL instead of the filename Sub OpenFromWebSiteAndSaveBack Dim oBk As Workbook 'Open a workbook from a web site Set...

Using Web Queries

Web Queries were introduced in Excel 97 and have been enhanced in each subsequent version of Excel. They enable you to retrieve a single table of information from a web page, with options to automatically refresh the data each time the workbook is opened, or at frequent intervals. One of the problems with Web Queries is that Excel uses the thousands and decimal separators specified in the Windows Regional Settings when attempting to recognize numbers in the page. If the exchange rate web page...

Code Module Properties

Returns the active CodePane for the module. If there is no visible CodePane, one is created and displayed. Note that a CodeModule can have up to two code panes, but there is no CodePanes collection for them Read-only. Returns the number of lines at the top of the module used for Dim, Type, and Option statements. If there are any such items at the top of the module, any comments following them are considered to be part of the following procedure, not the declarations. The following...

Run Time Error Handling

When you are designing an application, you should try to anticipate any problems that could occur when the application is used in the real world. You can remove all the bugs in your code and have flawless logic that works with all permutations of conditions, but a simple operational problem could still bring your code crashing down with a less than helpful message displayed to the user. For example, if you try to save a workbook file to the floppy disk in the A drive, and there is no disk in...

Adding a Chart Sheet Using VBA Code

There is no need to create an embedded chart. You can simply add a chart sheet and set its properties directly. You can also create an object variable, so that you have a simple and efficient way of referring to the chart in subsequent code. Rather than limit yourself to the preset layouts, you can select the chart features you want, such as a title. There is no need to plot by columns and then plot by rows. The following code incorporates these changes Sub...

Worksheets Collection Properties and Methods

Returns a collection holding all the horizontal page breaks associated with the Worksheets collection Set Get whether the worksheets in the collection are visible. Also can set this to xlVeryHidden to prevent a user from making the worksheets in the collection visible Read-only. Returns a collection holding all the vertical page breaks associated with the Worksheets collection Method. Parameters Before , After , Count , Type . Adds a worksheet to the collection. You can specify where...

Popup Menus

Excel's built-in shortcut menus are included in the command bar listing created by the macro ListFirstLevelControls, which you saw earlier in this chapter. The following modified version of this macro shows only the command bars of type msoBarTypePopup, as shown in Figure 15-11. SHide PivotChart Field Buttons 3956 gt W tateifl . All Lei-els FatsEs I PofNfiE1 The code to display the popups is shown here Dim ctl As CommandBarControl Dim cbr As CommandBar Dim iRow As Integer If Not...

Embedded Charts

Excel 2007 Userforms Charts

When you create a chart embedded as a ChartObject, it is a good idea to name the ChartObject so that it can be easily referenced in later code. When you select the chart, you will see its name to the left of the Formula bar at the top of the screen in the name box. You can select and change the name of the ChartObject in the name box and press Enter to update it. The embedded chart in Figure 8-4 was created, dragged to its new location, and had its name changed to MangoesChart. The name can...

Object Variables

The variables you have seen so far have held data such as numbers and text. You can also create object variables to refer to objects such as worksheets and ranges. The Set statement is used to assign an object reference to an object variable. Object variables should also be declared and assigned a type as with normal variables. If you don't know the type, you can use the generic term Object as the type Dim objWorkbook As Object Set objWorkbook ThisWorkbook MsgBox objWorkbook.Name It is more...

Watch Object Example

The following routine prompts the user for a range, then loops through each cell in the range and adds it to the Watch window. It then displays the Watch window Set rngWatches Application.InputBox _ Please select a cell or cell range to watch, 'View the watch window based on their answer Application.CommandBars Watch Window .Visible

Range Object and the Ranges Collection Object

The Range object is one of the more versatile objects in Excel. A range can be a single cell, a column, a row, a contiguous block of cells, or a non-contiguous range of cells. The main parent of a Range object is the Worksheet object. However, most of the objects in the Excel object model use the Range object. The Range property of the Worksheet object can be used to choose a certain range of cells using the Cell1 and Cell2 parameters. New to Excel 2007, the Ranges object holds a collection of...

The Open Method

This method opens a connection to the data source, and has the following syntax connection.Open ConnectionString, UserID, Password, Options The ConnectionString argument serves the same purpose as the ConnectionString property discussed in the previous section. ADO allows you to set this property in advance or pass it in at the time you open the connection. The UserID and Password arguments can be passed separately from the connection string if you wish. The Options argument is particularly...

Creating a UDF

Unlike manual operations, UDFs cannot be recorded you have to write them from scratch using a standard module in the VBE. If necessary, you can insert a standard module by right-clicking in the Project Explorer window and choosing Insert O Module. A simple example of a UDF is shown here Fahrenheit Centigrade 9 5 32 End Function Here, a function called Fahrenheit is created that converts degrees Centigrade to degrees Fahrenheit. In the worksheet, you could have column A containing degrees...

The VBProject Object

This object is the container for all the programming aspects of a workbook, including UserForms, standard modules, class modules, and the code behind each worksheet and the workbook itself. Each VBProject corresponds to one of the top-level items in the Project Explorer. A specific VBProject object can be located either by iterating through the VBE's VBProjects collection or through the VBProject property of a workbook. To find the VBProject that corresponds to the workbook Bookl.xlsm, the...

Chart Object Methods

Name Returns Parameters Description Activate Variant Makes the embedded chart the active chart Activate Variant Makes the embedded chart the active chart Brings the embedded chart to the front of all the other objects on the sheet. Changes Copies the Chart object into the clipboard as a picture. The Appearance parameter can be used to specify whether the picture is copied as it looks on the screen or when printed. The Format parameter can specify the type of picture that will be put into the...

RibbonX and VBA

As well as having the ability to create custom tabs and groups containing built-in controls, Microsoft has provided the ability to add many types of custom controls to the Ribbon, and to hook their actions and most of their attributes to VBA procedures and functions. This is done using a mechanism known as a callback. A callback means simply that, as part of a RibbonX definition, you provide the name of a procedure to run when the control is clicked, changed, and so on. It's exactly the same as...

Charts Collection Properties and Methods

The Charts collection has a few properties and methods besides the typical collection attributes. These are listed in the following table. Read-only. Returns a collection holding all the horizontal page breaks associated with the Charts collection Set Get whether the charts in the collection are visible. Also, you can set this to xlVeryHidden to prevent a user from making the charts in the collection visible Read-only. Returns a collection holding all the vertical page breaks associated with...

Using Names

One of the most useful features in Excel is the ability to create names. You can create a name by selecting the Formulas tab on the Ribbon and clicking the Name Manager button to display the Name Manager dialog box, shown in Figure 5-1. If the name refers to a range, you can create it by selecting the range, typing the name into the Name box at the left side of the Formula bar, and pressing Enter. However, in Excel, names can refer to more than just ranges. Mary ,, gt 2,3, t 3,4,5 rt, 5.5 5,6,7...

Application Methods

Activates an application specified by XlMSApplication. Opens the application if it is not open. Acts in a similar manner as the GetObject function in VBA Adds the array of strings specified by ListArray to Excel's custom lists. The ListArray may also be a cell range Calculates all the formulas in all open workbooks that have changed since the last calculation. Only applicable if using manual calculation Calculates all the formulas in all open workbooks. Forces recalculation of every formula in...

Constants Structures Handles and Classes

Most of the API functions include arguments that accept a limited set of predefined constants. For example, to get information about the operating system's capabilities, you can use the GetSystemMetrics function Declare Function GetSystemMetrics Lib user32 _ ByVal nIndex As Long As Long The value that you pass in the nIndex argument tells the function which metric you want to be given, and must be one of a specific set of constants that the function knows about. The applicable constants are...

Trusted Publishers

The Trusted Publishers category, shown in Figure 22-1, lists the digital certificates the user has chosen to trust. A digital certificate is a software signature that can be used to sign an Excel add-in or other type of application. The certificate ensures that the application actually originates from where it claims to, and that it has not been tampered with since it was signed. Digital certificates are also called digital signatures. Digital certificates must be obtained from a certification...

The Basics of MDX

Those of you who are familiar with SQL will have relatively little trouble picking up the basic concepts of MDX. As you look at the general syntax for an MDX statement, you will see the familiar SELECT and FROM clauses A member selection can be any combination of dimensions or members. These selections are given an axis designation. In MDX, a member selection can actually be placed in any one of up to 64 axes. To keep things simple, look at the most common axes columns and rows. When a member...

Comment Properties

Returns the name of the person who created the comment Read-only. Returns the comment box as a Shape object, allowing manipulation of the comment box Set Get whether the comment is visible all the time True or only when the user hovers over the cell containing the comment

Summary

In Excel 2007, Microsoft has enabled the Excel developer to use the Internet as an integral part of an application solution in the following ways Workbooks can be opened from and saved to web servers running the FrontPage Server Extensions. Excel can open HTML pages as though they were workbooks. Web Queries can be used to extract tables of data from web pages. The Internet Explorer object library can be automated to retrieve individual items of data from a web page, without the overhead of...

Programmatically Unzipping an Excel Container

The unzip procedure is little more than a series of simple steps that duplicate the manual act of copying files out of an Excel container and saving them into a destination folder. There are probably dozens of different methods and utilities that can be used to programmatically zip and unzip a compressed file. The procedures demonstrated here leverage the built-in file compression functionality within Windows XP. If you do not have Windows XP, you can use any one of dozens of compression...

Window Properties

Returns the cell in the window where the cursor is Read-only. Returns the currently selected chart in the window. If no chart is currently selected, nothing is returned Read-only. Returns the active pane in the window Read-only. Returns the active sheet in the window Read-only. Returns a view of the active sheet in the window Set Get whether the auto filter for date grouping is currently displayed in the specified window Set Get the caption that appears in the window Set Get whether...

Trapping Application Events

You can use a class module to trap application events. Most of these events are the same as the workbook events, but they apply to all open workbooks, not just the particular workbook that contains the event procedures. For example, in a workbook there is a BeforePrint event that is triggered when you start to print anything in that workbook. At the application level, there is a WorkbookBeforePrint event that is triggered when any open workbook starts to print. To see what application events...

Page Setup Properties

Set Get whether the header and footer for a given document are aligned with margins set in the page setup options Set Get whether worksheet items will be printed in black and white only. Not valid when parents are Chart objects Set Get the bottom margin of the page in points Set Get the text for the center part of the footer Read-only. Returns the picture for the center section of the footer Set Get the text for the center part of the header Read-only. Returns the picture for the center section...

Quick Access Toolbar

In versions of Excel prior to Excel 2007, you can attach macros to toolbar buttons. Because toolbars and menus have been replaced by the Ribbon in Excel 2007, this ability no longer exists, with the exception of the Quick Access Toolbar. The Quick Access Toolbar sits either above or below the Ribbon, and you can add any button from the Ribbon to it to give you direct access to the button. When you right-click a Ribbon button, you can choose Add to Quick Access Toolbar from the pop-up menu. The...

Current Region Property

If you have tables of data that are separated from surrounding data by at least one empty row and one empty column, you can select an individual table using the CurrentRegion property of any cell in the table. It is equivalent to the manual Ctrl keyboard shortcut or Ctrl A . In the Figure 4-7 worksheet, you could select the Bananas table by clicking the A9 cell and pressing Ctrl . The same result can be achieved with the following code, given that cell A9 has been named Bananas This property is...

Pivot Fields

The columns in the data source are referred to as fields. When the fields are used in a PivotTable, they become PivotField objects and belong to the PivotFields collection of the PivotTable object. The PivotFields collection contains all the fields in the data source and any calculated fields you have added, not just the fields that are visible in the PivotTable report. Calculated fields are discussed later in this section. You can add PivotFields to a report using two different techniques. You...

Property Properties

Parameters Indexl, Index2 , Index3 , Index4 . The Value of the Property can be an array of up to four indices. The IndexedValue can be used to read a single item in the returned array. Read-only. Returns the name of the property, and is also used to refer to a specific property. Read-only. If the value of the Property is an array, Numlndices returns the number of indices dimensions in the array. If not an array, it returns 0. Used to obtain a reference to the object returned by the Property, if...

Code Pane Properties

Returns the CodeModule that contains the code being viewed in the CodePane. Read-only. Returns whether the CodePane is set to show one procedure at a time, or a full-module view with separator lines between procedures. Read-only. Returns the number of lines visible in the CodePane. This and the TopLine property can be used to center a line in the CodePane window see following example . The CodeModule line number of the first line visible in the CodePane window. Read-only. Returns the...

Code Pane Methods

StartLine As Long, StartColumn As Long, EndLine As Long, EndColumn As Long StartLine As Long, StartColumn As Long, EndLine As Long, EndColumn As Long Used to retrieve the currently selected text. All of the arguments are passed ByRef and are modified within the procedure to return the selection. All arguments are required, but it is only required to pass arguments for those items you want to retrieve. For example, to get only the start line, you can use Dim lStart As Long lStart, 0,0,0 Used to...

Chart Object Properties

Returns the single cell range located under the lower-right corner of the ChartObject Read-only. Returns the actual chart associated with the ChartObject Set Get whether a macro associated with the ChartObject is capable of being triggered Set Get the height of embedded chart Read-only. Returns the position of the ChartObject among the parent collection Set Get the distance from the left edge of the ChartObject to the left edge of the parent sheet Set Get whether the ChartObject is...

Introduction

Excel made its debut on the Macintosh in 1985 and has never lost its position as the most popular spreadsheet application in the Mac environment. In 1987, Excel was ported to the PC, running under Windows. It took many years for Excel to overtake Lotus 1-2-3, which was one of the most successful software systems in the history of computing at that time. A number of spreadsheet applications enjoyed success prior to the release of the IBM PC in 1981. Among these were VisiCalc and Multiplan....

Hyperlink Object and the Hyperlinks Collection Example

This example creates a hyperlink-based Table of Contents worksheet .Range A1 .Value Table of .Hyperlinks.Add .Range A3 , .Hyperlinks.Add .Range A4 , 'Loop through the sheets in the workbook If oSht.Name lt gt oShtTOC.Name Then oShtTOC.Hyperlinks.Add oShtTOC.Cells iRow, 1 , , _

Pivot ItemList Object

Represents a list of Pivotltems associated with a particular cell in a PivotTable. You access the list through the PivotCell object. PivotItemLists are accessed either through the ColumnItems or RowItems properties of the PivotCell object. How many row and column items there are in the PivotItemList depend on the structure of the PivotTable. For example, cell D5 is in a PivotTable called WroxSalesl. In the row area to the left of cell D5 is the row heading OR Oregon . To the left of OR is...

Command BarButton Methods

Name Returns Parameters Description Copies a CommandBarButton to an existing command bar. Copies the face of a CommandBar Button to the Clipboard. Deletes the specified CommandBar Button from its collection. Set Temporary to True to delete the control for the current session only the application will display the control again in the next session. Runs the procedure or built-in command assigned to the specified CommandBarButton. For custom controls, use the OnAction property to specify the...

Workbook Events

The following workbook event procedures are available Private Sub Workbook_Activate Private Sub Workbook_AddinInstall Private Sub Workbook_AddinUninstall Private Sub Workbook_AfterXmlExport ByVal Map As XmlMap, ByVal Url As String, ByVal Result As XlXmlExportResult Private Sub Workbook_AfterXmlImport ByVal Map As XmlMap, ByVal IsRefreshAs Boolean, ByVal Result As XlXmlImportResult Private Sub Workbook_BeforeClose Cancel As Boolean Private Sub Workbook_BeforePrint Cancel As Boolean Private Sub...

Range Methods

Adds the text specified by the parameter to the cell specified in the range. Must be a single cell range Action As XlFilter Action, Criteria Range , Copies or filters the data in the current range. The Action parameter specifies whether a copy or filter is to take place. CriteriaRange optionally specifies the range containing the criteria. CopyToRange specifies the range that the filtered data will be copied to if Action is xlFilterCopy Names , Ignore Relative Absolute , UseRowColumn Names ,...

Connections Methods

Add Workbook Name As String, Returns a Connection Description As String, WorkbookConnection ConnectionString object, via a connection CommandText As Variant, connection to an WorkbookConnection object, via an Office Data Connection .odc file, a Offline Cube file .cub , or any other file that defines an external data source This example adds a connection to a local cube file and then creates a PivotTable. 'Add a connection to the local cube file C MyCustomCube.cub 'Create a Pivot Cache and Pivot...

Chart Object Object and the Chart Objects Collection

The ChartObjects collection holds all of the embedded Chart objects in a worksheet, chart sheet, or dialog sheet. This collection does not include the actual chart sheets themselves. Chart sheets can be accessed through the Charts collection. Each Chart in the ChartObjects collection is accessed through the ChartObject object. The ChartObject acts as a wrapper for the embedded chart itself. The Chart property of the ChartObject is used to access the actual chart. The ChartObject object also...

Trusted Locations

Trusted locations are a new concept in Office 2007. A trusted location is a folder on your computer or on your network that has been designated as containing only safe documents. Excel workbooks, add-ins, and other Office documents that are placed in a trusted location will not be subject to any security restrictions. The Trusted Locations category is shown in Figure 22-2. The Trusted Locations list shows two types of trusted locations. User Locations are trusted locations that are either added...

Color Scale Object Example

This example adds a three-color scale formatting rule to a specified range Set oColorScale 'Set the minimum threshold to the lowest value in the range 'Set the color for the minimum threshold xlConditionValueLowestValue 7039480 'Set the midpoint threshold to the value in a specific cell cell F10 in this case 'Set the for the midpoint threshold xlConditionValueNumber oColorScale.ColorScaleCriteria 2 .Value F 10 49407 'Set the maximum threshold to the lowest value in the range 'Set the color for...

Color ScaleCriterion Properties

Returns a FormatColor object, which defines the color assigned to the specified color scale threshold. Read-only Returns a value that represents the threshold for the criteria. For two-color scales, the index values will be 1 for the minimum threshold and 2 for the maximum threshold. For three-color scales, the values will be 1 for the minimum threshold, 2 for the midpoint, and 3 for the maximum. Read-only Specifies how the threshold values for a color scale conditional format are determined...

Special Names

Excel uses some names internally to track certain features. When you apply a print range to a worksheet, Excel gives that range the name Print_Area as a local name. If you set print titles, Excel creates the local name Print_Titles. If you select the Data tab on the Ribbon and click the Advanced button in the Sort amp Filter chunk to extract data from a list to a new range, Excel creates the local names Criteria and Extract. In older versions of Excel, the name Database was used to name the...

Programmatically Zipping an Excel Container

In the ZipPackage procedure demonstrated here, you are creating an empty .zip file and then filling it with the contents of a source directory. Notice that you are using the Sleep API function here. This lets you pause Excel for a specified number of milliseconds. Pausing Excel allows each file to be completely compressed and saved before moving on the next file. In this procedure, you are making Excel sleep for 500 milliseconds each time you copy a file to the .zip container Public Declare Sub...

Publish Objects Methods

Parameters SourceType As XlSourceType, Filename As String, Sheet , Source , HtmlType , DivID , Title . Adds a PublishObject to the collection Method. Deletes the PublishObject objects from the collection Method. Publishes all the items associated with the PublishObject objects to a web page

The Trust Center

The Trust Center is the new user interface for all settings related to document security in Office 2007. Many of these settings have been with Office for a long time and were simply relocated to the Trust Center user interface. There are also some new and enhanced document security features in Office 2007. Many of the Trust Center settings are specific to Excel, but some settings will affect all Office applications. The Trust Center user interface is buried a bit deeply within the Excel user...

Adding Menu Items to the VBE

The VBE uses the CommandBar object model rather than the Ribbon, so the procedure for adding menus to the VBE is almost the same as that documented in Chapter 15 for creating popup toolbars. There is one major difference, which is how to run your routine when the menu item is clicked. When adding menu items to Excel's popup toolbars, you set the OnAction property of the CommandBarButton to the name of the procedure to run. In the VBE, the CommandBarButton still has an OnAction property, but it...

Sending Data to Excel

By far the best way to get numbers, dates, Booleans, and strings into Excel cells is to do so in their native format. Hence, the following code works perfectly, regardless of locale As Date, dNumber As Double, bBool As Boolean, _ There is a boundary layer between VBA and Excel. When VBA passes a variable through the boundary, Excel does its best to interpret it according to its own rules. If the VBA and Excel data types are mutually compatible, the variable passes straight through unhindered....

Late Binding

The following code creates an entry in the Outlook calendar. The code uses the late binding technique 'Example of Outlook automation using late binding 'Creates an appointment in Outlook Dim olApp As Object 'Reference to Outlook Dim olAppointment As Object 'Reference to Outlook Appointment Dim olNameSpace As Object 'Reference to Outlook NameSpace Dim olFolder As Object 'Dummy reference to initialize Outlook Const olAppointmentltem 1 'Outlook intrinsic constants not available Const olFolderlnbox...

Point Object and the Points Collection

The Points collection holds all of the data points of a particular series of a chart. In fact, a chart Chart object can have many chart groups ChartGroups ChartGroup that can contain many series SeriesCollection Series , which in turn can contain many points Points Point . A Point object describes the particular point of a series on a chart. The parent of the Points collection is the Series object. The Points collection has no properties and methods outside the typical collection attributes...

Command Bars Collection Properties

The Application, Count, Creator, and Parent properties are defined at the beginning of this appendix. Read-only. Returns the CommandBarControl object whose OnAction property is set to the running procedure. If the running procedure was not initiated by a command bar control, this property returns Nothing. Read-only. Returns a CommandBar object that represents the active menu bar in the container application. This almost always returns the application's Worksheet menu bar. Set Get whether...

Chart Properties

Set Get whether Excel will stretch a 3D chart to match its 2D chart equivalent. RightAngleAxes must be true Read-only. Returns a Walls object allowing users to format the back wall of a 3D chart Set Get the basic shape used in 3D bar or column charts for example, box, cylinder, pyramid, and so on Read-only. Returns the part of a chart containing axes, titles, legends, and formatting properties Set Get a number from 1-48, indicating the chart style for the chart Read-only. Returns an object...

Pivot Table Methods

AddDataField Pivot Field As Object, Adds a data field to a report. field on the server, Caption is the label used to identify this data field, and Function is the function performed in the added data field Adds row, column, and page fields to a PivotTable report. RowFields, ColumnFields, and PageFields can hold a single string field name or an array of string field names. Set AddToTable to True to add the fields to the report. Set AddToTable to False to replace the fields in the report Returns...

The VBComponent Object

The UserForms, standard modules, class modules, and code modules behind the worksheets and workbook are all VBComponent objects. Each VBComponent object corresponds to one of the lower-level items in the Project Explorer tree. A specific VBComponent can be located through the VBComponents collection of a VBProject. Hence, to find the VBComponent that represents the UserForml form in Bookl.xls, code like this can be used Set oVBC The name of the VBComponent that contains the code behind the...

Format Condition Properties

Returns the range that is affected by the formatting rule Read-only. Returns a collection holding all the individual border attributes for the formatting condition Read-only. Returns an object containing Font options for the formatting condition Set Get the Boolean value specifying if the entire Excel table row should be formatted. The default value is False Read-only. Returns the value that the cells must contain or an expression or formula evaluating to True False. If the formula...

Programming with the Windows API

Visual Basic for Applications is a high-level language that provides you with a rich, powerful, yet quite simple set of functionality for controlling the Office suite of products, as well as many other applications. You are insulated some would say protected from the mundane minutiae of Windows programming that, say, a C programmer has to contend with. The price you pay for this protection is an inability to investigate and control many elements of the Windows platform. You can, for example,...

Displaying a User Form

Excel 2010 Create Vba Userform

To load a UserForm called UserForm1 into memory, without making it visible, you use the Load statement You can remove UserForm1 from memory using the Unload statement To make UserForm1 visible, use the Show method of the UserForm object If you show a UserForm that has not been loaded, it will be automatically loaded. You can use the Hide method to remove a UserForm from the screen without removing it from memory Figure 13-1 shows a simple UserForm in action that will be developed over the...

Primer in Excel VBA

This chapter is intended for those who are not familiar with Excel and the Excel macro recorder, or who are inexperienced with programming using the Visual Basic language. If you are already comfortable with navigating around the features provided by Excel, have used the macro recorder, and have a working knowledge of Visual Basic and the Visual Basic Editor, you might want to skip straight to Chapter 2. If this is not the case, this chapter has been designed to provide you with the information...

Axis Object and the Axes Collection

The Axes collection represents all of the Axes in an Excel chart. Each Axis object is equivalent to an axis in an Excel chart for example, X axis, Y axis, and so on . The parent of the Axes collection is the Chart object. Unlike most other collections, the Item method of the Axes collection has two parameters Type and AxisGroup. Use one of the xlAxisType constants for the Type parameter xlValue, xlCategory, or xlSeriesAxis . The optional second parameter, AxisGroup, can take one of the...

Page Break Properties

Returns whether the vertical page break is full Extent screen or only for the print area Returns whether the vertical page break is full Extent screen or only for the print area Set Get the cell where the vertical page break is located. The left edge of the cell is the location of the page break Set Get whether the page break is automatic or manually set

Creating a Toolbar

In previous versions of Office, you could manually create a simple toolbar with buttons and dropdowns. Now you can only do this using VBA code. The more complex controls, such as those of type msoControlEdit, msoControlDropdown, and msoControlComboBox, have always required VBA code. As with the new menu created earlier, Excel 2007 displays the new toolbar in the Add-Ins tab of the Ribbon. The toolbar in Figure 15-8 contains three controls. The first is of type msoControlButton and displays the...

Pivot Item Properties

Set Get the label text associated with the item Read-only. Parameters Index . Returns an object or collection containing a single PivotTable item PivotItem or group of PivotTable items PivotItems associated with the item Read-only. Returns a range containing the data or items in the item Set Get whether the PivotTable item is drilled down Set Get the formula associated with item, if any Read-only. Returns whether the item that was calculated is a data item Read-only. Returns the cell range...

Xml Map Methods

Name Returns Parameters Description Delete Deletes the current XmlMap object Export XlXmlExportResult Url As String, Exports the current XmlMap object Exports the current XmlMap object as XML that can be persisted to a file See Chapter 12 for examples of how to use the XMLMap object.

Maintaining a Data List

The code you have developed can now be extended to maintain a data list without too much extra effort. However, the last example takes a different approach. This time you will build all the code into frmPersonal, apart from the code behind the command button in the worksheet that shows the UserForm. The code behind this button now becomes the following It is really much easier to maintain a data list in a proper database application, such as Microsoft Access, but it can be done in Excel without...

Using ADO in Microsoft Excel Applications

This section combines the understanding of Excel programming that you've gained from previous chapters with the SQL and ADO techniques discussed so far in this chapter. Excel applications frequently require data from outside sources. The most common of these sources are Access and SQL Server databases. However, I've created applications that required source data from mainframe text file dumps and even Excel workbooks. As you'll see, ADO makes acquiring data...

Connector Format Properties

Returns whether the beginning of the connector has a shape attached. Use with BeginConnectedShape Read-only. Returns the shape that is connected to the beginning of the connector. Use with BeginConnected Read-only. Returns which connection site connection spot on the shape that the beginning of the connector is connected to. Use with BeginConnected Read-only. Returns whether the end of the connector has a shape attached. Use with BeginConnectedShape Read-only. Returns the shape that...

Auto Filter Object

When you AutoFilter a range that is not in a table, Excel uses an AutoFilter object whose parent is the Worksheet object. There can only be one AutoFilter object for each worksheet. If you AutoFilter a second range in a worksheet, all the settings for the first AutoFilter are lost. When you AutoFilter a table, the parent of the AutoFilter object is the ListObject object. Because you can have multiple tables in a worksheet, it is possible to have an AutoFilter operating simultaneously in each...

Traversing and Modifying XML Files with DOM and XPath

You'll remember from the start of this chapter that the construct of XML ensures that a parent child hierarchy exists between all elements and attributes within an XML document. This gives XML documents an inherent logical structure that allows each construct in the document to be parsed into nodes. XPath is a language that allows you to locate the component parts in an XML document by specifying a path to each node in the tree. With XPath, you can build an expression called a location path. A...

Plot Area Object Example

This example uses the PlotArea object to make all the embedded charts in the workbook not chart sheets have the same size and position plot area, regardless of the formatting of the axes for example, different fonts and number scales Sub MakeChartAreasSameSizeAsFirst Dim oCht As Chart, oPA As PlotArea Dim dWidth As Double, dHeight As Double Dim dTop As Double, dLeft As Double 'Get the dimensions of the inside of the plot area of the first chart With dWidth .InsideWidth dHeight .InsideHeight...

The Query Table and List Object

When you use the Get External Data feature to create Web Queries or retrieve tabular data, you are creating a QueryTable to manage that data. This QueryTable can exist alone, or it can be associated with a ListObject the ListObject is also covered in Chapter 6 . Retrieving data using Web Queries or text files from the user interface will create a standalone QueryTable. Retrieving data from relational databases like Access or SQL Server will create a ListObject whose data source is a QueryTable....

The Show DataForm Sub Procedure

Using ActiveSheet.ShowDataForm means exposing yourself to one of the most dangerous of Excel's international issues. ShowDataForm is the VBA equivalent of the pre-2007 Data O Form menu item which is not available by default in the Excel 2007 Ribbon, but can be added to the QAT by selecting the Form command from the All Commands list . It displays a standard dialog that allows the user to enter and change data in an Excel list or database. When run from Excel, the dates and numbers are displayed...

The CForm Resizer Class

By encapsulating all the resize code in a separate class module, any UserForm can be made resizable by adding just six lines of code to instantiate and call into the class, and setting the resize behavior for each control in its Tag property. The CFormResizer class provides the following functionality Sets the form to be resizable. Sets the initial size and position of the form, if it has been shown before. Resizes and repositions all the controls on the form, according to their Tag resizing...

DynamicMenu

The dynamicMenu is a unique control in RibbonX, because it is the only one whose content's structure can be changed at run time. The dropDown, comboBox, and gallery are essentially flat lists the dynamicMenu can contain a full control hierarchy of both custom and built-in controls including other dynamicMenus. This control was created to satisfy the specific requirement for dynamic content that changes radically as workbooks are opened, closed, and changed. Imagine a workbook containing...

CustomXMLNode Properties

The Application, Creator, and Parent properties are defined at the beginning of this appendix. Read-only. Returns a CustomXMLNodes collection representing the attributes of the current element in the current node. Read-only. Returns the name of the specified node without the namespace prefix. Read-only. Returns a collection of nodes containing all of the child elements of specified node. Read-only. Returns a CustomXMLNode object corresponding to the first child element of the current node....

Data Label Object and the Data Labels Collection

The DataLabels collection holds all the labels for individual points or trendlines in a data series. Each series has only one DataLabels collection. The parent of the DataLabels collection is the Series object. Each DataLabel object represents a single data label for a trendline or a point. The DataLabels collection is used with the HasDataLabels property of the parent Series object. The DataLabels collection has a few properties and methods besides the typical collection attributes. They are...