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.

Why do you expect your function to be available under predefined Application.WorksheetFunction? If you put it in a module, simply call it t_value(theta). — GSerg
– GSerg, Commented Aug 25, 2018 at 15:52
That's just how I've recalled (predefined) functions in the past. I was not aware how it may be different for a module. Thanks for the help! — Kara Combs
– Kara Combs, Commented Aug 25, 2018 at 15:55
++ good quality first question :) cheers & welcome to StackOverflow. — David Zemens
– David Zemens, Commented Aug 25, 2018 at 17:10

David Zemens · Accepted Answer · 2018-08-25 17:18:16Z

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

object browser showing WorksheetFunction class

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.

PaichengWu · Accepted Answer · 2018-08-26 01:51:32Z

4

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

edited Aug 26, 2018 at 1:51

answered Aug 25, 2018 at 16:30

PaichengWu

2,6891 gold badge16 silver badges28 bronze badges

Collectives™ on Stack Overflow

How to call a user defined function in vba code

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related