Spreadsheet Models for Managers
Getting Access to Spreadsheet Models for Managers
If 
you use Excel to model businesses, business processes, or
business transactions, this course will change your life. You’ll learn how to create tools for yourself that will amaze
even you. Unrestricted use of this material is available in two ways.
- As a stand-alone Web site
- It resides on your computer, and you can use it anywhere. No need for Internet access.
- At this Web site
- If you have access to the Internet whenever you want to view this material, you can purchase on-line access. Unlimited usage. I’m constantly making improvements and you’ll get them as soon as they’re available.
To Order On Line
| Order "Spreadsheet Models for Managers, on-line edition, one month" by credit card, for USD 69.95 each, using our secure server, and receive download instructions by return email. |
| Order "Spreadsheet Models for Managers, on-line edition, three months" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email. |
| Order "Spreadsheet Models for Managers, downloadable hyperbook edition" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email. |
To Order by Mail
Make your check payable to Chaco Canyon Consulting, for the amount indicated:
|
And send it to: Chaco Canyon Consulting 700 Huron Avenue, Suite 19C Cambridge, MA 02138 |
To use the course software you’ll need some other applications, which you very probably already have. By placing your order, you’re confirming that you have the software you need, as described on this site.
Spreadsheet Models for Managers
This reading is especially relevant for Sessions 13 and 14Excel Macros in Visual Basic for Applications
This section isn’t a complete description of VBA for Excel. Rather, think of it as a cookbook for writing Excel function macros in VBA. Command macros are another story for another time. Here are the topics we’ll look at:
- Why write a function macro?
- The ideal candidate for a function macro
- The basic structure of a function macro
- Variable declarations
- Assignment statements
- Returning values from function macros
- Statements you need for computations
- Arrays
- Invoking Excel worksheet functions
- VBA modules in Excel
- A simple development tool
- References
Why write a function macro?
Writing macros is usually fun for the people who write them, though some say that it takes a special kind of person to get off on this stuff. But while it might be fun for some people, for organizations, it’s pure pain. Since not everyone enjoys it or does it well, an organizational decision to invest in macros usually means betting heavily on a few people, or bringing in expensive consultants or contractors. Since the organization can develop a dependency on these scarce resources, the decision to invest in macros must be taken seriously.
There are four characteristics of situations in which function macros provide significant advantages that justify the organizational pain. Understanding these characteristics is much easier if we understand the concept of a spreadsheet cliché. A spreadsheet cliché is a structure that we use repeatedly. For example, we often compute running sums of the line directly above. Typical uses of this cliché are calculation of total headcount given a hiring stream, or annual revenue given a stream of monthly revenue. With this in mind, let’s examine some of the reasons for writing function macros.
Lower maintenance costs
If you have a set of spreadsheet assets that must be maintained over a significant period of time, you can lower maintenance costs significantly by identifying clichés in those assets and re-implementing them in Visual Basic. Thereafter, you’ll find reductions in the total number of cells used and the total number of formulas. You can also expect significant reductions in the Fault Feedback Ratio (FFR), which measures the number of faults generated by repairing a fault. The Fault Enhancement/Feature Ratio (FEFR), a measure of faults generated in new development or in enhancements, is also reduced.
Improved readability
When you can capture in a single macro a complex cell-gymnastic routine, you elevate the thinking of anyone working with the spreadsheets that use it. Good candidates for function macros are manipulations that require intermediate displays not for purposes of visibility or inspection, but simply because you can’t figure out how to perform the computation in a single cell or array formula. Even when you can express the calculation in a single formula, that formula can be very long. If it wraps in the formula bar, it can be difficult to read. Sometimes it’s wise to reduce such an expression to a simple call to a function macro.
When you improve readability in this way, everyone finds it easier to read and understand the spreadsheet. This helps reduce errors, lowers costs, and reduces your dependency on the author of the work.
<snip>…
Some of the page you’ve been reading has been suppressed from this sampler. To see the complete version of this article, why not order the full course? It’s available at this Web site or in downloadable format.
| Order "Spreadsheet Models for Managers, on-line edition, three months" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email. |
| Order "Spreadsheet Models for Managers, downloadable hyperbook edition" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email. |
…<end snip>
You can assign any legal name to your module, but there is one word of caution: the module’s name must differ from the names of all of its function macros. If the module’s name is the same as the name of one of its function macros, Excel gets confused.
The Visual Basic Editor pesters you every time you make a mistake, which can be annoying. To turn this off in Excel 2011:
- Choose the menu command Excel>Preferences…
- On the Editor tab of the Preferences dialog, turn off Auto Syntax Check
To turn this off in Excel 2007, 2010, and 2013 for Windows:
- Choose the menu command Tools>Options…
- On the Editor tab of the Options dialog, turn off Auto Syntax Check
Visual Basic Editor options
You have some additional control over the appearance of code in the editor, including font, font size, and colors. We recommend the Consolas (Western) font.
To set this in Excel 2011:
- Choose the menu command Excel>Preferences…
- On the Editor tab of the Preferences dialog, turn off Auto Syntax Check
To set this in Excel 2007, 2010, and 2013 for Windows:
- Choose the menu command Tools>Options…
- On the Editor Format tab of the Options dialog, choose your font.
The Visual Basic comment character is single-quote ('). At times, you might want to “comment out” several lines at once. Perhaps you have an extended comment that takes multiple lines, or perhaps you want to replace a piece of code with something else, but you want to retain it temporarily as you experiment with the new code. Excel in its Windows versions does have an automatic way to do this, but it isn’t visible by default in Windows. It is available by default in the Mac versions. We recommend that you customize the standard toolbar of your Visual Basic Editor to include the Comment Block tool and the Uncomment Block tool, to make these operations more convenient. Here’s how: Click Right on any toolbar in the Visual Basic Editor. Choose Customize… from the context menu that appears. The Customize dialog appears. Click the Commands tab. In the Categories box, click Edit. Then scroll the Commands box until you find the two commands Comment Block and Uncomment Block. One by one, drag them onto the Standard Toolbar. Click Close to close the Customize dialog.
Module options
Module options are directives to the VBA compiler. They’re somewhat technical, but fortunately we need only two. One tells the compiler to require variable declarations, and the other tells the compiler to number array indices starting at 1 instead of 0. Include these lines at the top of every module:
Altogether, a complete module might look like the example below, which is a function macro that multiplies two ranges, cell by cell. Of course, you would never have to write such a macro, because Excel can perform this operation as array arithmetic. And since it contains no checking for identical shape and size of its two arguments, it’s not very safe to use. But here it is:
A simple development tool
Sometimes, you write a macro, and it looks correct, but it gives you unexpected results. In this kind of situation, you need a debugging tool. The Visual Basic Editor has a few utilities that help, including breakpoints. Look at the entries for “debugging” in on line help for the Visual Basic Editor.
One very useful technique is to insert in your code a statement that displays the value of some critical variable. The syntax of that statement is
There are a variety of debugging tools in the VBA editor. If you plan to write more macros, it would be a good idea to learn about them. Using on line help, find the Debug tool bar for the VBA editor, and experiment.
If you find yourself managing people who are to write or maintain VBA macros, you might need ways of evaluating their technical competence. Assessing their abilities with respect to debugging and debugging tools is a good way to do this, because it helps identify people with real experience writing macros. So even if you don’t plan to become a macro expert yourself, expertise in the debugging environment can be useful in managing, evaluating and hiring people who do.
References
- Bill Jelen and Tracy Syrstad. . Indianapolis, Indiana: Que Publishing, 2010.
- Bill Jelen and Tracy Syrstad. . Indianapolis, Indiana: Que Publishing, 2013.
- John Walkenbach. . New York: Wiley, 2010.
- John Walkenbach. .
New York: Wiley, 2013.
- The above are a whole lot more than you need for this course. Don’t even think of looking at them unless you want to dive into programming. But if you want to, they’re solid references.
- On line help for VB takes some getting used to, but it’s serviceable.
Last Modified: Wednesday, 27-Apr-2016 04:15:26 EDT
We focus on function macros in this course because they’re more likely than command macros to make a real difference in your facility with constructing models. For instance, when your customer wants to see result streams displayed as [Month1, Month2, Month3, Q1 Total, Month4, Month5, Month6, Q2 Total, …], you probably realize that such a layout makes copy/paste and fill very inconvenient. A macro can provide a simple means of producing the preferred layout from a more easily maintained pure month structure. It’s also easy to construct macros for running sums and running differences. Can you think of other applications for function macros that make your models easier to build and maintain?
The value of a function macro increases with frequency of use, the complexity of the calculation it performs, and the area of the result it returns. As you examine the computations you perform routinely in your work, make note of those computations that meet these criteria. Before committing yourself to writing a macro to carry out one those computations, try various methods for implementing it using standard built-in worksheet functions. That effort might clarify for you the kernel of the computation that benefits most from a macro-based approach.