Robert M. ("Butch") Judkins
For the past fifteen years my primary focus has been spreadsheet development, initially with the Lotus 123
macro language, and for the past decade within Excel with Visual Basic for Applications. During this time
I have developed spreadsheet applications by unleashing the power hidden behind the more obvious
features of Excel.
The applications referenced below can be executed by opening a file, selecting a menu item, or clicking a
command button. The processing time ranges from seconds to approximately five minutes. This represents
a time savings of several minutes to hours per application.
The Ranking application was written to accommodate report specifications designed by Frank Powers,
Chief Operating Officer at American HomePatient. Visual Basic code resides in NetRevProdUnbExe, an
executable workbook template which displays a customized menu upon opening. Clicking the Execute…
menu item displays an input box prompting for the reporting period. The program opens source files to
populate Area and Unit tabs created to rank net revenue, operating contribution, productivity, and unbilled
categories. The CreateSortFile routine opens a sort file template. The routine then copies the tabs to the
sort file template and saves the file as NetRevOpConProdUnbmmyyyy (mm=month, yyyy=year). The
EndRoutine closes the source files and NetRevProdUnbExe file, effectively passing programmatic control
to the open sort file. This file contains a SortData menu listing twelve (12) category sort options in
addition to an option that sorts and prints the active tab by each category with a single click.
The Personnel Analysis application consists of visual basic modules designed to track personnel expense
as a percentage of net sales and rentals; trended by area, unit, and location. The modProdAnalTrnd module
defines the target as an arbitrary percent. The output file is generated in Oracle and consists of seven tabs of
raw data. The main routine generates a unit and area tab for each of the six (6) months in the output file.
Units and areas are ranked by percent. An area file is created for each area.
The Key Performance Indicators application displays a message box prompting the user to click the
Dashboard menu item created when the template is opened. Upon clicking the menu item the user is
prompted for the reporting period. The reporting period drives the entire program, as source files are
opened for the reporting year. Area lookup tables are created in the data files, and fields in the target file;
which is based on the template, are populated.
The Net Revenue Analysis application is executed with the click of a command button. Visual Basic
modules are executed, processing net revenue and operating contribution raw data generated by
consolidating four Oracle reports into an Excel workbook. Area by Unit and Area by Location worksheets
are created, sorted, and subtotaled. Additional tabs calculate and sort units and locations respectively by
percentage increase / decrease in net revenue.
The Performance Review application opens Key Indicators and Variance Report files with the click of a
command button. Data is written from Excel to a Word template, and a unique document is created for each
unit and area.
The EDIGrps add-in writes data from an Excel spreadsheet to a new Word document in a format
compatible with a Blue Cross/Blue Shield insurance database application. The delivery of this program
potentially saved the Company considerable dollars in consulting fees.
The Bank Macro application opens the BankInfo.xls file and updates an account lookup table. The
Previous.xls file is opened, and the customer, amount, transaction, and ID tables are defined. A new table is
created and populated with those records matching accounts in the source file. The table is then subtotaled,
and the records are pasted to a newly created worksheet. Finally, the detail rows are deleted, leaving the
summary totals for each account represented.