MAKE!
Post date: Oct 23, 2017 6:33:12 PM
Assemble nearly completed projects or update existing projects with most recent component versions in seconds with BXL's newest free (at this time) utility MAKE!
MAKE is aimed at advanced, or aspiring to be advanced VBA developers. Inspired by the Unix Make utility, MAKE is built entirely in Excel and automates the process of managing component inventory lists and project assembly lists. From those lists MAKE assembles nearly completed Excel projects to which we add whatever custom configuration, or, in some cases, custom code to finish our project.
Why 'Advanced' Developers?
Beginners code from scratch which is a lengthy and error prone process.
Intermediate developers start with a finished project and modify it to create what they need. That's faster and has fewer errors.
Advanced developers maintain libraries of pre-built, pre-tested components and assemble those components into finished projects. This is much faster and far less error prone especially as we discover better ways to do things and update our components with the latest and greatest methods and features. MAKE automates the assembly process for us but it can't automate assembly if we don't have reusable components to assemble.
How to Create Re-usable Component Libraries
If you aren't yet that advanced, here is a basic strategy to get there:
Analyze your existing models and look for what worksheets they all have in common: Inputs, Processes, Outputs, Documentation and Navigation. This can become a generic model structure foundation.
Categorize Inputs and group them into separate tables for reuse. Categories can include:
Single attributes (like model start, number of periods, etc) under user control
Repeating values (like attributes for multiple loans; products or customers) under user control
Constants (Like periods per year) under the modeler's control
Configuration (like which worksheets users can navigate to) under the modelers control
And more...
Categorize Processes and group them into separate tables for reuse. Categories can include:
Model calendar with periods and dates tied to user inputs of start date a number of periods
Project phases like construction and post construction
Production projections
Revenue projections
Expense projections
And more...
If using VBA, think about how you want to organize your code. I organize my code by:
General Purpose Routines
Web Routines
Outlook Routines
Charting Routines
Database Inquiry Routines
Database Update Routines
User Form Support Routines
And more...
Place 'like' routines in modules or class modules
Replace all hard-coded values inside routines with variables. Make those variables routine parameters
If you need examples just look at MAKE and the zip that comes with MAKE. They contain several BXL libraries and a sample directory structure for easy maintenance.
How to get MAKE!
Download the MAKE instructions (PDF). In the PDF are instructions on how to download MAKE's Zip file.
CAVEAT!
MAKE has not been tested in the MAC environment and probably does not work there. Sorry.
Want to be notified when we offer more free tools? Like our Facebook page. Find bugs or have suggestions? Tell us on Facebook: facebook.com/BeyondExcel