# VBA Ismissing for a Range

#### MNpoker

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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### MNpoker

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

#### Norie

##### Well-known Member
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.

#### MNpoker

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

#### Norie

##### Well-known Member

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.

#### Greg Truby

##### MrExcel MVP
Need to test for IS NOTHING.

#### MNpoker

##### Board Regular

Greg Truby said:
Need to test for IS NOTHING.

How is this coded?

Thanks as always for the responses.

#### Norie

##### Well-known Member
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``````

#### Greg Truby

##### MrExcel MVP
Very, very close Norie:

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

#### Norie

##### Well-known Member
Greg Truby said:
Very, very close Norie:
Greg

Mine worked and returned 20 on the worksheet.

Replies
15
Views
436
Replies
2
Views
110
Replies
4
Views
178
Replies
2
Views
172
Replies
6
Views
210