UDF's and Worksheet Function VBA Combinations

[Matt P.]

Hello,

I'm not sure what I'm doing wrong but I hope someone can
help me. I have a UDF that I want to calculate a value
for me. The normal Worksheet display of the function
would be =IF(ISERROR(AVERAGE(E5:E6)),"",AVERAGE(E5:E8))
I would like this to be a user defined function letting
the range be selectable by the user. I've created Each
individual part of the function and it works correctly but
for some reason when I try each piece together it doesn't
work.

If you run a macro to record the steps of manually
inserting the function the VB looks like.

ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(AVERAGE(R[-16]C[-1]:R[-15]C[-
1])),"""",AVERAGE(R[-16]C[-1]:R[-15]C[-1]))"

I've tried many ways to turn this into a function.
Like I said I can take each individual function and have
it work correctly in a UDF but to combine the IF, ISERROR,
and AVERAGE functions and then assign this value to the
Function name just doesn't seem to work. I've debugged and
it has had the correct true and false values for data that
I tested it on. Oh well, hopefully someone knows.

I would also like to create UDF's for these functions as
well.
=IF(sheet!F3="N/A","N/A",IF(sheet!F3="","",NETWORKDAYS
(sheet!D3,sheet!F3,sheet!D3)))

=SUM(IF(sheet1!$C$2:$C$500="AA",IF(sheet1!
$D$2:$D$500<=DATEVALUE("8/31/2004"),IF(sheet1!
$D$2:$D$500>=DATEVALUE("8/1/2004"),IF(sheet1!
$B$2:$B$500="DD",1,0),0),0),0))

I've got a few more questions but I'll hold off for now.
Thanks to whom ever can help me.

 

[Stephen Rasey]

Try this

Public Function AvgWithoutErr(rngA as range) variant
'to perform like =IF(ISERROR(AVERAGE(E5:E6)),"",AVERAGE(E5:E8))

on error goto E1
AvgWithoutErr = application.worksheetfunction.average(rnga)
if ISERROR(avgWithoutErr) Then AvgWithoutErr = ""
exit function
E1:
AvgWithoutErr = ""
Exit Function

Stephen Rasey
Houston
http://wiserways.com
http://excelsig.org

 

[Matt P.]

Thank you so much that just shaved off so much time.

 

[Erin]

Here is one for your second function:

Function IfFunction(Range1, Range2, Range3)
Dim BlnSameSize As Boolean
Dim dblArray() As Double

On Error GoTo ErrRtn
BlnSameSize = False
If Range1.Cells.Count = Range2.Cells.Count And
Range2.Cells.Count = Range3.Cells.Count Then BlnSameSize =
True
If Not BlnSameSize Then GoTo ErrRtn
ReDim dblArray(Range1.Cells.Count - 1)
For i = 1 To Range1.Cells.Count
If Range1.Cells(i) = "AA" And Range2.Cells(i) <=
#8/31/2004# And Range2.Cells(i) >= #8/1/2004# And
Range3.Cells(i) = "DD" Then
dblArray(i - 1) = 1
Else
dblArray(i - 1) = 0
End If
Next i
IfFunction = WorksheetFunction.Sum(dblArray)
Exit Function

ErrRtn:
IfFunction = CVErr(xlErrValue)
End Function