Spreadsheet Models for Managers

Getting Access to Spreadsheet Models for Managers

If Spreadsheet Models for Managersyou 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:
  • For the download: USD 199.00
  • For access online for three months: USD 199.00
  • For access online for one month: USD 69.95
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 14
Using Macros II

We have two demonstrations (2007+) for this session:

Array Function Macros (2007+)
[Sheet: SimpleExamples]

Here are two examples of array function macros. The first returns the sum of two 3x3 arrays. The second returns an array that’s the result of multiplying an arbitrary rectangular range by a constant. Both are unnecessary in the sense that Excel can already do these things.

Adding two 3x3 arrays

To view VB editor controlthis macro in Excel 2007, 2010, and 2013, use the ribbon command Developer>Code>Visual Basic.

To view VB editor iconthis macro in Excel 2011, use the ribbon command Developer>Editor.

Once you’ve opened the Visual Basic editor, look for the Project Explorer window. If you don’t see it, choose View>Project Explorer (Excel 2007, 2010, 2011, and 2013); or click the Project Explorer toolbar button (Excel 2007, 2010, 2011, and 2013). In the Project Explorer, find the item “session14.xlsm” (Excel 2007+). Open it by double-clicking (Mac) or right-clicking (Windows) the plus sign. Then open its modules, and finally open the Example Module. You should then see the code for all macros in this demonstration. Scroll until you can see ArraySum.

To go back to the worksheet window, click the Excel icon at the left end of the VBA editor menu bar. Try some experiments of your own: add two 3x3 arrays.

Multiplying by a constant

Use the same technique as above to view the Multiply macro. Do some experiments with it too. Make a copy of the Multiply macro in the Examples module, and rename it to Exponent. Modify it so that instead of multiplying an array by a factor, it raises the array to a power. Test it.

An Array Function Macro that Reorganizes an Array (2007+)
[Sheet: Fittings]

This example is well explained in the class notes. In the worksheet, notice that we demonstrate that it works by comparing the result we get with VLineUp to a similar result obtained with index.

Last Modified: Wednesday, 27-Apr-2016 04:15:26 EDT

How to Measure the Value of a Function Macro

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.