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

#### MNpoker

##### Board Regular
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

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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. Have you tried stepping through the code?

What are you tryin to do here?
Code:
``````Shift = DefineCurve(1)
Maximum = DefineCurve(3)
Theta = DefineCurve(4)``````

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

Norie said:
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?
Code:
``````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.

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:

Code:
``````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``````

Von Pookie said:
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:

Code:
``````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?

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.

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!!

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.

Replies
0
Views
173
Replies
3
Views
1K
Replies
1
Views
253
Replies
3
Views
4K
Replies
6
Views
397

### Forum statistics

1,203,094
Messages
6,053,505
Members
444,667
Latest member
KWR21 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back