如何在vba代码中调用用户定义的函数

I created a Public function in Module two called "t_value". I now want to use this function in the VBA code for a userform, which uses the input from the userform.

我在模块二中创建了一个名为“t_value”的Public函数。我现在想在用户表单的VBA代码中使用此函数,该用户表单使用userform的输入。

This is the function:

这是功能:

Public Function t_value(theta As Variant)
    Dim theta_1 As Integer, theta_2 As Integer
    Dim A As Variant, B As Variant, s As Variant

    theta_1 = Application.WorksheetFunction.Floor(theta, 5)
    theta_2 = Application.WorksheetFunction.Ceiling(theta, 5)
    A = theta - theta_1
    B = theta_2 - theta_1
    s = A / B
    t_value = s

End Function

Here is the code I would like to use the function above in:

以下是我想使用上述函数的代码:

Private Sub Submit_Click()
    Dim theta As Variant, alpha As Variant, t As Variant, u As Variant

    theta = UserForm1.theta_input.Value
    alpha = UserForm1.alpha_input.Value
    t = Application.WorksheetFunction.t_value(theta)

End Sub

Normally "Application.WorksheetFunction.[function]" works, but it wouldn't work for me in this situation - I thought it may be due to the fact I created the formula. Would it be easier to just put the formula into the Sub? I was worried about runtime. I'm rather new, so I'm not completely familiar with VBA syntax.

通常“Application.WorksheetFunction。[function]”有效,但在这种情况下它对我不起作用 - 我认为这可能是因为我创建了公式。将公式放入Sub中会更容易吗?我担心运行时。我比较新,所以我对VBA语法并不完全熟悉。

2 个解决方案

#1

Application.WorksheetFunction is a class defined in the Excel library; you can find it in the Object Browser (F2):

Application.WorksheetFunction是Excel库中定义的类;你可以在对象浏览器(F2)中找到它:

A public Function in a standard module is just a function that can be invoked from a worksheet cell (provided it doesn't have side-effects), just as well as from anywhere in the workbook's VBA project: you can't write any VBA code that "becomes a member" of a class that's defined in a library you're referencing.

标准模块中的公共函数只是一个可以从工作表单元格调用的函数(前提是它没有副作用),就像在工作簿的VBA项目中的任何位置一样:您无法编写任何VBA代码“成为您正在引用的库中定义的类的成员”。

So if you have a function called MyFunction in a module called Module1, you can invoke it like this:

因此,如果在名为Module1的模块中有一个名为MyFunction的函数,则可以像这样调用它:

foo = MyFunction(args)

Or like this:

或者像这样:

foo = Module1.MyFunction(args)

So in this case:

所以在这种情况下:

t = t_value(theta)

Would it be easier to just put the formula into the Sub?

将公式放入Sub中会更容易吗?

Nope, because a Sub won't return a value (however, you can pass variables ByRef):

不,因为Sub不会返回值(但是,你可以传递变量ByRef):

Sub t_value(theta as variant, ByRef t as Variant)

Dim theta_1 As Integer, theta_2 As Integer
Dim A As Variant, B As Variant, s As Variant

theta_1 = Application.WorksheetFunction.Floor(theta, 5)
theta_2 = Application.WorksheetFunction.Ceiling(theta, 5)
A = theta - theta_1
B = theta_2 - theta_1
s = A / B
t = s   '## Assign the value to the ByRef 't' variable and it should retain its value in the calling procedure
End Sub

Whether you choose to put this function in a module (Public) or in the user form module is a design decision that depends on whether you want the function to be generally available outside of the form instance(s). Whether you choose to make this function a sub is a bit different -- I'd probably recommend against it following the general best practice that Functions should return values and Subroutines should just perform actions and/or manipulate objects.

是否选择将此函数放在模块(公共)或用户表单模块中是一个设计决策,取决于您是否希望函数通常在表单实例之外可用。你是否选择使这个函数成为一个sub有点不同 - 我可能会建议不要遵循函数应该返回值的一般最佳实践,而子例程应该只执行操作和/或操作对象。

#2

Directly use t = t_value(theta) , instead of t = Application.WorksheetFunction.t_value(theta)

直接使用t = t_value(theta),而不是t = Application.WorksheetFunction.t_value(theta)