Jack Webb

In a hurry?

Get just the facts from Joe Friday.

Projects

 

 Project Scheduler for Video Clip Files
Platforms Excel 2010 icon    Access 2010 icon
Client Overview Cinema Scene Marketing (CSM) partners with movie theaters and movie studios to provide theater patrons with an entertaining lobby experience by deploying innovative digital media displays in theater lobbies.
Project Overview CSM has a database of video clips that it uses to populate each TrailerVision or Videowall display. It extracts selected clips from the database and it then needs to generate a playlist for each of the hundreds of displays it manages.
The playlist will contain a mixture of trailers from various movie studies, "bumper" video segments and advertising clips. CSM needed the application to duplicate a specific scheduling logic their human schedulers had been using to optimize the sequencing of the clips.
Once the clips were sequenced, create different splits so that kiosks that are in proximity to one another do not run "almost-synchronized" playlists.
The final step of the process was to generate XML output files that would be uploaded to their hardware network.
To facilitate development the process was broken into five (5) phases and each phase developed and tested before moving to the next phase of the process.

process graphic

Application Features The application GabrAria developed provided CSM with:
  • Mapping tools which gave them the flexibility to alter data formats and field names if the inbound data management was altered.
  • Path Management tools. As new hardware or software was brought into use, or as network server addresses changed key paths for the following are easy to update:
    • templates
    • input databases
    • XML output
  • A context-sensitive RibbonX tab. The application enables or disables controls based on the type of Excel workbook that is active.
csm ribbon when normal workbook active
csm ribbon when matrix workbook active
CSM ribbon sequencing workbook
CSM ribbon pre-XML workbook active
Project Stats
Total Lines 31,680
Code Lines 18,297
Comment Lines 13,383
Standard Modules 36
Class Modules 25
Userforms 7
Procedures 770
Deployment Three or four users.
Architecture
  • All VBA code is in an Excel Add-In.
  • Three templates are used to create workbooks that correspond to key steps in the process.
csm architecture
When we first contacted Greg we were challenged with scheduling the video clips needed to drive our TrailerVision product line. The scheduling tools we had developed in-house were simply unable to support the explosive growth we were experience for this product.  Greg was able to develop a custom application for us that allowed us flexibility in our input file paths and structures, administrative tools and generate a variety of XML output formats that we needed.  But most of all, Greg was able to develop a complex scheduling algorithm that would automatically take our jumble of various clip types, studios and quantities and produce a clip sequence that met our multifaceted needs.  While we have rarely needed any support for the application,  on those rare occasions when we’ve needed support, Greg has been able to get our problems solved very quickly. I would highly recommend Greg / GabrAria to anyone who needs his services.

John Crick, VP Network Technologies, Cinema Scene Marketing
Project Bridge Between Excel and SAP
Platform Excel 2010 icon   
Client Overview Client is a leader designer and retailer in the women's fashion industry. Based in New York City they generate $2.4 billion in annual sales through approximately 1,000 retail outlets throughout the United States and Canada. In addition to their strong traditional retail presence in North America, they ship to over 100 countries worldwide through their e-commerce channels.
Project Overview Client was in the midst of a major SAP implementation when they contacted GabrAria.  They had a number of critical processes that had developed organically (a nice way of saying non-uniformly) across multiple business groups over the years.  They needed to allow these business groups to continue to use their legacy processes which were Excel-based.  This required a new application that could serve as a bridge between heterogeneous Excel processes and provide a homogenous input process into SAP.  Despite significant IT resources, none of the client's internal IT personnel specialized in programming Excel. Therefore they were finding it very challenging to develop a tool that met the business groups’ needs plus the IT group’s needs.  GabrAria reviewed the client's requirements and determined Excel was capable of serving as an acceptable platform for the application needed.  GabrAria was able to design and build a tool that met all of the client's requirements.  The complexity of the development architecture and variety of desktops that were deployed to brought a variety of issues to the project. GabrAria did whatever it took to work through those challenges and make sure the client was satisfied with the results.  
Application Features

3 silo graphic administrative control menu The most significant feature of this application was the client's need to run three parallel environments:

  • Development
  • Quality Assurance (Testing)
  • Production

This meant that the entire path structures inside the application had to be designed as indexed arrays. Every single aspect of the application was impacted by this design decision.

This also meant that the application needed to provide administrator privileges to key users plus administrative controls to allow configuration of users' environments.

As with the Cinema Scene Marketing project, the project's interface was a custom ribbon tab.  The application would enable or disable various custom controls based on the type of workbook that was active.

version control dialogThe three-environment structure meant that version control had to be well implemented in the code and well managed throughout the implementation.   For this reason version management tools were provided to the application administrator(s).

logging menuIn addition to the centralized error handling and the error logging tools that are standard in all GabrAria add-ins, this application offered the client:

  • StartUp Logging to analyze any start up conflicts with other Add-Ins
  • Upload Logging to compare with SAP logs during testing
  • Performance Logging to facilitate identification of any processing bottlenecks.
Project Stats
Total Lines 40,054
Code Lines 22,325
Comment Lines 17,728
Standard Modules 52
Class Modules 17
Userforms 10
Procedures 806
Deployment Approximately eighty (80) users in multiple departments.
Architecture
  • All VBA code is in an Excel Add-In.
  • Templates are used to create workbooks that correspond to processes.
  • Each template contains complex validation rules to ensure that the data complies with SAP requirements and to business-group rules before users are permitted to upload the file.
ann architecture
 Project Canadian Expense Report
Platform Excel 2010 icon
Client Overview Haldex develops and manufactures brake systems for heavy trucks, trailers and buses. Their product offerings cover all primary components and subsystems included in complete air brake systems. Their annual sales are approximately US$ 571 million and they employ approximately 2,200 people at ten production sites and three distribution center located around the world.
Project Overview A Haldex employee at one of their facilities in North America had developed an expense report to be used for reporting expenses when U.S.-based employees needed to report business expenses while in Canada.   The employee had left Haldex and no one had the capability to make needed changes to macros in the expense report. 

The workbook contained a large number of forms controls that were populated via VBA code.  There were also several buttons on the surface of the worksheet that were not functioning properly.
Application Features

In this particular project it was not a case of adding significant or advanced features to the workbook.  Rather it was a case of getting simple on-sheet user interface components to function properly.

The most significant feature of this project was that key design aspects that governed the relationship between on-sheet data in cells and the VBA macros were re-designed to make the linkages more robust.  The previous design was very delicate.  The insertion or deletion of a row or column to the worksheet would cause the macros to break thereby making it very difficult to make any revisions to the expense report or related worksheets.  The re-design made the code robust against such changes, giving the finance group much greater flexibility to make changes to the worksheets without breaking the macros.

GabrAria was able to review the existing code base and make modifications necessary to have the forty-four (44) various dropdown controls on the worksheet's surface populate correctly.  Various formulas were also reviewed and corrections made.  Bugs were fixed in the macros that were called by buttons on the surface of the Expense Report Worksheet.

Architecture For this particular project it did not make sense to go with an advanced architecture that would separate code from data.  The code was not moved into a separate Excel Add-In.   Instead the code remained attached to the Expense Report workbook and the Controller's office maintained the "one true copy" of the workbook.   If any changes were needed, they were made to the Controller's copy of the workbook and distributed as needed.
I have known Greg for almost fifteen years and we have kept in touch after both of us left Sony (you always want to keep an Excel expert in your address book, right?). While at Sony Greg developed a reputation as the guy that could solve any Excel problem. So I was not surprised that he started his own Excel-based consulting company when Sony closed their facility in Kansas City. And when I needed some problems fixed on some Excel-based expense reporting tools that Haldex had developed in-house, Greg was the first person I called. Whether you have just some modest Excel problems that you need fixed or a huge full-scale application you need developed, do yourself a favor and hire Greg. He will provide you with a outstanding solution quickly for a very competitive price.

Sonya Downs, Director, Business Process Development, Haldex
 Project Supplemental Limited Title Runsheet
Platform Excel 2010 icon   
Client Overview Striker Land Services (SLS) provides all phases of land services to both independent energy producers and major oil and gas companies.
Project Overview SLS needed to create an Excel-based workbook that could function as a central, controlling document that would help landmen located around the country create a consistent and uniform listing of all the various documents that were related to a project.
Application Features
  • A custom ribbon tab was inserted into the XML of the workbook. Because the code and the ribbon XML were all included in the one workbook it does not need to be context sensitive. The custom ribbon tab will disappear when another workbook it activated.
  • This project utilized the "splash screen" methodology for forcing the user to enable macros. Before the workbook is saved all the worksheets are hidden and a worksheet that contains instructions on how to enable macros safely is unhidden. When the workbook opens, if macros are not enabled the user sees this splash screen and can follow the instructions there. If macros are enabled then the workbook's Open event handler unhides the operational worksheet and hides the splash screen.
  • The application also utilized a "program sheet". This makes it much easier for the person charged with maintaining the workbook to change or alter certain key aspects of the application without having to contact GabrAria and pay for common modifications in the future.
  • The Runsheet application offered a separate Print button. By using the custom print button, the application would create the printed output using a separate worksheet that had been optimized for printing instead of the default worksheet which had been optimized for screen display.
Project Stats
Total Lines 4,399
Code Lines 1,813
Comment Lines 2,586
Standard Modules 10
Class Modules 0
Userforms 0
Procedures 64
Deployment The workbook was deployed to SLS' landmen located throughout the United States.
Architecture For this project a simple architecture made the most sense.   The code remained attached to the Runsheet workbook and the central office maintained the foundation-copy of the workbook.   If any changes were needed, they were made to the central office's copy of the workbook and distributed as needed.
 Project Marketing Survey Analyses
Platforms Excel 2010 icon    Access 2010 icon
Client Overview Sony World Repair Parts Center (Americas)  [WRPC(AS)]   Located in Kansas City, Sony's WRPC(AS) was the service parts distrubution hub for North and South America for all Sony products including Broadcast and Professional product lines.  Additionally, the Kansas City facility served as the location for processing all the Product Registration / Marketing Surveys for Sony.
†  Please note that I did this project while employed by Sony.  Its inclusion here does not imply any endorsement of GabrAria or myself, Greg Truby, by Sony Electronics Inc.
Project Overview

Sony needed to transition from the traditional paper-based marketing surveys that were part of the Product Registration paperwork packaged with all new products to an internet-based survey & registration process.  However given the large variety of product lines coming from a large network of vendors, this process could not happen all at once.  Instead the transition was done one product group at a time. This meant that in the first year or so of the transition process Sony was still receiving hundreds of thousands of paper product registrations / marketing surveys each month.

Nonetheless, Sony wished to minimize the costs of maintaining the legacy processes.  Therefore they asked me to re-engineer the process of transferring the data from the paper surveys into Access. This meant that the table structures and relationships that had been used for the preceding twenty years were completely obsolete. All the tables' structures and relationships had to be redesigned to allow for a new much more efficient keying and extraction process.

Once the data entry process was re-engineered, I was then tasked with completed re-engineering the data extraction and analysis-building process with the aim to automate the process as much as possible.

Application Features
  • Used VBA to dynamically build the SQL strings needed to extract the data from the Access tables and create tables for import into Excel.
  • One of the most interesting aspects of this project was that the main application created workbooks that were themselves "applications", i.e. code writing code.

    After creating the analysis workbooks from scratch, the program would add controls to the surface of the worksheet and then append code modules to the workbook so that the controls were fully functional.

    no border
Results
Hours per Month: Marketing Analysis Process
Before Project: 704 (four full time headcount)
After Project: 6 to 8
Man-hour reduction: 99%
Architecture Code was located in an Excel Add-In and in various VBA modules in Access which were called from Forms in the database.
 Project You're Lookin' At It!
Platforms expression web 4 icon    expression design 4 icon
Client Myself
Project Overview To design and build a complete commercial website of a quality similar to those done by professional web designers.
Application Features
  • Professional quality vector graphics for the background images
    • The topmost graphics slice where the contact | about | ... menu is located.
    • The lower header graphics slice with the company logo, selected quotations and main menu.
    • The footer graphics slice with the copyright notice and my favorite quote.
  • A well designed set of pages and menu structure to make page access easy.
    • The main menu is well positioned.
    • Less-frequently-used pages are easily accessible through a secondary upper menu.
    • A third level of navigation support is available via on-page menu structures
  • The on-page menu structures implement a drop-down structure completely using CSS3 classes.
Deployment Worldwide via the internet.
Architecture
  • The website uses an Expression Web 4 Dynamic Web Template which facilitates giving all of the site's pages a consistent look and set of navigation tools. (While EW4 does offer a collection of canned DWT's in this case I designed and built by own template.)
  • Very little formatting is done via the style attribute on the HTML tags. Almost all layout is implemented "properly" using CSS3 and pages are all tested on the five major browsers to insure proper rendering.
    • Internet Explorer
    • Chrome
    • Firefox
    • Safari
    • Opera
  • Uses PHP to check for blank fields and valid e-mail addresses in quote and contact forms.   Provides feedback to users if required fields are missing or e-mail address is not valid and allows them to resubmit the form without having to re-fill in fields that were filled in correctly the first time.
This is my very first website! I built this website entirely from scratch. I did not use a template for any portion of this site.

I read several books to learn how to write HTML, CSS3 and how to use the Expression Web 4 web authoring software. I had never used any vector graphics software in my life and there are very few books on Expression Design 4, the vector graphics program I used to create all of the background graphics. I used online video tutorials and teach myself enough (it wasn't always pretty) about professional graphics software to create what you see in the headers & footers of this website.

I'm telling you all of this because by looking at this website, which is something I was able to do using tools I knew almost nothing about and writing in programming languages I just learned, it should give you an idea of what I can do in Excel, software I know quite well and using VBA, a language I programmed in extensively.


Greg Truby, owner, GabrAria LLC


Top