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

Query Tables and Parameter Queries

It is often useful to base your QueryTable on a parameter query rather than a fixed SQL statement. This allows you to determine which subset of the data you display, and even allows you to provide your users with the ability to modify the parameters when the QueryTable is refreshed. One notable quirk of parameter queries is that QueryTables will not support them if you use the OLE DB provider used in the previous two sections. Instead, you must switch to the ODBC driver. This is a simple matter...

VBComponents Collection Methods

Adds a new, built-in VBComponent to the project. The ComponentType can be one ofvbext_ct_StdModule, vbext_ct_ClassModule, or vbext_ct_MSForm. Parameters ProgId. Adds a new, custom VBComponent to the project. The result is always of type vbext_ct_ ActiveXDesigner. It seems that custom VB components can only be added to ActiveX DLL projects and not to Excel workbook projects. Parameters FileName. Adds a new VBComponent to the project from a file usually a previously...

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

OLEObjects Collection Properties and Methods

Set Get whether the OLE object is automatically loaded when the workbook is opened. Not valid for ActiveX controls. Usually set to False. This property only works if there is one OLEObject in the collection Read-only. Returns the border's properties around the OLE object. This property only works if there is one OLEObject in the collection Set Get whether the OLEObject is enabled. This property only works if there is one OLEObject in the collection Set Get the height of OLEObject frame. This...

Dynamic ActiveX Controls

As previously stated, it is more difficult to program the ActiveX controls than the Form controls. At the same time, the ActiveX controls are more powerful, so it is a good idea to know how to program them. You will see how to construct a combo box that behaves in a similar way to the drop-down in the previous example. Just to be different, use the BeforeRightClick event to trigger the appearance of a combo box in the D column of the SalesData worksheet, as follows Private Sub...

Adding Ribbon Controls

Chapter 14 explained how VBA applications can modify the Ribbon by creating custom tabs, groups, or controls. This was done by creating a text file containing the XML for the custom UI definition and adding it to the XML workbook file. When Excel loads the workbook, it sees the custom part and processes it, creating custom controls. As designed, this allows you to create document-level RibbonX cus-tomizations, but that chapter demonstrated how to achieve application-level customizations by...

Changing Windows Regional Settings and the Office UI Language

Throughout this chapter, the potential errors will be demonstrated by using the three locales outlined in the following table. The regional settings are changed using the Regional Settings applet Regional Options in Windows 2000 in Windows Control Panel, and the Office 2007 language is changed using the Microsoft Office 2007 Language Settings program. Unfortunately, the only way to change the Windows language is to install a new version from scratch. When testing your application, it is a very...

Consuming XML Data Directly

Once you have a well-formed XML document, you can start using the data it contains. One of the simplest ways to use an XML document is to open it directly from Excel. To help demonstrate this, open EmployeeSales.xml, shown here. This XML document contains data revolving around the invoices filed by the employee in an organization lt xml version 1.0 gt lt EmployeeSales gt lt Employee gt lt Empid gt 2312 lt Empid gt lt FirstName gt Mike lt FirstName gt lt InvoiceAmount gt 23 00 lt InvoiceAmount...

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

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

Loading XML into a DOM Document

Before you can do anything with DOM, you will have to set a reference to the MSXML object library. To do so, open the Visual Basic Editor and select Tools O References. In the References dialog box, select the latest version of Microsoft XML, as demonstrated in Figure 12-9. Microsoft XML, v3.0 Microsoft XMLjVt.O afiiicros Ft'XML 'v5 Microsoft XML, version 2.0 Microsoft.Vsa.dll MimeDir 1.0 Type Library mmAEPIugln 1.0 Type Library MMC Internal Web Browser event sink 1,0 Type Libre MMFUtil 1.0...

Edit the sharedStrings XML File to Implement Mass Updates to Text

As mentioned before, the sharedStringsXML part holds all of the strings used in the Excel file. These strings are referenced via the shared index number by each sheet in the container to apply them to the correct cell. One nifty trick is to change a string in the sharedStrings file and watch that change take effect in your Excel file. For example, in the SalesByPeriod.xlsx sample file, certain records are tagged with the market South America. Suppose you wanted to change all instances of South...

Using DOM with ADO to Convert Excel Data to XML

A useful aspect of a DOMDocument object is that it can serve as the container for any hierarchical XML structure. This allows you to load any valid XML construct into a DOMDocument object. Coincidentally, ADO has an XML persistence constant that enables any recordset to persist in an XML stream. ADO ActiveX Data Objects is a data access technology that is installed with Microsoft Data Access Components, and it's covered in detail in Chapter 20. To use the procedure demonstrated here, you will...

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

RibbonX in Dictator Applications

Most dictator-style Excel applications typically start by removing all Excel's menus and as many other UI elements as possible, so as to present a locked-down interface to the user. To do this for the Ribbon, use the startFromScratch attribute of the ribbon element to give a minimal Office Menu with New, Open, Save As, Recent Files, and Excel Options lt customUI 6 01 customui gt lt ribbon startFromScratch true gt lt ribbon gt lt customUI gt Obviously, you'd then include lots more XML to build...

Creating Custom Task Panes

When task panes were introduced in Office XP, developers were soon eager to use them for their own content. In Office 2007, Microsoft has answered that request by adding the ability for COM Add-Ins to create custom task panes CTPs , using custom ActiveX controls to define their content. Just like RibbonX, the COM Add-In tells Excel that it contains a custom task pane by implementing another interface, ICustomTaskPaneConsumer. A COM Add-In, therefore, needs to do the following to create a CTP...

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

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

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

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

Query Table Associated with a List Object

Standalone QueryTables are good for retrieving data that will be used for background or display purposes only. If you want the user to be able to interact with the data after it has been retrieved, a better option is to create a QueryTable associated with a ListObject. This creates a table in the Excel user interface with all of the built-in ease-of-manipulation features that users need to work with the data. Note that there is some overlap between the QueryTable and ListObject properties and...

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

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

Data Labels Collection Properties and Methods

Set Get whether the font size will change automatically if the parent chart changes sizes Set Get whether Excel will generate the data label text automatically Read-only. Returns the ChartFormat object, which controls the line, fill, and effect formatting for the chart area Set Get how the data labels are horizontally aligned. Use the xlAlign constants Read-only. Returns the name of the collection Set Get the numeric formatting to use if the data labels are numeric values or dates Set Get...

Collection of User Form Controls

When you have a number of the same type of control on a form, you often write almost identical event procedures for each one. For example, say you want to be able to double-click the label to the left of each of the TextBox in the UserForm in Figure 16-6 to clear the TextBox and set the focus to the TextBox. You would normally write four almost identical event procedures, one for each label control. Using a class module, you can write a single generic event procedure to apply to all the label...

Wrapping API Calls in Class Modules

If you need to use lots of API calls in your application, your code can get very messy, very quickly. Most developers prefer to encapsulate the API calls within class modules, which provide a number of benefits The API declarations and calls are removed from your core application code. The class module can perform a number of initialization and clean-up tasks, improving your system's robustness. Many of the API functions take a large number of parameters, most of which are not used in your...