Custom Excel Functions: Hello, World

What is Least Squares Fitting?

Go look in D.C. Baird, Experimentation.

Also check out http://civil.colorado.edu/~saouma/Winkler/report/node41.html; the equations for the basic LS fit is derived there.

Start off simple

You can call any Excel function from within Visual Basic; just use

Application.WorksheetFunction.AnyExcelFunction(...)

We will need the Excel function SumSq, so let's practice with it now.  This function takes an array of values and returns the sum of the squares of each individual value.  For instance, SumSq(Array(1,2,3,4,5)) is 1400. 

Go ahead and enter the following code (how):

Option Explicit
'Demonstrates that you can call an Excel function from within VBA.
Function SimpleSumSq(Xarr As Variant) As Variant
'The Excel SumSq function
    '   takes an array of numbers, and
    '   returns the sum of the squares of each element in the array.
    SimpleSumSq = Application.WorksheetFunction.SumSq(Xarr)
End Function

Go back to Excel and set up a spreadsheet to test your new function.  You want to make an array of numbers, and then call SumSquares on that array.

Make the Array
Enter function:
Result:

Congratulations.

Move on to the Least Squares in Excel spreadsheets