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