The Excel Object Model

The Visual Basic for Applications programming language is common across all the Microsoft Office applications. In addition to Excel, you can use VBA in Word, Access, PowerPoint, and Outlook. Once you learn it, you can apply it to any of these. However, to work with an application, you need to learn about the objects it contains. In Word, you deal with documents, paragraphs, and words. In Access, you deal with databases, recordsets, and fields. In Excel, you deal with workbooks, worksheets, and...

Top Properties

Returns the range that is affected by the formatting rule Read-only. Returns a collection that specifies the cell borders for the formatting condition Set Get the scope of data to be evaluated in a PivotTable report. Use the xlCalcFor constant Read-only. Specifies the font formatting attributes for the conditional formatting rule Set Get the Boolean value specifying if the entire Excel table row should be formatted. The default value is False Read-only. Specifies the Interior...

Reading Data from Excel

When reading a cell's value, using its Value property, the data type that Excel provides to VBA is determined by a combination of the cell's value and its formatting. For example, the number 3000 could reach VBA as a Double, a Currency, or a Date (March 18, 1908). The only international issue of concern here is if the cell's value is read directly into a string variable the conversion will then be done implicitly, and you may not get what you expect (particularly if the cell contains a Boolean...

Forms Controls

Figure 10-3 shows a Form control that is being used to select a product name to be entered in column D. The control appears over any cell in column D that you double-click. When you select the product, the product name is entered in the cell behind the control, the price of the product is entered in column F on the same row, and the control disappears. If you hover your cursor over the Form button that creates the control shown in Figure 10-3, the ScreenTip that pops up describes this control...

Excel and the Internet

Until a few years ago, a typical Excel-based application was almost entirely contained within Excel itself the only external interaction would be with the user, from whom you obtained data and to whom you presented results. If you needed to store data, you'd use separate workbooks and try to mimic a relational database as best you could. As data access technologies developed, from ODBC drivers through DAO to the current versions of ADO (documented in Chapter 20), it became more commonplace to...

Excel VBA Programmers Reference

This book is aimed squarely at Excel users who want to harness the power of the VBA language in their Excel applications. At all times, the VBA language is presented in the context of Excel, not just as a general application programming language. The pages that follow have been loosely divided into three sections Working with Specific Objects (Chapters 2-27) Object Model References (Appendices A-C) The Primer has been written for those who are new to VBA programming and the Excel object model....

Interacting with Excel

VBA and Excel are two different programs that have had very different upbringings. VBA speaks American. Excel also speaks American. However, Excel can also speak in its users' language if they have the appropriate Windows settings and Office language pack installed. On the other hand, VBA knows only a little about Windows settings, and even less about Office 2007 language packs. So, either you can do some awkward coding to teach VBA how to speak to Excel in the user's language, or you can just...

Questions

Seems like there are always some, eh From the previous edition of this book, we received hundreds of questions. We have tried to respond to every one of them as best as possible. What we ask is that you give it your best shot to understand the problem based on the explanations in the book. If the book fails you, then you can either e-mail Wrox (support wrox.com) or us personally (greenj bigpond.net.au, RobBovey AppsPro.com, Stephen oaltd.co.uk). You can also ask questions on the vba_excel list...

Using the Macro Recorder

Excel's macro recorder operates very much like the recorder that stores the greeting on your telephone answering machine. To record a greeting, you first prepare yourself by rehearsing the greeting to ensure that it says what you want. Then you switch on the recorder and deliver the greeting. When you have finished, you switch off the recorder. You now have a recording that automatically plays when you leave a call unanswered. Recording an Excel macro is very similar. You first rehearse the...

Creating a Pivot Table Report

PivotTables can accept input data from a spreadsheet, or from an external source such as an Access database. When using Excel data, the data should be structured as a data list, as explained at the beginning of Chapter 6, although it is also possible to use data from another PivotTable or from multiple consolidation ranges. The columns of the list are fields and the rows are records, apart from the top row that defines the names of the fields. Take the Table in Figure 7-1, containing data for...

Variable Type

Computers store different types of data in different ways. The way a number is stored is quite different from the way text, or a character string, is stored. Different categories of numbers are also stored in different ways. An integer (a whole number with no decimals) is stored differently from a number with decimals. Most computer languages require that you declare the type of data to be stored in a variable. VBA does not require this, but your code will be more efficient if you do declare...

VBProject Properties

For ActiveX DLLs only, gets or sets the name of the DLL file to compile the project into. For ActiveX DLLs only, the description of the DLL as it will appear in the Tools O References list. Read-only. For workbook projects, returns the full name of the workbook. For ActiveX DLL projects, returns the name of the source code version of the project *.vba. If the file has not been saved, a run-time error occurs if you try to read this property. Identifies the default help-file context ID for the...

Chart Events

The following chart event procedures are available in the code module for each chart object Private Sub Chart_Activate() Private Sub Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Argl As Long, ByVal Arg2 As Long, Cancel As Boolean) Private Sub Chart_BeforeRightClick(Cancel As Boolean) Private Sub Chart_Calculate() Private Sub Chart_Deactivate() Private Sub Chart_MouseDown(ByVal Button As XlMouseButton, ByVal Shift As Long, ByVal x As Long, ByVal yAs Long) Private Sub...

Globals

Many of the Application object's methods and properties are also members of < globals> , which can be found at the top of the list of classes in the Object Browser, as shown in Figure 2-1. If a property or method is in < globals> , you can refer to that property or method without a preceding reference to an object. For example, the following two references are equivalent However, you do need to be careful. It is easy to assume that frequently used Application object properties, such as...

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

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

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

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

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

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

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

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

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

Weekly Budget Template

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

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

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

Range Property

You can use the Range property of the Application object to refer to a Range object on the active worksheet. The following example refers to a Range object that is the B2 cell on the currently active worksheet Note that you can't test code examples like this one as they are presented. However, as long as you are referring to a range on the active worksheet, these examples can be tested by the Immediate window of the VBE, as follows It is important to note that the preceding reference to a Range...

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

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

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

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

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

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

Identifying the Users Regional Settings and Windows Language

Everything you need to know about your user's Windows Regional Settings and Windows language version is found in the Application.International property. The online help lists all of the items that can be accessed, though you are unlikely to use more than a few of them. The most notable are XlCountryCode The language version of Excel or of the currently active Office language XlCountrySetting The Windows regional settings location XlDateOrder The choice of month-day-year, day-month-year, or...

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

Xml NameSpace Object and the XMLName SpacesCollection

The XMLNameSpace object represents an XML namespace that has been added to a workbook. The XMLNameSpaces collection contains all of the XMLNameSpace objects within a workbook. Along with the common collection attributes, the XMLNameSpaces collection contains a Value property that returns the actual namespace name. The XMLNameSpaces collection only has one method. This method is the InstallManifest, which installs a specified XML expansion pack a collection of files that add custom displays and...

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

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

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

Memory Engram

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

Importing Data into an Existing XML

As you may have guessed, the Import method imports data from an XML file into an XML list or cells that have been mapped to a particular XML map. This method is particularly useful when building automated XML-based reporting processes where data is programmatically imported from XML documents in shared locations. In the example shown here, the code imports data from the EmployeeSales.xml file to the specified XML map _ ThisWorkbook.Path amp EmployeeSales.xml The Workbook object provides a few...

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

Code Module Methods

Name Returns Parameters Description AddFromFile FileName As String Reads code from a text file and adds it to the end of the code module. It does not check if the names of procedures read from a file already exist in the module. AddFromString String As String Adds code from a string to the end CreateEvent Long EventName As String, Creates an empty event procedure Proc ObjectName As String in a module, filling in the event parameters for you. Cannot be used on standard modules, as they do not...

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

Connector Format Object Example

This example formats all fully connected connectors as curved lines Sub FormatConnectors Dim oShp As Shape Dim oCF As ConnectorFormat 'Loop through all the Shapes in the sheet For Each oShp In ActiveSheet.Shapes 'Is it a Connector If oShp.Connector Then 'Yes, so get the ConnectorFormat object Set oCF oShp.ConnectorFormat 'If the connector is connected at both ends, 'make it a curved line. With oCF If .BeginConnected And .EndConnected Then .Type msoConnectorCurve End If End With End If Next End...