I have been creating applications with Visual Basic for Applications (VBA), Access, and Excel, for more than 25 years. These have been applications I created for myself to manage complex processes and applications used by as many as 20 people with up to 100% concurrency.
Usually, my applications have back-end databases in MS SQL Server or Oracle, for which I often build the tables and views. I have my applications interact with these databases through linked tables, pass-through queries, and stored procedures (TSQL and PL/SQL) executed from the application. My applications often include automated interaction with Outlook, Word, and even PowerPoint.
I am especially proud of my user interface (UI) design. I work with my clients to create intuitive, mentally-ergonomic interfaces that efficiently use screen space. The pinnacle of my UI design is for self-serve reporting features. See the case study and work sample "Better reporting from NetSuite than NetSuite", below.
Including the clients for the projects showcased on this page, my clients for VBA-based tools are:
MTS Systems: (1) a price-setting application for thousands of after-market products; (2) a price-setting application for software products; (3) a quote generator for products and services, designed to support > 1 language; (4) a generator of customer-facing reports of hydraulic oil quality tests.
Wells Fargo Corporate Trust: 10+ applications for managing billion-dollar housing bond issues and other special-rule investment products.
Blue Cross/Blue Shield of Minnesota: (1) an app for regulatory reporting and (2) an app for creating customer-facing MS Word-produced customized letters. Both applications reduced from two weeks to two hours the time to perform these monthly tasks.
Target: an app to analyze digital advertising products by performance for the customer and for Target.
Nabisco: an all-Access app that let me manage the editing, review, approval, publication, and archiving of hundreds of product, ingredient, and packaging specifications. Built to support my role in the Refrigerated Foods division, the app was later used by my counterparts in the Planters/Lifesavers and Food Service divisions of Nabisco.
Ameriprise Financial: created or enhanced dozens of apps to perform security trading clearing operations. Also consulted on and implemented lean metrics to measure productivity and identify wasted effort.
Best Buy: In support of an entire automated pricing system I created, I also created (1) a product manager interface for reviewing, editing, and accepting programmatically-defined prices and (2) a group manager interface to review how rules were being applied, to edit and create rules, and test those rules using the previous business day's data.
Medtronic: Application to manage data quality standards to support project merging multiple databases into SAP/ERP. App associated each quality standard with a script created to enforce it; programmatically combined any number of selected scripts to create custom test scripts as needed throughout the project; recorded details on problems found (table, record, field, standard violated, and field value); created report of summary statistics after each test and emailed report to community of interest.
Minnesota Orchestra: a contact list generator for group ticket sales. The UI lets the user select multiple past concert titles to identify groups that attended in the past.
Case studies and work samples on this page
Global Traffic Technologies: Self-serve reporting tool for NetSuite CRM data.
Minnesota Astronomical Society: membership management database & communication tool.
Wells Fargo Home Mortgage: Recruiting candidate database with search interface.
Global Traffic Technologies
Oakdale, MN
Better reporting from NetSuite than NetSuite
The client’s CRM application, NetSuite, did not provide the level of customized reporting needed. For example, it could not provide aggregate (summarized) data for customers by quarter, product class, and other categories. Also, the NetSuite interface did not facilitate the kinds of searches and filtering the client wanted to do.
Facts & requirements
- The NetSuite CRM application used a SQL Server back-end.
- Users needed to be able to search by:
◊ numeric range
◊ date period
◊ look-up value
◊ multiple criteria for a given field
◊ wild-card and boolean criteria
- Users wanted to choose what to see in each row of results.
Challenges
Simplicity in my design of the user interface would be challenged by the large number of ways the client wanted to filter, group, and display. The screen could end up crowded, resulting in an unfriendly user interface.
Another challenge was that the information the client wanted as options would come from many different tables. Not knowing in advance what combinations of choices the user might make, my application could not simply filter a predetermined set of data. Instead, it would need to compose on-the-fly whatever custom multi-join SQL strings were needed to meet the user's wishes.
Analysis
My solution would need to address three areas:
- Keeping the interface simple by displaying user options only as needed.
- Finding a flexible way of producing complex SQL.
- Providing aggregate (summary) data regardless of the results of the search.
Solution
I produced an MS Access/SQL Server/VBA application called aSSRT (a Self-Serve Reporting Tool).
Key to aSSRT's functionality were administrative tables that recorded how one table joined to another and what type of Access form control should be displayed on the form based on the type of search criterion the user wants to define. For example, when the user chooses:
- a numeric range, two boxes appear and accept only numbers as criteria.
- “Opportunity Title (lookup)”, a list box appears listing all Opportunity Titles.
- “Opportunity Title (wildcard)”, a text box appears and accepts wildcard characters.
An additional search option was a drag-and-drop pop-up form I designed. This would appear if the user selected a “multi” search option.
Shown below is a portion of this admin table. The user never saw this table, but when they selected something on which to base their search, this table determined the on-screen control that appeared to accept the user's criteria: a control for numbers (num), date, lookup of available values (lkup), wild card, or drag-and-drop for selecting multiple available values (multi).
aSSRT proved so popular that I was later contracted again to add new functionality.
Minnesota Astronomical Society
Minneapolis, MN
Membership management & communications application
The Minnesota Astronomical Society (MAS) is a non-profit organization promoting public knowledge and enjoyment of astronomy. MAS maintains an observatory and several observation sites around Minnesota and has a growing membership of almost 500 individuals and households. I was one of those members.
Managing membership dues, renewal reminders, rosters of who has keys to the observation sites, and other membership responsibilities is done by one volunteer. The Access database that was used to manage this had been created almost 15 years earlier by a non-programmer. Most of the database’s limited features had ceased to work years ago. As a result, the Membership Coordinator had to spend almost a day each month manipulating the data with Excel to produce renewal letters, reports, and newsletter distribution lists.
The success criteria for the membership management & communication tool I would create was:
Automate tasks and reminders wherever possible.
For code likely to need changes, don't require a developer (like me); let those areas of code be maintainable by the end-user.
Verify data quality before mass communication.
Let the user practice on test data during initial training or any time they wanted to practice.
The MAS Membership Database I created is a wholly MS Access client/server application. It consists of four separate Access files: the user interface, the LIVE data file (production), a TEST data file (always a fresh copy of the LIVE data), and an administrative settings file.
See a slideshow about the user interface and its features.
See examples of my VBA from this app
(including my workaround to permit mail merges from database that is both data source and merge initiator)
Wells Fargo
Minneapolis, MN
Tool to search an entire industry for best employment candidates
In the problem-solving case study "Why finding 30,000 candidates was easier than finding three", I mention having created a user interface to querying the very large number of mortgage loan officers I webscraped from competitor websites. That search tool is called CANINE.
CANINE stands for Candidate Identification & News Database. The news part was a feature that employed my competitive intelligence skills. CANINE was a distributed, split MS Access-only client/server application because a SQL Server database was not available.
With the user interface, recruiters can:
- Find all candidates working within a given radius (miles) around the ZIP code of the Wells Fargo office for which candidates were needed.
- Filter candidates by foreign language skills, loan specialization, and sales awards.
- Quickly see if any candidate was currently or had been considered for employment with Wells Fargo. This feature tied into a Human Resources database.
After my assignment was completed, I was contracted periodically to repeat my nationwide research and replace the candidate table with updated information. At its largest, the database recorded over 30,000 mortgage loan officers around the United States.
See a slideshow about the CANINE user interface and its features.
Copyright Will Beauchemin 2024