PDA

View Full Version : Solved: Proper syntax for writing a user defined function?



AKK
07-20-2009, 12:06 PM
I have written a good bit of VBA, but I am currently trying to write a user defined function for the first time, and I'm having a hard time figuring out to what extent the syntax is different or the same. I need a function that does the following:


Look at all cells from B6 up to the cell that is directly to the left of the cell with the formula in it.
If any of those cells have a numerical value that is not zero, return the value 0.
If all the cells in that range are zero, return the value currently in K6.I haven't really gotten that far yet --



Function SumOnce(rngPrevCells) As Integer

Dim rngPrevCells As Range

Set rngPrevCells = Range("B6", Selection.Offset(0, -1))

'???

End Function


What I'm really having trouble with is figuring out the syntax for that second bullet point I listed. Can anyone help?

Thanks!

CHatmaker
07-20-2009, 01:37 PM
Function SumOnce(rngPrevCells as Range) As Integer

rngPrevCells is what is being passed to the UDF. If you pass it like this, the user selects the range to be compared.

If you want to select it in your function instead then use:

Function SumOnce() As Integer

AKK
07-20-2009, 01:50 PM
Thanks. Is there any way to instruct Excel to check whether any of the cells in rngPrevCells have a non-zero value?

p45cal
07-20-2009, 01:57 PM
Function SumOnce(rngPrevCells As Range, defaultval As Range) As Integer
SumOnce = defaultval.Value
For Each cll In rngPrevCells.Cells
If cll.Value <> 0 Then
SumOnce = 0
Exit For
End If
Next cll
End FunctionUse thus, say in C2:
=SumOnce($B2:B$6,$K$6)
and copy down (and/or up).

But do you need a udf?
in the same cell C2:
=IF(SUM($B2:B$6)>0,0,$K$6)
or
=IF(COUNTIF($B2:B$6,">0")>0,0,$K$6)
copying down and/or up as before.

AKK
07-20-2009, 02:15 PM
Thanks, that is much easier! Now I just have one other question.

The ranges I'm summing are parts of rows that always start at B6, so I changed the formula to =IF(Sum($B6:B6)>0,0,$K$6). But I also just realized that I need the formula to check whether or not the cell directly above it has a value of 0, and return 0 if it does. Is there any way to write a conditional statement with multiple criteria, or would that warrant a UDF?

Thanks so much for your help.

CHatmaker
07-20-2009, 03:06 PM
You can nest IF statements as:

=if(<first condition>,If(<second condition>,<True Result>, <False Result>), <False First Condition Result>)

AKK
07-20-2009, 03:34 PM
The nested IF statements worked perfectly. Thanks!