Matthew S. Harris

Project Summary

Professor Allen, a professor at the Business School of Brigham Young University, found me when making Internet searches for a previously published author of Excel VBA books who had received excellent reader reviews.

Prof. Allen engaged me to write a textbook tailored to his curriculum for business-school courses on VBA programming in Excel. Devising the book's structure and outline was a highly collaborative process between myself and Prof. Allen; the specific tasks of explaining specific topics and devising appropriate code examples for those topics was accomplished by myself.

The book is for a 3-semester series of courses that take students from beginner VBA skills through advanced topics. All three sections (one section per semester) of the book were published towards the end of 2016, and have received excellent student reviews. The book's 41 chapters are approximately 600k words total and contain several thousand lines of example code.

The book is published as an e-book by MyEducator.com. You can view the title page of the book and table of contents at https://www.myeducator.com/reader/web/1206a/. (Unless you purchase the book, you can only view the title page and table of contents.)

This book teaches basic VBA programming, and then takes the reader step-by-step into increasingly advanced topics such as:

• user-defined data types
• creating custom object classes
• custom ribbons
• creating Excel add-ins
• automating the creation and formatting of Pivot Tables and Pivot Charts
• automating the set-up and completion of Solver
• using VBA in Excel to control Internet Explorer, MS Word, MS Outlook, and MS PowerPoint
• data import/linking from a variety of database formats
• using ADODB to execute SQL queries on external databases
• how to use Windows API functions in VBA

One of the very satisfying aspects of a project like this is the opportunity to get new generations of people started (and started the right way) in programming time- and effort-saving apps for themselves and others by creating a detailed and comprehensive textbook like this one with code examples showing how to write the highest-quality and most easily maintained code possible.

Project Summary

This project was a continuation of the 2013 energy-use study funded by Stanford University/US Department of Energy.

Matthew developed an MS Access application that provides these benefits:

• Saves hundreds of hours of labor by importing CSV files with thousands of rows, aggregating time-based data, validating data, removing duplicate records.
• Saves days of labor by automating participant feedback via e-mails to a single mouse-click.
• Helps ensure the validity of the study by ensuring all data is validated and the import process is auditable.
• Makes it possible for a single researcher to conduct the study.

Case History:

Raw study data is submitted to the researchers in CSV files from two different sources, in two different formats. One data-set is time-based energy consumption data, and the second data-set is data provided by study participants via a questionnaire.

Matthew performed data analysis of the CSV data files, and designed a methodology to import data from both data sets and establish appropriate relationships between participant-supplied data and participant's energy consumption information.

Matthew used MS Access VBA to develop an application which fulfills these goals:

• Provide a consolidated repository for study data in a normalized relational database
• Reasonably ensure that all input data is validated and the import process is auditable
• Automate the creation of data graphs by using MS Excel under the control of VBA code executing in MS Access
• Automate the generation of e-mails sent to study participants by using MS Outlook under the control of VBA code executing in MS Access

Project Summary

A researcher working on an energy-use study funded by Stanford University and the United States Department of Energy (DoE) asked Matthew for help processing raw data collected for the study.

Matthew developed an MS Access application that provides these benefits:

• Saves hundreds of hours of labor by importing CSV files with thousands of rows, aggregating time-based data, validating data, removing duplicate records.
• Helps ensure the validity of the study by ensuring all data is validated and the import process is auditable.
• Makes it possible for a single researcher to conduct the study.

Case History:

Raw study data is submitted to the researchers in CSV files. Not all rows of the raw input file contain valid data, and not every file contains data collected at the same time interval.

Matthew performed a comprehensive analysis of the raw data files, and developed a data-aggregation and validation application in MS Access VBA to meet the following goals:

• Provide a consolidated repository for study data in a normalized relational database
• Provide a "clean" data-set for statistical analysis, exportable without needing to create queries to exclude invalid or irrelevant data
• Automate the aggregation of data collected at quarter-hour intervals into hourly interval equivalents
• Reasonably ensure that all input data is validated and the import/aggregation process is auditable

Project Summary

Repeat client. Upgrade/update previously developed Customer Service database and VBA software from a 2004 project from MS Access 2003 to MS Access 2010.

The upgrade process provided these benefits:

• Utilize new features of MS Access.
• Saved several hours of labor per month by adding a feature to automatically create summary charts of commodity prices in MS Excel.

Project Summary

WellStaff USA is a staffing company for health care professionals. Matthew designed and programmed a productivity application in MS Excel VBA for WellStaff USA, at the request of WellStaff's personnel. WellStaff USA found Matthew as a result of a web search for custom software!

The MS Excel application was an estimating calculator for sales, providing these benefits:

• Enabled a salesperson to negotiate rates and discounts with a customer in real time, without having to make a call-back to the customer.
• Ensured minimum limits were not exceeded.
• Provided output easily copied & pasted into a contracts application.

Sadly, Wellstaff USA no longer seems to be in operation.

Project Summary

The New Haven Schools Foundation (NHSF) is a non-profit organization dedicated to supporting the New Haven School District (nhsfoundation.org). NHSF awards/administers scholarship funds, and conducts fund-raising activities in order to provide grants to teachers or student groups to fund curricular and extra-curricular educational materials and events, and to purchase school equipment not otherwise in the district's budget.

In an effort to enhance fundraising efforts, on a tight budget, and with special needs not met by off-the-shelf donor management software, NHSF contracted Matthew to develop a Donor Management System in MS Access.

Matthew designed and programmed an application to meet the specific needs of the NHSF, providing the following benefits:

• Easily used by volunteers and persons without database knowledge or experience.
• Makes reporting on funds raised through specific campaigns a matter of minutes.
• Replaces chaotic and ill-maintained paper records with an organized & and easily searched record-keeping system.
• Easily identifies high-level donors.
• Produces a variety of reports (financial, demographic, and others) with a few mouse-clicks.
• Saves hundreds of labor hours by automatically creating mail-merge documents (for donor outreach, event notices) based on mail lists created through custom searches.
• Substantially reduces time required to produce Federally-mandated tax-receipt letters: user enters a year value and selects an MS Word mail-merge template; the application automatically generates the tax letters, calculating the deductible amount of the donation in cases where the donor received goods in return for the donation.

Case History:

Matthew designed and created a secured database in MS Access to meet the specific needs of the NHSF. The NHSF Donor Management System provides a variety of record-keeping and reporting tasks:

• Tracks donor contact information and donation amounts, making it possible to identify high-level donors and to contact all donors.
• Easily determine cash in- and out-flow by tracking fund-raising campaigns, class-room grants and scholarship awards.>
• Easily identify which fund-raising campaigns are most effective.
• Creates financial summary, demographic, and other reports.
• Enables users to select a variety of search criteria to identify donor groups, and to save the search criteria.
• Calculates donors' tax-deductible value of contributions where a gift is received in return for the donation.
• Creates mail lists or mass-mailings to selected donors for fund-raising outreach & notice of upcoming events.
• Track and report on data related to the Board of Directors and its members:
  • Dates of service.
  • Committees and their members.
  • Meeting minutes and members in attendance.

The NHSF Donor Management System's features include:

• Secured/encrypted data via MS Access workgroup security.
• User-interface to manage users and passwords that does not require knowledge of MS Access.
• Store contact information for donors, scholarship recipients, and grant applicants.
• Track and report on:
  • Donations related to specific fundraising activities.
  • Demographic data about grant and scholarship recipients.
  • Grant applications/awards, and scholarship awards.
• Automatically select records and generate mailing-lists for:
  • Tax-receipt letters to donors.
  • Congratulations/regrets letters to grant and scholarship recipients.
  • Fundraising campaign/event notification letters/cards by selecting groups of donors matching a variety of search criteria, including past participation in an event.
• Searches may be saved for repeated use; mail-lists may be saved as static lists.
• When generating a mailing list, the application generates a mail-merge data file, then opens MS Word to complete the mailing.
• Searchable note fields enable finding donor records based on special interests, such as an interest in estate donation.
• Inter-operates with MS Word when generating mass-mailings by automatically starting Word and initiating the mail-merge in a user-selectable merge document.

Project Summary

In an ongoing effort to protect consumers from credit card fraud, VISA developed a set of security guidelines for businesses handling credit card data, and maintains a certification program for businesses adhering to these security guidelines. At the time this project was undertaken, the security certification program was known as Cardholder Information Security Program (CISP). (The CISP security standard has since been replaced by the Payment Card Industry (PCI) data security standard).

VISA had a need to better supervise and report on compliance and certification for the CISP program.

Matthew designed and created a secured multi-user database for in-house use at VISA. This database assisted seven different task groups within VISA's Risk Management department in tracking and reporting on outside entities' compliance with, and certification for, anti-fraud security practices established by VISA.

The resulting MS Access application solved a variety of timeliness issues, increased the reliability of compliance tracking, and significantly automated several time-consuming tasks:

• Eliminated a 2-3 day turnaround in inter-departmental data sharing by providing real-time shared data access.
• Instant summary reports for top managers; some summary reports previously required as many as 14 days to prepare.
• Track other companies' compliance with, and certification for, VISA's anti-fraud security practices.
• Automatic reminders of entities whose certification renewal was upcoming and/or overdue.
• Reduced the time required to audit a particular type of report submitted by banks doing business with VISA from 6 to 7 days to 6 to 7 hours.
• Reduced data-entry time by automatically updating key information across multiple data-sets.
• Eliminated the time required to manually update a compliance report published on one of VISA's web sites by automatically writing the new report HTML page to a disk file.

Case History:

Matthew completed a 6-month contract programming assignment at VISA USA. Because of Visa's corporate rules governing the hire of contract and consulting labor, Matthew was assigned to work at VISA through Partners In Business, a business-services firm (http://pibsinc.com).

Matthew designed and programmed a process-tracking and reporting database system for use by VISA staff responsible for auditing compliance with CISP requirements. This application was used by 7 different departments, and had these features:

• A secured multi-user database in MS Access (30-40 simultaneous users).
• Utilized user-entered data and data obtained from other corporate databases.
• Networked application used by multiple departments within VISA's Risk Management administration as well as the CISP certification program.
• Real-time data sharing between departments, greatly streamlining operations.
• Real-time reporting on certification status and other processes.
• User-interface to manage users, permissions, and passwords.

The completed application was a little over 80,000 lines of code. Confidentiality prohibits discussing the product in any more specific detail.

Project Summary

Repeat client. Matthew created a multi-user Customer Service database (CRM) in MS Access. Deliverable items included the application itself, an installer program, a data-dictionary (technical documentation); and user-manuals in both printable and HTML formats.

Case History:

Tri-CED is a nonprofit corporation contracted to perform curbside collection of organic and non-organic recyclables in the California cities of Union City and Hayward (www.tri-ced.org). Because of the success of an earlier project and an affordable bid, Tri-CED engaged Matthew to develop this product for them.

Tri-CED has two major needs: First, to adequately manage service for over 65,000 pickup locations. Each location receives different services depending on city, whether the location is a single- or multi-family residence, a business, and/or is eligible for handicap service. Second, Tri-CED had a contractual need to submit extremely detailed performance reports to the cities served and to labor unions. Tri-CED's existing customer database was insufficient to these needs.

The solution was to develop a new Tri-CED Customer Service System (TCSS) in MS Access and VBA. The TCSS system is a complete Customer Relations Management system customized precisely to fit Tri-CED's unique requirements and existing business practices. The TCSS system cost about 70% less than the closest-matching off-the-shelf CRM system, and (unlike the off-the-shelf CRM) used existing hardware, has unlimited users, and met Tri-CED's needs 100%.

Matthew designed & programmed the TCSS system from the ground up, giving it these features:

• Maintains a record of all service addresses and associated (multiple) customer contacts.
• Maintains records of driver hours and tonnage collected per material, per truck.
• Flexible searches to view addresses, contacts, service orders, and other information.
• Create and track service orders, maintaining a viewable history of all service orders at any address.
• Create and track driver tags (a driver's notification about problems at the pickup).
• Manage route-assignments - which pickup locations are serviced by which routes.
• Automate monthly, quarterly, and annual reporting for statistical and performance reports.
• High level of user configurability: users can add new containers, material types, service types, and other "lookup" items to update or expand the type of information tracked and reported on.
• User-level security (user-name + password) to control which activities a specific user may perform.
• Online HTML help manual and printed user manual.

As part of the delivery and deployment of the new system, Matthew also converted about 60,000 existing customer records into the new system. Tri-CED staff and management have expressed extreme satisfaction with this product, in use on a daily basis since for 9+ years without problems.

Project Summary

Matthew created a Point-of-Sale and Inventory/Shipping system in MS Access to meet Tri-CED's unique needs as a CA redemption container business. The database application (Inventory & Point Of Transaction, or IPOT) operates a cash drawer and receipt printer, generating receipts compliant with California regulations for redemption container purchases. IPOT tracks all relevant information needed to meet reporting requirements, and automates the generation of those reports, as well as reports used internally for accounting and performance evaluation. IPOT tracks inventory real-time, creating shipping documents and accounting summaries.

Case History:

Tri-CED is a nonprofit company which purchases redemption containers and recyclable materials directly from the public through a "BuyBack" facility, and then resells them in bulk.

The Buyback facility had several needs. Foremost, as an authorized CA recycling facility, strict requirements must be met for receipt format, record-keeping, and reporting. Because of the CA state requirements and the fact that the Buyback purchases items rather than selling them, a simple cash-register and/or off-the-shelf Point-of-Sale system is inadequate, resulting in large amounts of hand-written receipts and reports. Tri-CED management also had a strong desire to automate inventory monitoring and the creation of required shipping documents (Bills of Lading).

The solution was development of a custom Point-of-Transaction/Inventory system using MS Access and VBA. The resulting system consists of four modules: a Cashier module, an Inventory/Shipping module, a Management Console, and a Workstation module.

• All modules are programmed in MS Access/VBA.
• Each module is designed to run temporarily as a stand-alone application in the case of network failure.
• Each module interacts with the other modules to provide near real-time reporting on cashier and inventory activity.
• All modules employ user-level security (user-name + password) to control which activities a user may perform.

The Cashier module runs on a dedicated computer, and controls a cash drawer and receipt printer. The cashier enters the customer's materials as line-items - the material type, and the count or weight of each material. The Cashier system computes the subtotal for each line-item from current price-lists, computes the total to be paid to the customer, prints a receipt, and opens the cash drawer. The Cashier module also provides these features:

• Supervisor-authorized price overrides
• Void in-progress or completed transactions
• User lock-out after inactivity to ensure cash drawer security

The Inventory module was designed for stylus input on a tablet handheld computer for real-time data-entry of baled materials.

The Management Console provides these features:

• Centralized updating of price-lists and materials handled.
• Receipt summaries needed for daily accounting.
• Centralized user/password management.
• Scheduling of intervals at which various modules attempt to update each other.
• Database integrity checks.

The Workstation module provides these features:

• Statistical and summary reports on cash paid out.
• Statistical and summary reports on materials shipped (tonnage/material, value, purchasers).
• Near real-time reporting on inventory levels.
• Automatic reminder when inventory reaches minimum level for an outbound shipment.
• Automated generation of Bills of Lading for outbound shipments.

This system has been in use 6 days a week, serving approximately 200 customers each business day, for 10+ years without problems.

Project Summary

Matthew maintained dabases created by another vendor for conference registrants. Matthew created additional databases & data-entry and reporting programs for Abstracts, Registrations (pre-registration and on-site), and scoring of reviewed papers. Matthew developed queries and reports to generate financial reports and to output electronic typesetting files used to print conference books and ID badges. Matthew trained and supervised data-entry operators and other users of the conference databases.

Case History:

The 6th International Conference on AIDS was hosted jointly by UC-San Francisco and the City of San Francisco. Like most conferences, the 1990 AIDS Conference began with a small core staff about 12 months in advance of the conference date. Matthew was contracted early in the conference process to act as the conference's technical advisor for database issues, especially regarding the practices of the sub-contractor handling advance registrations and submission of papers for presentation. Matthew's duties and responsibilities to the conference grew rapidly.

Matthew carried out these tasks on behalf of the conference:

• Perform quality assurance tests on databases compiled by subcontractors handling registrations and submissions of papers.
• Generate financial reports from databases compiled by subcontractors.
• Build databases and program data-entry for real-time entry and reporting of scores produced by Review Committee selecting papers for presentation.
• Generate mass-mailings of acceptance/rejection letters to persons submitting papers.
• Integrate registrations database with staff databases into a single database used to print ID badges.
• Maintain security of confidential information in conference databases, especially wiping disk drives of loaned/rented computers.
• Control authorized releases of database mailing lists to various private and public institutions.
• Generate special report files formatted with typesetting directives to automate electronic typesetting of conference books (Abstracts sections, indices to papers).
• Train/supervise other persons using conference databases.
• Arrange for donated and rental computing equipment needed for peak load times; supervised the setup, use, takedown, and return of all rented equipment.
• Advise conference staff on a variety of technical issues.

The 1990 AIDS Conference was an exciting and fast-paced project. During the critical stages of the conference – the Review Committee which meets the week before the conference to select papers for presentation, the creation of the typesetting files for the conference books, and the actual four days of the conference itself – Matthew remained accessible on-site 24 hours a day to ensure that all problems that arose within his area of responsibility were solved quickly.

Overview

Matthew performed a variety of contracted services to different medical research departments within the University of California - San Francisco.

UCSF-Division of Infectious Disease

Project Summary

Matthew assisted researchers with database design, data import/export & file format conversion, mass mailings. Matthew devised various methods to automate word-processing and e-mail tasks.

Case History:

The UCSF Division of Infectious Disease was a research facility operated in conjunction with San Francisco General Hospital. Matthew was contracted on a repeating basis over several years to provide a variety of computer support functions. Duties & notable achievements included:

• Create scripts for word-processing and spreadsheet software to automate document production.
• Write applications to automate sending of e-mail through pre-WWW Internet mail systems.
• Convert data to and from dBASE, Paradox, CSV, SAS, Lotus, Quattro and other formats.
• Assist researchers with database and statistical analysis software.
• Install, maintain, upgrade and repair computers and printers.
• Install voice-mail system.

UCSF-San Francisco Community Consortium

Project Summary

Matthew created database applications for clinical drug-trials, and patient and physician database applications. Matthew also trained data-entry staff.

Case History:

The Community Consortium was a group of public health outreach organizations working with research groups to support each other's activities. This one-time contract was to develop a data-entry program which would store data in a commonly-used database format using a robust stand-alone program. The data-entry was for a double-blind clinical drug trial, therefore data-entry needed to be tightly screened to help ensure maximum accuracy, and also needed to be formatted for transcription from up to 3 paper source documents. After completing a bulletproof data-entry program, Matthew installed it at the client site, and trained all the data-entry operators.

Project Summary

Matthew received multiple contracts over several years as a Pascal programming language tutor. Matthew also served as an instructor.

Case History:

UC-Berkeley Extension Services primarily serves the ongoing education needs of adults.

Matthew was engaged repeatedly by the University to provide one-on-one tutoring for students in the Pascal language course, and was retained to teach the course during the final semester it was offered. (The course was dropped due to lack of enrollment; interest in the Pascal language throughout the computer industry had waned significantly.)

While working as instructor, Matthew was responsible for devising the course curricula.

Project Summary

Matthew created database & data-entry applications for research questionnaires, helped automate mass-mailings to study participants. Matthew also wrote documentation for the databases and applications he created, and trained permanent personnel on using the databases.

Case History:

Kaiser Permanente Department of Research (formerly Medical Methods Research) engages in ongoing medical research. Matthew performed a variety of consulting and programming services for this department. In particular, he created database designs and data-entry applications for research questionnaires sent to study participants, and for managing mass-mailing lists of patients and study participants.

Matthew also trained Department of Research staff on using the database applications, word processing programs and other computer-related operations.

Project Summary

Field-Paoli Associates is a prestigious architectural & urban-planning firm. Matthew performed Network Administartion & user-support for 4 servers and 50 workstations. Matthew's task was to maintain, install, configure, troubleshoot, and repair workstations, servers, network components, e-mail servers/clients, anti-virus security, license managers, special print devices, and Internet communications. Matthew also installed and maintained applications such as MS Office.

Duties:

• Network administration (2 Windows NT domain servers; e-mail server and print server).
• Workstation maintenance (50 Windows 98/NT workstations)
• User-support to 35-45 employees for a variety of applications and technical areas: MS Office, Photoshop, FTP software, web publishing, video-conferencing, image scanning, high-end printing.
• Install and manage license-manager software for products such as AutoCAD.

Notable achievements:

• Setup a secure backup system enabling recovery of server state for any day in the past 90 days.
• Improve Anti-Virus security in mail systems and workstations.
• Moved company's broadband service from DSL to fractional T1.
• Install and configure EFI Fiery Print Management (a high-end network print management system). During staff training, EFI trainer stated that this installation was the only one she had seen with no connectivity problems and all workstations configured with remote access to EFI management features.

Project Summary

Matthew was one of a four-person Design team. He was the sole programmer responsible for creating the client application which provided the entire user interface to a remote database. He also contributed to the product's online Help system and other documentation, and was responsible for training the company's sales persons and product installers. The Charon product was in commercial distribution for 3+ years.

Case History:

Charon Information Systems was created to develop a single product - the Charon Subscriber System. The CSS was a state-of-the-art system targeted to the sector of the construction industry bidding on public works projects.

The Charon Subscriber System consisted of a central database of jobs currently open for bidding, and a client application used to interact with the Charon database. After a user enrolled in the Charon System, the central database system would call each subscriber every night and download the most recent jobs opened for bid according to the user's selection profile. Matthew was one of four developers on the project, and was solely responsible for programming the user-client system.

Matthew's duties and responsibilities included:

• One of four-person Design & Development (project management) team.
• Design to specification, code, and debug the Charon Remote Subscriber System Program (the user client).
• Work with other programmers to integrate several programs into single commercial package.
• Created a user-interface sufficiently intuitive that no operator manual was necessary.
• Implement program features such as:
  • On line help
  • File maintenance
  • electronic mail
  • telephone auto dial facilities
• Install PC data communications hardware and software at customer sites.
• Train customer personnel to use software and communications equipment.

The Charon Subscriber System was in development for one year and in commercial distribution for a little over 3 years, during which time Matthew functioned on a consulting basis for bug-fixes, user installations, trouble-calls.

Sadly, the Charon System was a little ahead of its time, having been introduced at a time when being "online" in any form was rare, and many businesses were too conservative to adopt the system despite its proven ability to reduce the costs of finding new business. The Charon System was so cutting-edge that most firms that did sign up had yet to buy their first computer! After valiant marketing attempts, Charon Information Systems closed its doors and its founder, Mr. Roger Jennings, moved on to other ventures.

Project Summary

Matthew's tasks were to build prototype computer and robotic assemblies and to make other electronic and mechanical fabrications, including production models. Matthew's efforts supported engineering staff's prototyping of new devices.

Case History:

At the time Matthew worked there, Integrated Automation was an applied Research & Development company, developing products to automate various processes for a variety of clients. IA built automated/robotic warehouse systems, banking systems, process control and inspection systems utilizing state-of-the-art software and hardware technologies. Integrated Automation, for example, produced one of the first ATM machines for Wells Fargo Bank, and built systems for the Bay Area Rapid Transit System (BART).

Matthew's duties at Integrated Automation included:

• Assist engineering staff.
• Work from blueprint, schematic, hand sketch, or oral description to create prototype electronic and mechanical assemblies.
• Make limited production runs; modify and assemble NEMA type enclosures, wire harnesses, printed and wire wrap circuit boards.
• Use milling machine and lathe to create or modify instruments, mountings, machine parts.
• Organize and maintain machine shop area and tools.
• Seek economical sources; purchase tools, materials.
• Make laboratory measurements, keep log books.