Spreadsheet Models for Managers

Demonstrations for Session 14
Using Macros II

• Array Function Macros
• An Array Function Macro that Reorganizes an Array

Array Function Macros (2000-4) or (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 is 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 2003 or 2004, click the Visual Basic Editor toolbar button. It looks like this:. If you don't see this button in the toolbar, perhaps your Visual Basic toolbar is hidden. To view it, choose the menu command View>Toolbars>Visual Basic.

To view this macro in Excel 2007, use the ribbon command Developer>Visual Basic.

Once you've opened the Visual Basic editor, look for the Project Explorer window. If you don't see it, choose View>Project Explorer. In the Project Explorer, find the item "session14.xls". Open it by double-clicking (Mac) or right-clicking the plus sign(Windows). 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.