Excel, writing your own functions is easier than you think

Annoying isn’t it that excel cannot compute d prime or standard Error ‘in one fell sweep’. The solution, write your own function 🙂


Writing your own functions

(some text originates from this site and this site)

  1. Open up a new workbook.
  2. Get into VBA (Press Alt+F11)
  3. Insert a new module (Insert > Module)
  4. – Copy and Paste the Excel user defined function examples –
  5. Get out of VBA (Press Alt+Q)
  6. Use the functions (They will appear in the Paste Function dialog box, Shift+F3, under the “User Defined” category)

If you want to use a UDF in more than one workbook, you can save your functions in your own custom add-in. Simply save your excel file that contains your VBA functions as an add-in file (.xla). Then load the add-in (Tools > Add-Ins…). Warning! Be careful about using custom functions in spreadsheets that you need to share with others. If they don’t have your add-in, the functions will not work when they use the spreadsheet.

Benefits of User Defined Excel Functions

  • Create a complex or custom math function.
  • Simplify formulas that would otherwise be extremely long “mega formulas”.
  • Diagnostics such as checking cell formats.
  • Custom text manipulation.
  • Advanced array formulas and matrix functions.

Useful additional stuff

By inserting the code – “Application.Volatile”, the function will re-calculate just like an in-built function. This should overcome the problem mentioned in the paragraph entitled Warning. For example:

Application.Volatile

 

Example: d’

Function Dprime(Hit As Double, Falarm As Double)

Dprime = WorksheetFunction.NormSInv(Hit) – WorksheetFunction.NormSInv(Falarm)

End Function

 

Example: standard error

Function StdErr(k As Range)

StdErr = (WorksheetFunction.StDev(k)) / Sqr(WorksheetFunction.Count(k))

End Function

Leave a Reply

Your email address will not be published. Required fields are marked *