VBA Ismissing for a Range

MNpoker

Board Regular
Joined
Dec 15, 2003
Messages
154
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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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

For AA = 1 To NumCurves
If IsMissing(Maximums(AA)) Then Maximums(AA) = 999999999999#
Next AA

also tried
For AA = 1 To NumCurves
If IsMissing(Maximums) Then Maximums(AA) = 999999999999#
Next AA
 
Upvote 0
Do you really need to declare Maximum as a range?

In fact as far as I know you can only use IsMissing with Variant parameters.
 
Upvote 0
I think yes.

Because the first one could have a different maximum than the second.

Are they showing up a zero values if not entered?

For example does Maximum(3) = 0 if I omit it in the formula?

If so I could use If Maximum(aa) = 0 then Maximum = 999999999999
 
Upvote 0
When I try this with and without a range it always returns 10.
Code:
Function myfunc(Optional rng As Range) As Integer

If IsMissing(rng) Then
    myfunc = 20
Else
    myfunc = 10
End If

End Function

Check out the help for IsMissing, it's got some good information.
 
Upvote 0
MNPoker

This is how I coded Greg's suggestion in my small test.
Code:
Function mysum(Optional rng As Range) As Integer

If rng Is Nothing Then
    mysum = 20
Else
    mysum = 10
End If

End Function
 
Upvote 0
Very, very close Norie: :biggrin:

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> sblah(<SPAN style="color:#00007F">Optional</SPAN> r <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">If</SPAN> r <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        sblah = "like OMG! r is nothing, nada, nichts! that is sooo unreal!"
    <SPAN style="color:#00007F">Else</SPAN>
        sblah = 10
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top