Automated Data Analysis Using Excel

Brian D. Bissett

• Publisher: Chapman and Hall/CRC
• Year: 2020
• ISBN:
  9781000088496
• Edition: 2
• Topic: Economics
• Subtopic: Statistics for Business & Economics
• Index: Economics

This new edition covers some of the key topics relating to the latest version of MS Office through Excel 2019, including the creation of custom ribbons by injecting XML code into Excel Workbooks and how to link Excel VBA macros to customize ribbon objects. It now also provides examples in using ADO, DAO, and SQL queries to retrieve data from databases for analysis. Operations such as fully automated linear and non-linear curve fitting, linear and non-linear mapping, charting, plotting, sorting, and filtering of data have been updated to leverage the newest Excel VBA object models. The text provides examples on automated data analysis and the preparation of custom reports suitable for legal archiving and dissemination.

Functionality Demonstrated in This Edition Includes:

• Find and extract information raw data files

• 
  
  

• Format data in color (conditional formatting)

• 
  
  

• Perform non-linear and linear regressions on data

• 
  
  

• Create custom functions for specific applications

• 
  
  

• Generate datasets for regressions and functions

• 
  
  

• Create custom reports for regulatory agencies

• 
  
  

• Leverage email to send generated reports

• 
  
  

• Return data to Excel using ADO, DAO, and SQL queries

• 
  
  

• Create database files for processed data

• 
  
  

• Create tables, records, and fields in databases

• 
  
  

• Add data to databases in fields or records

• 
  
  

• Leverage external computational engines

• 
  
  

• Call functions in MATLAB ® and Origin ® from Excel

• 
  
  

1Customizing Excel's Ribbon Interface

Introduction

From Excel 2007 forward, menu items have completely changed from a text-based series of menus to an image and/or text-based series of items which reside in tabs on a long narrow image or “Ribbon” to create a new type of Excel menu. While the new menu system does look much nicer, it is unfortunately much more complicated to implement, and does not add any substantive functionality to the product other than making the user interface more esthetically pleasing. One of the nice things about the new menus is a tab can be hidden or displayed depending on any number of criteria, so the developer can with careful planning “unclutter” the Excel user interface with only the menu commands and options that should be needed.

To create a custom user menu or new tab, the developer must write some XML Code. The XML Code provides the structure to the ribbon tab. For instance, the XML Code defines what buttons will be on the tab, what the size of the items will be on the tab, what the placement of the items will be on the tab, and what subroutines will be triggered by selecting an item on the tab.

XML Code can be written in a text editor, but this is certainly not optimal for several reasons. First, before utilizing XML Code, it should be validated for correctness, and a text editor cannot do this. Second, XML Code like any language has a certain structure to it, and it is much easier to read and debug the code when seen in a properly structured format. For instance, XML Tags have beginning and ending tags, and a properly structured XML file will have indentations reflecting the file structure which make it easy to read and write. There are two free XML editors that I like to utilize for creating Excel ribbons. They are WMHelp XMLpad and notepad++ available from: (http:/​/​www.wmhelp.com/​download.htm) and (http:/​/​notepad-plus-plus.org/​) XMLpad is the easier for a novice to use as it has the XML validation capability built into the program. Notepad++ is a little more difficult to use, but also much more powerful in that it supports a variety of languages and is much more customizable.

In order to validate Microsoft Office Ribbon XML using notepad++, the user will need to perform three actions. First, the user will need to download a plugin called “xml tools” for notepad++. Second, the user will need to install the Office 2010 XML Schema to validate against (Figure 1.1). Last, the user will need to install the Office 2010 Control IDs (Figure 1.2). Once these tasks have been accomplished, the user can validate Ribbon XML by selecting Plug Ins->XML Tools->Check XML Syntax Now.

FIGURE 1.1Google Find Office 2010 XML Schema.

FIGURE 1.2Google Find Office 2010 Control IDs.

The XML Code has a certain structure to it that will be discussed in detail later in the chapter. However, once the XML Code is created and validated, it needs to be injected into an Excel File such as an Excel Workbook, Template, or Add-In. The Custom UI Editor Tool for Microsoft Office exists to inject XML Ribbon Code into an Excel file (Figure 1.3).

FIGURE 1.3Google Find Custom UI Editor Tool.

Unfortunately, the Excel file containing the injected XML Code will have to be opened to see if the XML Code displays the Ribbon as the user anticipated and works correctly. While the Custom UI Editor Tool for Microsoft Office does contain an XML editor, it is clunky and ill-suited for developing large XML files from scratch.

There are any number of ways to set up Excel to launch VBA Programs from a Ribbon menu item, but the best practice is to set up an Excel Macro-Enabled Add-In and inject the Ribbon XML Code into the Excel Add-In. The Add-In will display the Ribbon every time Excel starts, provided the Add-In has been installed in Excel. The Ribbon Add-In will not contain any programmatic VBA code; it will only contain code which triggers VBA subroutines in other Excel files to run when an action occurs on the Ribbon. For example, a button is pressed on the Ribbon, and the VBA Code in the Ribbon Add-In directs a subroutine in another Excel Workbook to execute.

Ribbon creation in Excel is an extremely complex and intricate task, and it is beyond the scope of this text to cover every facet of Ribbon development. For this text, Ribbon development will be limited to the following tasks:

1. Creating Tabs.
2. Creating Large, Small, Split, DropDown, and Toggle Buttons in a Ribbon.
3. Creating DropDown and Combo Box selections in Ribbons.
4. Creating EditBoxes, Checkboxes, and Labels in Ribbons.
5. Triggering VBA Programs from items 2 and 3.
6. Creating Groupings of related items in a Ribbon.

The full capabilities and syntax of Ribbon XML development can be referenced by reviewing Microsoft's “Custom UI XML Markup Specification” Reference available for download online (Figure 1.4).

FIGURE 1.4Google Find Microsoft's Custom UI XML Markup Specification.

Xml Code Structure for Creating Ribbons

XML allows for comments, and comments should be placed between the following syntax “<!--” and “-->”.

Examples:

Ribbons have been the mechanism utilized in Microsoft Office since Microsoft Office version 2007. The first line of any Ribbon XML must specify which version of Microsoft Office the User Interface (UI) Ribbon is to function in. Microsoft Office 2013 and 2010 utilize the same specification.

For Microsoft Office 2007, the following specification is used.

The above Microsoft Office Version Specific XML is inserted into an Excel Workbook using the Insert Command in the Custom UI Editor Tool, which is discussed at length later in this Chapter. (Note: Immediately prior to the publication of this text, a redesigned Custom UI editor for Microsoft Office was made available on GitHub and is a complete redesign of the original editor using WPF - Windows Presentation Foundation.) Once a version-specific Custom UI Part is added to Workbook usi...