Optional Variables

Hello - I have a UDF that counts the number of values in a range matching a
criteria. It works perfectly when I enter inputs for all variables, however
when I only enter values for condR1 and cond1 I get a #VALUE!. I think it's
because condR2 and cond2 are now null so when it checks for "cond2Arr(i, 1) =
cond2" it fails. How can I get by this? Is there a check I can do to see if
condR2 is null or something? Thanks.

Function TempCount(condR1 As Range, cond1 As String, Optional condR2 As
Range, Optional cond2 As String)
Dim cond1Arr, cond2Arr
cond1Arr = condR1
cond2Arr = condR2

For i = 1 To UBound(cond1Arr)
If (cond1Arr(i, 1) = cond1 And cond2Arr(i, 1) = cond2) Then
temp = temp + 1
End If
Next i
TempCount = temp
End Function

Dave,

The variable condR2 will be Nothing if it is omitted, so you can
test for that. E.g.,

If condR2 Is Nothing Then
' omitted
End If



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Dave" <[email protected]> wrote in message
news:[email protected]...
> Hello - I have a UDF that counts the number of values in a
> range matching a
> criteria. It works perfectly when I enter inputs for all
> variables, however
> when I only enter values for condR1 and cond1 I get a #VALUE!.
> I think it's
> because condR2 and cond2 are now null so when it checks for
> "cond2Arr(i, 1) =
> cond2" it fails. How can I get by this? Is there a check I
> can do to see if
> condR2 is null or something? Thanks.
>
> Function TempCount(condR1 As Range, cond1 As String, Optional
> condR2 As
> Range, Optional cond2 As String)
> Dim cond1Arr, cond2Arr
> cond1Arr = condR1
> cond2Arr = condR2
>
> For i = 1 To UBound(cond1Arr)
> If (cond1Arr(i, 1) = cond1 And cond2Arr(i, 1) = cond2) Then
> temp = temp + 1
> End If
> Next i
> TempCount = temp
> End Function