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 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 
this macro in Excel
2007, 2010, and 2013, use the ribbon command Developer>Code>Visual Basic.
To view 
this 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
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.