VBA Ismissing for a Range

[MNpoker]

Help on giving Optional Functions a value when they are a range and they are missing.


------------------------------------
ExponentialCDF(Weight As Range, Theta As Range, Optional Maximum As Range)

If IsMissing(maximum) then

------------------------------

Now if a maximum is not entered I want the maximum to be 999999999999 in all cases.

The number of cases would be identical to the number of (pieces?) in the Theta and Weight Ranges.

For example

ExponentailCDF(A1:b1,d1:d2)

This should produce
weight(1) = value in cell a1
weight(2) = value in cell b1

Theta(1) = value in cell d1
Theta(2) = value in cell d2

This part works fine and dandy (and if I enter in a range for the maximums it works fine as well). It's when I don't want to specify a maximum that it doesn't work in this case I'd want
Maximum(1) = 999999999999
Maximum(2) = 999999999999

I'll post some more of the actual code in the next post.

 

[MNpoker]

If Maximums Is Nothing Then
For AA = 1 To NumCurves
Maximums(AA) = 999999999
Next AA
End If

I'm still getting an error. I need to populate the Maximums Range with values if it's missing (or is nothing)

 

[Greg Truby]

No, no, I meant that you didn't have enough "tongue-in-cheek" humor. Actually, though, to have done it properly, my function should have had a clever philosophical name like TestForNihilism or something, but I dropped the ball...

Ciao bello,

 

[Norie]

What error are you getting?

 

[MNpoker]

The formula produces an #Value error.

20			
			
0.5	50	999999999	999999999
0.5	100		
			
PDF	0.010796854		
PDF	#VALUE!		

[code]

The first is when I include cells c3 and d3 in the formula the second is when I don't
=mpdf($A$1,$A$3:$A$4,$B$3:$B$4,C3:D3)

=mpdf($A$1,$A$3:$A$4,$B$3:$B$4)

 

[Greg Truby]

Can you post the entire code for the UDF mpdf?

 

[Norie]

Greg

At least I can spell properly!!!. humour

Humor - is that not something to do with cigars.:

 

[MNpoker]

Can you post the entire code for the UDF mpdf?

You asked for it!!! -- It need a little clean up but I'll do that once I get it working. (for example there is no longer a shift allowance but it's still mentioned in the ExpPDF function)

Finding the PDF of a mixed exponential.

Public Function MPDF(X, Optional Weights As Range, Optional ThetaA As Range, Optional Maximums As Range, Optional C, Optional CurveType As String, Optional Weight, Optional Parameter1, Optional Parameter2)

' To get the PDF of a mixed curve at X we add the PDF's of each individual curve * weight of the curve
' Allows up to 1000 Single Curves


PDFM = 0
Dim W(100)

'Set up the curves that go into the mixed curves
NRows = Weights.Rows.Count
NColumns = Weights.Columns.Count

NumCurves = Application.WorksheetFunction.Max(NRows, NColumns)

' See if the optional parameters were included
If IsMissing(C) Then C = 0
If IsMissing(Weight) Then Weight = 0


If Maximums Is Nothing Then
For AA = 1 To NumCurves
Maximums(AA) = 999999999
Next AA
End If
' See if C is illegally > X
If C >= X Then MPDF = "The Curve must be Evaluated Above the Conditional Parameter!!": Exit Function

'Normalize the weights

' Get the total weight
TW = 0
For WN = 1 To NumCurves
TW = TW + Weights.Cells(WN)
Next WN
TW = TW + Weight

' Get the normalized weights

For WN1 = 1 To NumCurves
W(WN1) = Weights.Cells(WN1) / TW
Next WN1
Weight1 = Weight
Weight1 = Weight1 / TW

' Get the Individual Curve Arrays

For A1 = 1 To NumCurves
PDFM = PDFM + W(A1) * ExpPDF(X, ThetaA(A1), Maximums(A1))
Next A1

PDFM = PDFM + Weight1 * LogPDF(X, Parameter1, Parameter2, Maximums(NumCurves + 1))

MPDF = PDFM


'Adjust for conditional Curve
If C > 0 Then
MPDF = MPDF / (1 - MCDF(C, Weights, ThetaA, Maximums, , CurveType, Weight, Parameter1, Parameter2))
End If

End Function

 

[MNpoker]

You'll need this as well

Public Function ExpPDF(X, Theta, Optional Maximum, Optional C)

' The Probability Density Function

' See if the optional parameter was included
If IsMissing(C) Then C = 0
If IsMissing(Maximum) Then Maximum = 999999999999#

'Determine if the curve is outside the allowed parameters (Less than the shift or greater than the Maximum)
If C >= X Then CurvePDF = "The Curve must be Evaluated Above the Conditional Parameter!!": Exit Function
If Shift > Maximum Then CurvePDF = 0: Exit Function
If X > Maximum Then CurvePDF = 0: Exit Function
If C >= Maximum Then CurvePDF = 0: Exit Function
If X = Maximum Then CurvePDF = 1 - ExpCDF(X, Theta, , C): Exit Function

'EVALUATE

ExpPDF = (Exp(-X / Theta) / Theta)

'Adjust for conditional Curve
If C > 0 Then
ExpPDF = ExpPDF / (1 - ExpCDF(C, Theta))
End If

End Function

 

[Greg Truby]

Sorry but gotta go tonight. If Norie (or someone else) doesn't get you sorted out between now and then, I can take a look at it in the morning.

 

[MNpoker]

If I could get this to work when I don't include the optional 'maximums' I think I'd be there

I want this to return a value of 999,999,999,999 when I put in the formula
Tester(6)

Public Function Tester(X, Optional Maximums As Range)

NumCurves = X

If Maximums Is Nothing Then
For AA = 1 To NumCurves
Maximums(AA) = 999999999
Next AA
End If

Tester = Maximums(3)

End Function