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.
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:
Option Explicit Function MyCustomFunction(input) MyCustomFunction = 42 + input End Function
A1: 2 A2: =MyCustomFunction(A1)