Automated Data Analysis Using Excel

Pfizer Global Research and Development, Groton, Connecticut,

INTRODUCTION
Purpose of This Text
Evolution of the Spreadsheet
Should I Be Using Excel? What Other Options Exist?
Scope of Coverage
Projects Which Lead to This Text
Before Beginning
Odd Behavior in Excel to Watch Out For
The Top Productivity Hindrances in Excel
Final Thoughts

ACCESSING DATA IN EXCEL: A MACRO WRITERS PERSPECTIVE
Introduction
The Workbook
The Worksheet
Ranges in Worksheets
Using Explicit Referencing
Rows and Columns
Searching Worksheets-Using Find
Copying, Clearing, and Deleting Data
Sorting Data
Deleting Rows and Columns
Summary

METHODS OF LOADING/SAVING DATA IN EXCEL
Introduction
Using the Standard Open File Dialog Box to Load a File
Using the Standard Save As Dialog Box to Save a File
Automatically Opening Files and Templates
Importing Data to a Worksheet
Automatically Saving Files and Templates
Allowing the User to Browse for a Directory
Setting the Starting Directory for a User to Browse From
Using the Windows Registry to Save Settings
Determining Subfolders of a Chosen Folder
Determining Files within a Chosen Folder
Practical Strategies for Dealing with Large Amounts of Data
Creating Database "Friendly" Files
Obtaining Drive, Directory, and File Information
Summary

CONTROL AND MANIPULATION OF WORKSHEET DATA
Introduction
Scope and Use of Variables in VBA
Operating in Excel's Environment from VBA
Utilizing Arrays to Store Data
Passing Parameters By Value or By Reference
Array Looping Structures
Using Object Variables
An In-Depth Look at Worksheets
Extraction of Data Using Landmarks and Looping Structures
Summary

THE UTILIZATION OF FUNCTIONS FROM WITHIN VBA
Introduction
Creating and Utilizing a VBA Function in Code
Handling Errors in VBA Functions
Adding a Function to a Worksheet Cell Using VBA Code
Creating Additional Built in Functions for Excel
Dynamic Formatting of Worksheets Using Functions
Applying Dynamic Formatting Using VBA
Using the Macro Recorder to Capture a Process
Creating a Linear Regression Tool Using the VBA Analysis Toolpak
Creating a Polynomial Regression Tool Using the VBA Analysis Toolpak
Summary

DATA MINING IN EXCEL
Introduction
The Terrible Truth about Colors in VBA
Form Reuse in VBA Projects
The RefEdit Control and Its Associated Problems
Highlighting and Coloring Cell Fonts and Backgrounds
Creating a Highlight If Tool
Creating a Color Font If Tool
Creating a Copy If / Move If Tool
Creating an Extract If Tool
Creating a Windowing Tool
Linear and Nonlinear Mapping
Automatically Loading and Extracting Data from Complex Directory Structures
Summary

CREATING CUSTOM REPORT WORKSHEETS
Introduction
Use of Templates when Creating Custom Reports
Preparation of Dual View Reports
Executing Calculations upon Changing Views
Analysis within Report Worksheets
Basic Formatting Techniques
Automatically Emailing Reports
Summary

INTRODUCTION TO MICROSOFT ACCESS
Introduction
Elements of a Relational Database
Connecting to an MS Access Database
Queries: How to Retrieve Information in Databases using SQL
Using the Microsoft ODBC Add-In for Microsoft Excel (XLODBC.XLA)
Constructing a Database Query Tool
Using Data Access Objects (DAO)
Elements in the DAO Architecture
Summary

FROM EXCEL TO ACCESS AND BACK AGAIN
Introduction-DAO vs. SQL
Using Pointers in Dynamic Database Algorithms
Concepts in Database Alteration and Management
Creating New Tables in Access from Excel
Adding and Removing Fields in Access Tables from Excel
Adding Records to Specific Fields in Database Tables
Deleting Records in Databases Using Bound Controls
Compacting Databases Using VBA
Returning the Results of a Remote Access Database Query to an Excel Worksheet
Summary

ANALYSES VIA EXTERNAL APPLICATIONS
Introduction
Setting Up a Matlab ActiveX Server from Excel
Matrix and Vector Building
Defining Matrices and Vectors in Matlab from Excel
Using Matlab to Perform More Advanced Forms of Regression
The Inner Workings of the Multiple Linear Regression Example
Interfacing Excel and Origin to Perform More Complex Analyses
Excel to Origin Dynamic Data Exchange (DDE) Example
Interfacing Excel and Origin Using COM (Component Object Model)
Example: Creating a COM Tool to Perform Curve Fitting Using Origin from Excel
Opening and Plotting Excel Workbooks in Origin for Superior Graphics
Summary

AN EXAMPLE ADA APPLICATION WHICH GENERATES A REPORT
Introduction/Problem Definition
A Quick Word on Six Sigma
Dealing with the Raw Data
Process Analysis
The Final Report
Saving the Final Report
Summary of the Final Application

APPENDIX A: Advanced Browse for Directory Code Listing
APPENDIX B: The Menumakr Shareware Application
APPENDIX C: Regression Analysis and Best-Fit Lines
APPENDIX D: Built-In Constants for Microsoft Visual Basic for Applications
APPENDIX E: Excel VBA ODBC Functions (XLODBC.XLA)
APPENDIX F: Scope of Variables in Visual Basic for Applications
APPENDIX G: LONG Integer to a Hexadecimal Constant
APPENDIX H: Use of the SendKeys Statement in Excel VBA