How to call a User Defined Function in code?

Question

I created a user defined function. I'm trying to call it in my macro.

When the code reaches the line with my function, I get a Run-time error

'438': Object doesnt support this property or method?

Function SMM(incentivebeta As Double, agebeta As Double, intercept As Double, incentive As Double, age As Double) As Double

    SMM = 1 / (1 + Exp(-(intercept + incentivebeta * incentive + agebeta * age)))

End Function

Private Sub btnSimulate_Click()
    Dim counter As Long
            For counter = 1 To 360
                ws.Range("start").Offset(counter, 7).Value = WorksheetFunction.SMM(1, 2, 3, 4, 5)

What is the name of the code module which holds SMM?

Mistella
– Mistella

2018-12-06 18:38:17 +00:00
Commented Dec 6, 2018 at 18:38 — Mistella
– Mistella, Commented Dec 6, 2018 at 18:38
its named "module2"

financedude
– financedude

2018-12-06 18:39:07 +00:00
Commented Dec 6, 2018 at 18:39 — financedude
– financedude, Commented Dec 6, 2018 at 18:39

John Coleman · Accepted Answer · 2018-12-06 19:02:44Z

2

The object which "doesn't support this property or method" is Worksheet Function and the method it doesn't support is SMM. You have defined that function, but that doesn't mean that the function you have defined is now a worksheet function. Just use SMM rather than WorksheetFunction.SMM.

Also, as Gary's Student points out, you will get an unrelated error if ws isn't properly set.

answered Dec 6, 2018 at 19:02

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Coleman Over a year ago

@financedude Glad I could help. If it solves the problem, you could always mark the answer as accepted (via the check mark next to the answer)

Gary's Student · Accepted Answer · 2018-12-06 18:54:10Z

1

You have neither Dim'ed nor Set the worksheet variant ws.

(there may be other errors in your code.)

answered Dec 6, 2018 at 18:54

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

1 Comment

John Coleman Over a year ago

That particular error would give an "object required" message. Assuming OP doesn't have ws as a global variable, the VBA interpreter encounters the "object doesn't support this method error" when evaluating the right hand side before it gets to the problem on the left hand side of the offending line.

Collectives™ on Stack Overflow

How to call a User Defined Function in code?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related