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.
 

Some videos you may like

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
Joined
Dec 15, 2003
Messages
154
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
Joined
Apr 28, 2004
Messages
76,059
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 15, 2003
Messages
154
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
Joined
Apr 28, 2004
Messages
76,059
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

MNpoker

Board Regular
Joined
Dec 15, 2003
Messages
154

ADVERTISEMENT

Greg Truby said:
Need to test for IS NOTHING.

How is this coded?

Thanks as always for the responses.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,059
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 19, 2002
Messages
10,014
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>
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,059
Office Version
  1. 365
Platform
  1. Windows
Greg Truby said:
Very, very close Norie: :biggrin:
Greg

Mine worked and returned 20 on the worksheet.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,535
Messages
5,572,759
Members
412,482
Latest member
arooshrana2
Top