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
Demonstrations for Session 13
Using Macros I
We have one demonstration (2007+) for this session: How to Write Function Macros in VBA.
How to Write Function Macros in VBA (2007+)
[Sheet: Examples]
Here are four examples of simple function macros.
EOQ
The EOQ macro implements the formula for EOQ. You can view the macro source code as follows:
- In Excel 2011, expose the Visual Basic toolbar by right-clicking (Windows) or Ctrl+Clicking (Mac) any toolbar to expose the toolbar shortcut menu. Then select the Visual Basic toolbar. In Excel 2007+, click the Developer tab of the Ribbon.
- Click the Visual Basic editor tool or control, which appears as follows:
Excel 2007, 2010, and 2013 
Excel 2011 
- In the Visual Basic Editor, find the Project Explorer. If you don’t see it, click the Project Explorer tool button.
- Find the module called Examples in session13.xlsm.
The EOQ macro is a simple function macro, the first one in the module. It uses the VBA function Sqr to compute the square root. To find VBA functions, look in the Visual Basic on line help, for the volume called “Visual Basic Language Reference” and, within that, look for “Functions.” The work we’ll do with macros won’t require knowledge of VBA functions.
DoNothing
This macro illustrates how to return objects. As it happens, this macro does nothing. But if you want to return an object, the assignment statement must use “Set”.
GeometricMean
The geometric mean of two numbers is the square root of the product of the two numbers. This function macro implements it. Notice the choices of function names and argument names. Names can be any string of alphanumeric characters and underscores, starting with letters. Case doesn’t matter. In our example, we’ve chosen names that are designed to make the macro easy to understand, without the need for extensive commenting. Managers of groups of people who make spreadsheet models should ensure that function and variable names are chosen with these factors in mind.
Depreciation
Here’s an implementation of the depreciation schedule generator that supports the Ripple Principle. It’s analogous to what we did in the examples for Session 8. It uses Caller, a property of the Application object that holds the cell that invoked the function. It also uses Column, a property of a range object. Both of these property invocations illustrate how VBA uses postfix notation to access properties of objects. To test your understanding of Caller, write a worksheet function that returns twice the value of the cell to the right of the caller.
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?