Create a custom worksheet function in Excel VBA

Tomalak picture Tomalak · Apr 16, 2009 · Viewed 47.8k times · Source

I have a faint memory of being able to use VBA functions to calculate values in Excel, like this (as the cell formula):

=MyCustomFunction(A3)

Can this be done?

EDIT:

This is my VBA function signature:

Public Function MyCustomFunction(str As String) As String

The function sits in the ThisWorkbook module. If I try to use it in the worksheet as shown above, I get the #NAME? error.


Solution (Thanks, codeape): The function is not accessible when it is defined ThisWorkbook module. It must be in a "proper" module, one that has been added manually to the workbook.

Answer

codeape picture codeape · Apr 16, 2009

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 Explicit

Function MyCustomFunction(input)
    MyCustomFunction = 42 + input
End Function
  • Switch back to worksheet (Alt-F11), and enter some values:
A1: 2
A2: =MyCustomFunction(A1)