Create a custom worksheet function in Excel VBA Create a custom worksheet function in Excel VBA vba vba

Create a custom worksheet function in Excel VBA


Yes it can. You simply define a VBA function in a module. See http://www.vertex42.com/ExcelArticles/user-defined-functions.html for a nice introduction with examples.

Here's a simple example:

  • Create a new workbook
  • Switch to VBA view (Alt-F11)
  • Insert a module: Insert | Module
  • Module contents:
Option ExplicitFunction MyCustomFunction(input)    MyCustomFunction = 42 + inputEnd Function
  • Switch back to worksheet (Alt-F11), and enter some values:
A1: 2A2: =MyCustomFunction(A1)


The word input needs to be replaced as it is a basic keyword. Try num instead. You can also go further by specifying a type, eg variant.

Function MyCustomFunction(num As Variant)    MyCustomFunction = 42 + numEnd Function


matomo