Caller

The Caller property of the Application object returns a reference to the object that called or executed a macro procedure. It had a wide range of uses in Excel 5 and Excel 95, where it was used with menus and controls on dialog sheets. From Excel 97 onward, command bars and ActiveX controls on user forms have replaced menus and controls on dialog sheets, and the Ribbon and Quick Access Menu have now replaced command bars. The Caller property does not apply to these new features. Caller still...

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

Default WebOptions Properties

Set Get whether Portable Network Graphics Format PNG is allowed as an output format. PNG is a file format for the lossless, portable, well-compressed storage of images Set Get whether web pages are always saved in the default encoding Set Get whether Office is the default web editor for Office-created pages Set Get whether Office components are downloaded to the end user's machine when viewing Excel files in a web browser Set Get the type of encoding to save a document as Read-only. Returns...

Direct Reference to Ranges

When you define a UDF, it is possible to directly refer to worksheet ranges rather than through the input parameters of the UDF. This is illustrated in the following version of the lnvoiceAmount() function Function InvoiceAmount2(Product, Volume) 'Create object variable referring to table in worksheet Set Table Price WorksheetFunction.VLookup(Product, Table, 2) 'Find discount volume threshold DiscountVolume WorksheetFunction.VLookup(Product, Table, 3) 'Apply discount if volume above threshold...

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

Controls at All Levels

Figure 15-5 and the following code take the previous procedure to greater levels of detail. All controls are examined to see what controls are contained within them. Where possible, the contained controls are listed. Some controls, such as those containing graphics, can't be listed in greater detail. The information on sub-controls is indented across the worksheet. The code is capable of reporting to as many levels as there are, but Excel 2007 does not have controls beyond the fourth level....

Pasting Text

When pasting text from other applications into Excel, it is parsed according to the WRS. There is no way to tell Excel the number and date formats and language to recognize. The only workaround is to use a DataObject to retrieve the text from the clipboard, parse it yourself in VBA, then write the result to the sheet. For clarity, the following example assumes that the clipboard contains a single U.S.-formatted number and that it should be enhanced to check for U.S.-formatted dates as well...

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

Command Bars

The CommandBars collection is an object contained in the Office Object Model, documented in Appendix C. It contains all the menus, toolbars, and shortcut popup menus that are already built into Excel and the other Office applications, as well as any of those objects that you create yourself. You access command bars through the CommandBars property of the Application object. Command bars were first introduced into Office in Office 97. Excel 5 and 95 supported menu bars and toolbars as separate...

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

Toolbars Menu Bars and Popups

Figure 15-1 shows the standard Worksheet menu bar at the top of the Excel window in Excel 2003. The Worksheet menu bar contains menus, such as File and Edit. When you click a menu, you see another list containing commands and menus Cut and Copy are examples of commands in the Edit menu. Clear is an example of a menu contained within the Edit menu. Figure 15-2 shows the Standard toolbar in Excel 2003. I jjjjj st, e - n ii a o - J Toolbars contain controls that can be clicked to execute Excel...

Data Form

Excel has a built-in form that you can use to view, find, and edit data in a list. The feature is not available on the Ribbon, so you need to add it to the Quick Access menu if you want to use it through the user interface. Right-click the Quick Access menu and choose Customize Quick Access Toolbar to open the dialog box shown in Figure 6-12. Select the Customization button, if necessary, and from the drop-down above the left list box, select Commands Not in the Ribbon. Find the Form command...

Open an XML Document Directly into a List

Through the user interface, you can open an XML document directly from Excel, automatically creating an XML list that is mapped to the elements in the source XML document. This functionality can be replicated via code by using the OpenXML method of the Workbooks collection. This method returns a workbook object with the XML data mapped to your spreadsheet strTargetFile ThisWorkbook.Path & EmployeeSales.xml 'Use the OpenXML method to open the target file Workbooks.OpenXML Filename...

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

Class Modules

Class modules are used in VBA to create your own customized objects. Most VBA users will never have to create their own objects because Excel already provides all of the objects they need. However, there are occasions when class modules can be very useful. You can use them to Respond to application events you can write code that is executed whenever any open workbook is saved or printed, for example Respond to embedded chart events Set up a single event procedure that can be used by a number of...

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

Picture Format Properties

Set Get the brightness of the parent shape 0 to 1, where 1 is the brightest Set Get the type of color setting of the parent shape Set Get the contrast of the parent shape 0 to 1, where 1 is the greatest contrast Set Get how much is cropped off the bottom Set Get how much is cropped off the left Set Get how much is cropped off the right Set Get how much is cropped off the top Set Get the color used for transparency Set Get whether transparent colors appear transparent

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

Comment Object and the Comments Collection Example

This example removes the user name added by Excel at the start of the comment and formats the comment to make it more readable Dim oComment As Comment, i As Integer 'Loop through all the comments in the sheet For Each oComment In ActiveSheet.Comments 'Using the text of the comment With oComment.Shape.TextFrame.Characters 'Find and remove the user name inserted by Excel i InStr 1, .Text, amp vbLf If i gt 0 Then .Name Arial .Size 10 .Bold False End With End With 'Make the text frame auto-fit...

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

The Properties Window

The Properties window shows you the properties that can be changed at design time for the currently active object in the Project Explorer window. For example, if you click Sheet1 in the Project Explorer, the Sheet1 properties are displayed in the Properties window, as shown in Figure 1-7. The ScrollArea property has been set to A1 D10, to restrict users to that area of the worksheet. You can get to the help screen associated with any property very easily. Just select the property, such as the...

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

Visible Property

You can hide items by setting their Visible property to False. Say you are working with the grouped dates from the last exercise, and you want to see only Jan 2006 and Jan 2007, as shown in Figure 7-12. Sub CompareMonths Dim pvt As PivotTable Dim pvi As Pivotltem Dim sMonth As String 'Specify month to be visible sMonth Jan Set pvt ActiveSheet.PivotTables l 'Hide all years except 2006 amp 2007 For Each pvi In If pvi.Name lt gt 2006 And pvi.Name lt gt 2007 Then 'Make sure specified month is...

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

Advanced Filter

A powerful way to filter data from a list is to use Advanced Filter. You can filter the list in place, like AutoFilter, or you can extract it to a different location. The extract location can be in the same worksheet, in another worksheet in the same workbook, or in another open workbook. In the following example, the data for NSW and VIC has been extracted for the first quarter of 2007. The data has been copied from the workbook containing the data list to a new workbook. The source data can...

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

Command BarPopup Properties

The Application, Creator, and Parent properties are defined at the beginning of this appendix. Set Get whether the specified CommandBarPopup appears at the beginning of a group of controls on the command bar. Read-only. Returns True if the specified command bar or command bar control is a built-in command bar or control of the container application. Returns False if it's a custom command bar or control, or if it's a built-in control whose OnAction property has been set. Set Get the caption text...

Pivot Items

Each PivotField object has a Pivotltems collection associated with it. You can access the Pivotltems using the Pivotltems method of the PivotField object. It is a bit peculiar that this is a method and not a property, and is in contrast to the Hiddenltems property and Visibleltems property of the PivotField object that return subsets of the Pivotltems collection. The Pivotltems collection contains the unique values in a field. For example, the Product field in the source data has four unique...

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

Pivot Tables Collection

You can use another method to create a PivotTable from a PivotCache, using the Add method of the PivotTables collection. If you have already created a PivotCache in your workbook and you want to create a second PivotTable, you can use the following code Dim pvc As PivotCache Dim pvt As PivotTable Set pvc ActiveWorkbook.PivotCaches l 'Add new PivotTable to PivotTables collection There is no particular advantage to using this method compared with the CreatePivotTable method. It's just another...

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

Format Condition Methods

Type As XlFormat ConditionType, Operator , Formulal , Formula2 Modifies the format ting condition. Since all the properties are read-only, this is the only way to modify the format condition Sets the range for which the formatting rule will be applied FormatCondition Object and the FormatConditions Collection Example Name Returns Parameters Description ting rule so that it is evaluated before all other rules on the worksheet Sets the priority value for the formatting rule so that it is...

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

Office UI Language Settings

The Office User Interface language can be selected by using the Microsoft Office Language Settings applet, installed with Office 2007, and it defines The text displayed on Excel's menus and dialog boxes The text for the standard buttons on Excel's message boxes The text for Excel's built-in worksheet functions The text displayed in Excel's cells for Boolean values The text for Boolean values recognized by the Text Import Wizard, the VBA OpenText method, and when typing directly into Excel The...

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

Cells Property

You can use the Cells property of the Application, Worksheet, or Range objects to refer to the Range object containing all the cells in a Worksheet object or Range object. The following two lines of code each refer to a Range object that contains all the cells in the active worksheet Because the Cells property of the Application object is a member of lt globals gt , you can also refer to the Range object containing all the cells on the active worksheet as follows You can use the Cells property...

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 Object and Code Panes Collection

A CodePane is a view of a CodeModule, providing you with access to the interaction layer between the developer and the code being edited. Most VBE Add-Ins use this layer to identify the line in which CodePane is currently being edited and then modify the code at the line, using CodeModule's methods and properties. Note that there can be more than one CodePane for a CodeModule for example, by splitting a code window into two panes with the horizontal splitter bar . The following tables define...

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

Headers and Footers

A common need in Excel is to print information in the page header or footer that either comes from the worksheet cells or is not available in the standard header and footer options. You might want to insert a company name that is part of the data in the worksheet and display the full path to the workbook file. The filename is available as an option in headers and footers in Excel 2007. It can be inserted using the code amp F, as shown in the following code. Data can be accessed from worksheet...

Watch Object and the Watches Collection Object

The Watch object represents one Watch in the Watch window found on the Formulas tab in the Excel interface . Each Watch can be a cell or cell range you need to keep track of as other data on the worksheet changes. A Watch object is an auditing tool similar to the watches you can create in the VBE. Watches do just that They keep track of a cell or cell range, allowing you to study changes to those cells when other data on the worksheet changes. The Watches collection contains all the Watch...

Sorting a Table

You can sort the data in the table in the same way as you sort a range, or by clicking one of the dropdowns beside the field names and selecting one of the sort options. If you record a sort based on the Name field using the drop-down beside the field name, you will get code like the following _ SortOn SortOnValues, Order _ xlAscending, DataOption xlSortNormal With .SetRange Range Table1 All .Header xlYes .MatchCase False .Orientation xlTopToBottom .SortMethod xlPinYin .Apply End With The code...

Resize Property

You can use the Resize property of the Range object to refer to a range with the same top left-hand corner as the original range, but with a different number of rows and columns. The following refers to D10 E10 Resize is useful when you want to extend or reduce a range by a row or column. For example, if you have a data list, which has been given the name Database, and you have just added another row at the bottom, you need to redefine the name to include the extra row. The following code...

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

OLEObject Methods

Sets the focus and activates the OLE object Brings the OLE object to the front of all the other objects Copies the OLE object into the clipboard Appearance As Xl Picture Appearance, Format As XlCopy Picture Format Copies the OLE 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 clipboard Cuts the OLE object...

Name Properties

Set Get the category of the Name in the language used to create the macro. Valid only if the Name is a custom function or command Set Get the category of the Name in the language of the end user. Valid only if the Name is a custom function or command Set Get any comments associated with a name Read-only. Returns the spot where Name is located in the Names collection Set Get the specified Name object as a workbook parameter Set Get if the Name refers to a command, a function, or just a range Set...

The Open Text Function

Workbooks.OpenText is the VBA equivalent of opening a text file in Excel by using Office Menu O Open. It opens the text file, parses it to identify numbers, dates, Booleans, and strings, and stores the results in worksheet cells. Of relevance to this chapter is the method Excel uses to parse the data file and how it has changed over the past few versions . In Excel 5, the text file was parsed according to your Windows Regional Settings when opened from the user interface, but according to U.S....

The Fields Collection

The Fields collection contains the values, and information about those values, from the current record in a Recordset object. In Excel, the Fields collection is most commonly used to return the column names of each field in the recordset, prior to accessing the contents of the recordset using the CopyFromRecordset method of the Range object. The following example demonstrates how to read the field names from the Fields collection of a Recordset object .Offset 0, lOffset .Value objField.Name...

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

Text EffectFormat Properties

Set Get the alignment of the WordArt Set Get whether the WordArt is italic Set Get the font used in the WordArt Set Get the font size in the WordArt Set Get whether the characters are kerned in the Set Get whether both the uppercase and lowercase characters are the same height Set Get the effect associated with the WordArt Set Get whether the WordArt has been rotated by 90 degrees Set Get the spacing ratio between characters Toggles the text from vertical to horizontal and back

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

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

Identifying the Office UI Language Settings

The first step in creating a multilingual application is to identify the user's settings. You can identify the language chosen in Windows Regional Settings by using Application.International xlCountrySetting , which returns a number that corresponds approximately to the country codes used by the telephone system 1 is the USA, 44 is the UK, 47 is Norway, and so forth . You can also use to retrieve the user interface language using the same numbering system. This method has worked well in...