I had a nostalgic kind of week this week, working with VBA to create a custom function to apply some business logic to a calculation in a spreadsheet. I got into programming through working with VBA macros, and still find myself defaulting to VBA syntax when I’m very tired even though I haven’t worked regularly with VBA for over four years. It felt a bit like driving a horse and cart after driving a car (and, yes, I have driven both), but was useful for the task in hand.

Anyway, a useful guide to creating a user-defined function in a module in a workbook can be found in Microsoft’s online Office support. You can access most of the Excel worksheet functions in VBA using the WorksheetFunction object and you can pass in ranges e.g.

Function Score(r As Range)
    If WorkSheetFunction.Sum(r) >= 100 Then
        Score= 1
    Else
        Score = 0
    End If
End Function

(see here for more examples of using the WorksheetFunction object)

You can access custom functions which are saved in another workbook (open in the same instance of Excel) by prefixing the function with the workbook name e.g.
Functions.xls!Score(A1:A10)