The NAG Statistical Add-Ins have a unique ability to update the computed solutions whenever the data changes. They achieve this goal by returning the solution to statistical problems as an array function. In normal usage a utility macro, NAGExtract, may then be used to re-position the output in other parts of the workbook.
This approach is very flexible and appeals to many users. The price paid for this powerful capability is twofold:
- The user needs to know how to enter array functions (Control+Shift+Enter)
- A long chain of dependencies can result in too much unnecessary computation
In this article we describe how, with modest programming skills, these problems may be resolved.
Macros do not update the solution when data they have used as input is changed. We may use this fact to put small wrappers around the NAG Add-Ins to expose the NAG statistical capability in macro form. This approach allows the output to be tailored exactly to the users wishes and, of course, suppresses the automatic updating of results whenever the data changes. (If that were the only problem then of course this may be suppressed via the Tools/Options menu in Excel.)
Select the Tools/Macro/Visual Basic menu and use Insert/Module to create a module sheet. Enter the wrapper code in this sheet. As we will wish to use the NAG Add-Ins we need to reference these. We do this via the Tools/References menus of the module sheet. Use the Browse button to locate the NAG Add-Ins. You will need to search for all files and locate the NAG *.xla files. These are frequently in the Microsoft Office\Office\Library directory. Ensure that the relevant NAG books are referenced.
This simple act enables you to place a small wrapper around a NAG function. Consider the example below:
Sub mysummary() Dim myArray As Variant Dim myRange As Range Dim Mean As Double, StandardDeviation As Double Dim Minimum As Double, Maximum As Double Set myRange = Selection myArray = summary_stats(myRange) If Left$(myArray(1, 1), 1) = "<" Then Mean = myArray(5, 1) StandardDeviation = myArray(7, 1) Minimum = myArray(13, 1) Maximum = myArray(15, 1) Cells(1, 4).Value = "The mean is " & Str$(Mean) Cells(2, 4).Value = "The standard deviation is " & Str$(StandardDeviation) Cells(3, 4).Value = "The minimum is " & Str$(Minimum) Cells(4, 4).Value = "The maximum is " & Str$(Maximum) Else MsgBox myArray(1, 1), vbCritical, "An error has occurred" End If End Sub
This code allows "mysummary" to be called from the Tools/Macros/Macro menu of the worksheet. It has been written to take data that has been pre-selected from contiguous cells of the worksheet, placing these in the range variable myRange via the command:
Set myRange = Selection
The NAG Add-In summary_stats may then be called directly from the code with myRange as the mandatory argument:
myArray = summary_stats(myRange)
We need to check that the add-in has worked. We do this by testing that the first character of the output is a "<". If we have successfully computed a solution then we have all the information we want in the variant myArray.
It remains to extract the pieces that interest us and output the information appropriately. In our example we take the mean, standard deviation, minimum and maximum:
Mean = myArray(5, 1) StandardDeviation = myArray(7, 1) Minimum = myArray(13, 1) Maximum = myArray(15, 1)
There are two points to notice in this code. Firstly the variant myArray may be accessed with subscripts, just as if it were an array, which role it fulfills on successful exit. Secondly please note that the array has two subscripts. The second of these is always 1 and is present to ensure that our Add-In output is produced as a column vector.
This completes our objective. The NAG Statistical Add-Ins have been used as computational building blocks to provide statistical capability in an easy-to-use macro form of our own design. The programming effort has been minimal and by using macros we have removed the need for users to enter array functions and taken away the automatic updating of results whenever the data changes.
We have demonstrated that the NAG Statistical Add-Ins are not only a valuable product in their own right, but a very powerful building block for those Excel programmers wanting reliable statistical calculations in a specific format of their own design.
For specific technical advice in using NAG's products, please contact our technical experts.
Return to Technical Tips & Hints index page.