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

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

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

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

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

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

Embedded Charts

Excel 2007 Userforms Charts

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

Object Variables

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

Watch Object Example

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

Range Object and the Ranges Collection Object

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

Creating a UDF

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

The VBProject Object

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

Chart Object Methods

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

RibbonX and VBA

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

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

Using Names

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

Application Methods

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

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

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

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

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

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

Workbook Events

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

Range Methods

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

Connections Methods

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

Chart Object Object and the Chart Objects Collection

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

Trusted Locations

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

Color Scale Object Example

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

Color ScaleCriterion Properties

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

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

Point Object and the Points Collection

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

Command Bars Collection Properties

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

Chart Properties

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

Pivot Table Methods

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

The VBComponent Object

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

Format Condition Properties

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

Encapsulation

Class modules allow you to encapsulate code and data in such a way that it becomes very easy to use, very easy to share, and much easier to maintain. You hide the code that does the work from the user, who only needs to know what sort of object the class module represents, and what properties and methods are associated with the object. This is particularly useful when it is necessary to make calls to the Windows API application programming interface to perform tasks that are not possible in...

Programming with the Windows API

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

Displaying a User Form

Excel 2010 Create Vba Userform

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

Primer in Excel VBA

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

Axis Object and the Axes Collection

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

Page Break Properties

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

Creating a Toolbar

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

Pivot Item Properties

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

Maintaining a Data List

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

Using ADO in Microsoft Excel Applications

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

Connecting to Microsoft Access

ADO connects to Microsoft Access databases through the use of the Microsoft Office 12 Access Database Engine OLE DB Provider. To connect to a Microsoft Access database, you simply specify this provider in the ADO connection string and then include any additional provider-specific arguments required. The following is a summary of the connection string arguments you will most frequently use when connecting to an Access database Data Source full path and filename to the Access database required...

Connector Format Properties

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

Traversing and Modifying XML Files with DOM and XPath

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

Plot Area Object Example

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

The Query Table and List Object

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

The Show DataForm Sub Procedure

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

The CForm Resizer Class

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

DynamicMenu

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

CustomXMLNode Properties

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

Data Label Object and the Data Labels Collection

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

Customizing the QAT

The Office 2007 design philosophy is that the QAT belongs to the user and applications should never add their controls directly to it. If the users consider your feature to be useful, they'll put it on the QAT and Excel will automatically handle the interaction. In certain scenarios it would be highly beneficial to be able to add controls to the QAT such as a user creating add-ins similar to the Auditing add-in presented at the start of this chapter, but by moving groups of controls on and off...

Columns and Rows Properties

Columns and Rows are properties of the Application, Worksheet, and Range objects. They return a reference to all the columns or rows in a worksheet or range. In each case, the reference returned is a Range object, but this Range object has some odd characteristics that might make you think there are such things as a Column object and a Row object, which do not exist in Excel. They are useful when you want to count the number of rows or columns, or process all the rows or columns of a range....

Chart Objects Collection Properties and Methods

Set Get whether any macros associated with each ChartObject object in the collection can be triggered by the user Set Get the height of the ChartObject in the collection if there is only one object in the collection Set Get the distance from the left edge of the ChartObject to the left edge of the parent sheet. This property only works if there is only one ChartObject in the collection Set Get whether the ChartObject is locked when the parent sheet is protected. This property only works if...

OLEDBConnection Properties

Returns an ADO connection object Set Get whether an external connection file will be used to establish a connection to the data source. When this property is set to True, any embedded connection information will be ignored, and the external connection file will be used Set Get the processing of queries asynchronously. False for OLAP data sources Set Get a command string that passes commands to the data source. This property essentially replaces the SQL property, which still exists...