Page 73 - Excel for Scientists and Engineers: Numerical Methods

50 EXCEL: NUMERICAL METHODS

Function MyLINEST(known-ys, known-xs)
Dim Results(3)
code to calculate slope, intercept and R-squared
Results( 1) = MySlope
Results(2) = Mylntercept
Results(3) = MyRSq
MyLlNEST = Results
End Function
Figure 2-19. A Function procedure that returns an array of results.

A second approach is to use the Array keyword. The Array function returns
a variant that contains an array.

Function MyLINEST(known-ys, known-xs)
code to calculate slope, intercept and R-squared
MyLINEST = Array(MySlope,My Intercept, MyRSq)
End Function

Figure 2-20. Using the Array keyword in a Function procedure.
The Array keyword can accommodate only a one-dimensional array. To use
this approach to return a two-dimensional array of results, you must create an
array of arrays, as illustrated in Figure 2-2 1. Both arrays must contain the same
number of values.

code to calculate slope, intercept, R-squared,
std dev of slope, std dev of intercept, std error of y values.
MyLINEST2 = Array(Array(MySlope, Mylntercept, MyRSq), -
Array(stdev-m, - stdev-b, SE-y))
End Function

Figure 2-21. Using the Array keyword to return a 2-D array.

Creating Add-In Function Macros
Saving a custom function as an Add-In is by far the most convenient way to
use it. Here are some of the advantages:
An Add-In custom function is listed in the Paste Function list box
without the workbook name preceding the name of the function,
making it virtually indistinguishable from Excel's built-in functions.
If the Add-In workbook is placed in the AddIns folder, the Add-In will
be available every time you start Excel.