I finally broke down and wrote my own function for root mean square (RMS). This is an extremely common statistical function that Excel (2003 in my case) does not have. Usually I just type the following code
=SQRT(SUMSQ(A:A)/COUNT(A:A))
I figured writing this function would be very simple. It wasn't too hard but was not very intuitive for someone who had never done it before. So here's how it's done.
Phase 1 - Write Function
1. Go to Tools > Macro > Visual Basic Editor; or press ALT+F11.
2. Once in the Microsoft Visual Basic window got to Insert > Module.
3. In the Module window, type the code for your function. For example, here is my simple function:
Function RMS(values)"Function" tells VBA it's not a subroutine or macro. "RMS" is the name of the function and you must end the function with the function name being equal to desired output value. For me it was just one simple line. The reason I have words and periods (library names) before the Excel normal Excel functions I used is discussed below. "End Function" is auto-generated by VBA. Also, comments are any text after the apostrophe (') on a line. I have three comment lines that I used to make thing look clean and describe my function a bit.
'
' This computes the root mean square of entered values
'
RMS = Sqr(WorksheetFunction.SumSq(values) / WorksheetFunction.Count(values))
End Function
Phase 2 - Saving as Add-In
Note: Follow these steps only if you want your new function in a new custom add-in. If you want your function in one of the add-ins you previously made, simply drag your new module into the modules folder of your existing add-in project (mine is called "VBAProject (Donald Add-Ins.xls)". The projects are in the "projects" window which can be viewed by View > Project Explorer (or CTRL+R).
1. Go to File > Save
2. Click the "Save as type:" drop down and select "Microsoft Office Excel Add-In (*.xla)".
3. You will be directed to your computer's "AddIns" folder. Simply type a descriptive name for your custom add-in functions (i.e. "Donald Add-Ins").
Interestingly enough you will not see your new add-in project directory structure in the Project Explorer window until after the new add-in is actaully "added in". You don't have to worry since it will be there when you go back to Excel. If, however, you'd like to see it leave VBA open go back to Excel and follow the below steps. When you're done, go back to VBA and you'll see it.
Phase 3 - Selecting the Add-In and Using the New Function
1. Go to Tools > Add-ins...
2. Check the box to the left of your new add-in, then click OK
3. To use the function simply type its name as you would any normal Excel function with the values in parentheses (i.e. RMS(A:A) was my function).
A couple other things I struggled with in writing the function:
1. You can't type functions in VBA exactly as you would in Excel. To see available function go to View > Object Browser (or press F2). You can do some basic searches, but it's not perfect. For example, you won't find SQRT (the square root function) in the search. But an internet search helped me find the Excel's SQRT is VBA's SQR (no idea why they did that - but Math details are here: http://msdn2.microsoft.com/en-us/library/thc0a116.aspx). Otherwise I was able to find COUNT in the Object Browser search just fine.
2. You must tell VBA from which library to pull the object you're specifying. If you don't you see this error "Compile Error: Sub or Function not defined".
click below picture to enlarge...
You can find the libraries for the objects in the above-mentioned Object Browser. Find your object then click on it and the library will be at the bottom. For example,
Function Sqr(Number As Double) As Double
-> Member of VBA.Math <- or Function SumSq(...) As Double -> Member of Excel.WorksheetFunction <-
So you know that SQR is a member of the "Math" libray. However, since that library is already in VBA it will be recognized simply by "Sqr" without a library declaration. However for the SumSq function you need to tell VBA where this library is since it's an Excel library. The way you tell VBA this information is when using the object in your function simply type the name of the library before the name of the object with a period in between (i.e. WorksheetFunction.SumSq()).
Here is a good website on building functions and add-ins. It may be more helpful than my directions:
http://www.fontstuff.com/vba/vbatut03.htm
Some other good discussions:
http://www.eng-tips.com/viewthread.cfm?qid=91843&page=9
http://www.vertex42.com/ExcelArticles/user-defined-functions.html
http://office.microsoft.com/en-us/excel/HA010548461033.aspx?pid=CL100570551033
