Range Arguments

[BradB]

I am new to VB and I have run into some trouble. I am trying to create a function that will calculate the average of a range, but omit all zeroes. The formula for this operation is not an issue, it is defining the argument of the function as a range of values from the spreadsheet. I would like the function to work just like any of the other Excel functions, where you can type "=zavg(" and then click the cell range you want. Here is the code I have so far...

Function zavg(num As Single)

If application.Sum(num) = 0 Then

zavg = 0

Else

zavg = (application.Sum(num)) / (application.SumProduct((num <> 0) * 1))

End If

End Function

 

[BradB]

For anyone who reads this. I got it. This is how:

Function zavg(rng As range) As Double

If (application.WorksheetFunction.Sum(rng.Cells)) = 0 Then

zavg = 0

Else

zavg = (application.WorksheetFunction.Sum(rng.Cells)) / (application.WorksheetFunction.CountIf(rng.Cells, ">0"))

End If
End Function