How to call a user defined function in vba code

Question

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.

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.

Answer 1

Application.WorksheetFunction is a class defined in the Excel library; you can find it in the Object Browser (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.

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

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?

Nope, because a Sub won't return a value (however, you can pass variables 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.