Home / Support / Tech Tips

Tech Tip: Statistical Add-Ins for Excel - A Macro for Simplifying the Use of Array Functions

The Statistical Add-Ins for Excel have proved very popular amongst the Excel community. A unique selling point is the facility to automatically update results when data changes. This is achieved by delivering computed results as an Excel array function. Some users find that the standard Microsoft mechanism for using array functions is too complicated since users have to select all the cells they want to contain the results and also users must press 'control-shift-enter' rather than just 'enter'.

The macro below may be used to simplify this procedure. Simply call the NAG function as you would with a normal scalar function. When this macro is made available by opening the workbook containing the macro, call the macro from the Tools\Macro\Macros menu. The cell selected on the worksheet should contain the result from the NAG Add-In and look like <List of n>, where n is an integer. The macro will expand this scalar result to the full array result, using just the right number of worksheet cells.

To put this macro into a workbook click on the Tools\Macro\Visual Basic menus. In the Visual Basic window click on the Insert\Module menus. Paste the macro into this module and close the Visual Basic window. Save the workbook. If you want the macro to be available automatically whenever you use Excel then the workbook created should be called Personal.xls and should be located in the Office\Xlstart directory. This will be automatically opened whenever Excel starts. If you want to hide this workbook then it may be hidden using the Hide option in the Windows menu.

Sub Expand()

Dim s As String, t As String
Dim n As Long

End Sub

For more detailed product information on NAG's Statistical Add-Ins for Excel, please see www.nag.com/ae.

For specific technical advice in using NAG's products, please contact our technical experts.

Return to Technical Tips & Hints index page.