在Excel VBA中创建一个自定义工作表函数

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

我对使用VBA函数在Excel中计算值有一种模糊的记忆，如这样(作为计算单元公式):

=MyCustomFunction(A3)

Can this be done?

这个可以做吗?

EDIT:

编辑:

This is my VBA function signature:

这是我的VBA函数签名:

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.

函数位于这个工作簿模块中。如果我尝试在工作表中使用它，如上所示，我将得到#NAME?错误。

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.

解决方案(谢谢，codeape):在定义这个工作簿模块时，无法访问这个函数。它必须是在一个“适当的”模块中，一个已经手动添加到工作簿的模块。

2 个解决方案

#1

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.

是的,它可以。只需在模块中定义VBA函数。请参见http://www.vertex42.com/excelarticles/user - definedfunctions.html。

Here's a simple example:

这是一个简单的例子:

Create a new workbook
新建一个工作簿
Switch to VBA view (Alt-F11)
切换到VBA视图(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:
切换回工作表(Alt-F11)，并输入一些值:

A1: 2
A2: =MyCustomFunction(A1)

#2

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.

输入字需要替换，因为它是一个基本的关键字。尝试num代替。您还可以进一步指定类型(如变量)。

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

#1