Exporting to an XML File

Similar to the Import method, the Export method allows for the exporting of data from an XML list or range of cells into an XML document. As you can see in the following code, the data is exported to a URL of your choice. Although the URL shown here points to a local location, the export URL can be on a network drive or web server. This method comes in handy when integrating data inputs from various users into a standardized data-gathering exercise _ URL ThisWorkbook.Path & Exported.xml End...

Common Properties and Methods

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

Charts

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

Working with XML and the Open XML File Formats

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

OnTime

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

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

Xml Map Properties

Set this to True to automatically adjust column widths when data is refreshed Set this to True and imported data will be appended to current data. Otherwise, imported data will be overwritten Read-only. Returns an XmlDataBinding object that represents the binding associated with the specified schema map Read-only. Determines if the current data is exportable Get Set the name of a given XMLMap object Set Get whether the column filters are persisted Set Get whether number formatting is persisted...

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

Establishing the Connection

Once you have made a connection with an Office application, its objects are exposed for automation through a type library. There are two ways to establish such a connection late binding and early binding. In either case, you establish the connection by creating an object variable that refers to the target application, or a specific object in the target application. You can then proceed to use the properties and methods of the object referred to by the object variable. In late binding, you...

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

Code Module Properties

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

Run Time Error Handling

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

Adding a Chart Sheet Using VBA Code

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

Worksheets Collection Properties and Methods

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

Popup Menus

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

Embedded Charts

Excel 2007 Userforms Charts

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

Object Variables

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

Watch Object Example

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

Range Object and the Ranges Collection Object

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

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

Application Methods

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

Constants Structures Handles and Classes

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

Trusted Publishers

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

Add Ins Collection Methods

Returns an Add-In associated with the item. The parameter can be either a number or the Progld of the Add-In for example, MyAddin.dsrMyConnection . Updates the list of available COM Add-Ins from the Registry. This should only need to be used if you are compiling an Add-In through code for example, using VBProject.MakeCompiledFile . The following example iterates through all the Add-Ins registered for use in the VBE and prints information about those that are active...

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

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

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

Resize Property

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

Chart Object Properties

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

Introduction

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

Hyperlink Object and the Hyperlinks Collection Example

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

Pivot ItemList Object

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

Command BarButton Methods

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

OLEObject Methods

Sets the focus and activates the OLE object Brings the OLE object to the front of all the other objects Copies the OLE object into the clipboard Appearance As Xl Picture Appearance, Format As XlCopy Picture Format Copies the OLE object into the clipboard as a picture. The Appearance parameter can be used to specify whether the picture is copied as it looks on the screen or when printed. The Format parameter can specify the type of picture that will be put into the clipboard Cuts the OLE object...

The Open Text Function

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

The Fields Collection

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

Workbook Events

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

Range Methods

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

Connections Methods

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

Chart Object Object and the Chart Objects Collection

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

Text EffectFormat Properties

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

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

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

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

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

The xxxLocal Properties

Up until now, you have had to interact with Excel using English-language functions and the default U.S. formats. Presented now is an alternative situation, where your code interacts with the user in his or her own language using the appropriate regional settings. How, then, can your program take something typed in by the user such as a number format or formula and send it straight to Excel, or display an Excel formula in a message box in the user's own language Microsoft has anticipated this...

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

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

Date Custom Filter

If you want something a bit more complex, such as a range of dates, you need to do a bit more work. The following screen shows how you can manually filter the data in a table to show a particular month. Click the drop-down button beside Date and choose Date Filters. Then select either Custom Filter or Between. You can then fill in the dialog box as shown in Figure 6-6. The format you use when you type in dates in the Custom AutoFilter dialog box depends on your regional settings. You can use a...

Pivot Item Properties

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

Xml Map Methods

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

Maintaining a Data List

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