Easy VBA Question? Assign a value and end function If ....

[MNpoker]

I have a public function (to find the PDF of an Exponential in this case) and if the X value is outside the area of the curve I want to send back a value of 0 then have the function end.

I am getting a Value# error instead.

HELP!!! and thanks

Public Function CurveEXPPDF(A, DefineCurve As Range)

' The Probability Density Function of a Exponential

'Put in the values for the specific curve
Shift = DefineCurve(1)
Maximum = DefineCurve(3)
Theta = DefineCurve(4)
'Determine if the curve is outside the allowed parameters (Less than the shift or greater than the Maximum)
If A < Shift Then CurveEXPPDF = 0: End <-- If a number lower than the 'shift' is selected I want to return a value of zero
If A > Maximum Then CurveEXPPDF = 0: End
' Get how far into the actual curve the X value is (adjusting for the shift)
X = A - DefineCurve(1)

CurveEXPPDF = Exp(-X / Theta) / Theta

End Function

 

[Greg Truby]

Have you used the F8 key in the VBE to step through this? I didn't test this but in eyeballing the code, <strike>I would guess that your use of colons for multi-lining here is probably the problem. The END statements would always execute. You'd want to put them inside an IF/END-IF pairing. </strike>Furthermore, I would use Exit Function rather than END since END halts all execution, I tend to use if very sparingly.

Edited - I just learned something -- on singe-line IF statements, statements that follow a colon are dependent on the IF's logic. How 'bout that.

 

[Norie]

Have you tried stepping through the code?

What are you tryin to do here?

Shift = DefineCurve(1) 
Maximum = DefineCurve(3) 
Theta = DefineCurve(4)

 

[MNpoker]

The function works fine as long as I am between the Shift and the Maximum.

Have you tried stepping through the code?

How do I do a step through on a Public Function?

What are you tryin to do here?

Shift = DefineCurve(1) 
Maximum = DefineCurve(3) 
Theta = DefineCurve(4)

This is where I have the parameter for the curves. To keep things organized I want to keep the same definitions for all of the curves I create, Lognormal, Pareto, etc.

But when I get into the function it helps me to read the code if I switch it back to the base parameters.
'General Notes
' Define Curve has the parameters for an exponential:
' 1 - Shift
' 2 - Minimum (Not applicable)
' 3 - Maximum
' 4 - Parameter 1 (Theta)
' 5 - N/A
' 6 - N/A
' 7 - N/A

I'll also be creating 'mixed' curves once I get the base ones completed.

 

[Von Pookie]

Seems to me it's simply the "End" statement throwing it off.

If I edit the code so that it runs to the end of the function without exiting early, I don't get any errors:

If A < Shift Then
    CurveEXPPDF = 0
ElseIf A > Maximum Then
    CurveEXPPDF = 0
Else
    ' Get how far into the actual curve the X value is (adjusting for the shift)
    X = A - DefineCurve(1)
    CurveEXPPDF = Exp(-X / Theta) / Theta
End If

 

[MNpoker]

Seems to me it's simply the "End" statement throwing it off.

If I edit the code so that it runs to the end of the function without exiting early, I don't get any errors:

If A < Shift Then
    CurveEXPPDF = 0
ElseIf A > Maximum Then
    CurveEXPPDF = 0
Else
    ' Get how far into the actual curve the X value is (adjusting for the shift)
    X = A - DefineCurve(1)
    CurveEXPPDF = Exp(-X / Theta) / Theta
End If

Thanks!!!!

I guess I din't understand how to nest the Else If statements.

P.S. This could get messy? as my curves get larger (mixed, finding conditional values, etc.)
Is there a way to end a public function early if I want to?

 

[Norie]

To step through a function you should set a breakpoint (F9) somewhere in the code.

Then when you use the function in a worksheet you should be able to step through it.

To exit a function just use Exit Function.

 

[MNpoker]

Ah so I could use 'Exit Function' in place of End in my original code.

-- I'll figure out that (F9) thing soon.

Thanks

And as always thanks for the excellent and prompt responses!!

 

[Norie]

Sorry for being a bit vague about F9.

Just put the cursor on the line of code you are interested in and you cang toggle a breakpoint on/off by pressing F9.

The line with the breakpoint is where code execution should stop.