Custom Functions in VBA

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)

Advertisements

About Jennifer Phillips Campbell

Software Developer and Medieval Historian
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s