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]

This doesn't even work
Tester(6) And either does Test(6,a1:a4) where a3 has a value

Public Function Tester(X, Optional Maximums As Range)

For AA = 1 To 4
Maximums(AA) = 999999999
Next AA

Tester = Maximums(3)

End Function

 

[MNpoker]

Last bump I promise!!!

This is going to entail a huge amount of work if I can't get this figured out.

 

[Norie]

That function failed on this line when I tried it.

Maximums(AA) = 999999999

There was no error message as functions just stop executing when they encounter an error and return the #VALUE error in the cell.

 

[Scott Huish]

Try this:

Public Function Tester(X, Optional Maximums As Range)
Dim maximum()
NumCurves = X
If Maximums Is Nothing Then
    ReDim maximum(NumCurves)
    For AA = 1 To NumCurves
        maximum(AA) = 999999999
    Next AA
End If
Tester = maximum(3)
End Function

 

[MNpoker]

BINGO!!

Think that's it (Hotpeppers did not work when I included values in the maximums - but a minor change seemed to fix that.)

Public Function Tester(X, Optional Maximums As Range)
Dim maximum()
NumCurves = X
If Maximums Is Nothing Then
    ReDim maximum(NumCurves)
    For AA = 1 To NumCurves
        maximum(AA) = 999999999
    Next AA
Else
ReDim maximum(NumCurves)
    For AA = 1 To NumCurves
    maximum(AA) = Maximums(AA)
    Next AA
End If
Tester = maximum(3)
End Function

Thanks!!!

Crossing my fingers that is works globally.