Someone on the newsgroups wanted to rewrite the CEILING function in order to learn how to write user defined functions. Rewriting built-in functions is a good way to learn, I think. It seems like I pick up something new every time I go through the exercise,which is not that often. Ben McBen supplied the logic in answer to the post and I added the error checking. If you’ve ever written a worksheet function, you know it’s 90% error checking and 10% logic.
Dim dNumber As Double
Dim dSignif As Double
Dim lTemp As Long
Dim vReturn As Variant
Const lERR_NUM As Long = 9997
Const lERR_TYPE As Long = 9998
Const lERR_OTHER As Long = 9999
On Error GoTo Err_Proc
‘Verify that arguments are numbers
Select Case TypeName(number)
Case “Range”
Select Case TypeName(number.Value)
Case “String”
Err.Raise lERR_TYPE
Case “Boolean”
dNumber = Abs(CDbl(number.Value))
Case Else
dNumber = CDbl(number.Value)
End Select
Case “String”
Err.Raise lERR_TYPE
Case “Boolean”
dNumber = Abs(CDbl(number))
Case Else
dNumber = CDbl(number)
End Select
Select Case TypeName(significance)
Case “Range”
Select Case TypeName(significance.Value)
Case “String”
Err.Raise lERR_TYPE
Case “Boolean”
dSignif = Abs(CDbl(significance.Value))
Case Else
dSignif = CDbl(significance.Value)
End Select
Case “String”
Err.Raise lERR_TYPE
Case “Boolean”
dSignif = Abs(CDbl(significance))
Case Else
dSignif = CDbl(significance)
End Select
‘Verify that signs are the same
If (dNumber < 0) <> (dSignif < 0) Then
Err.Raise lERR_NUM
End If
‘if significance is zero, always return zero
If dSignif = 0 Then
vReturn = 0
Else
lTemp = Int(dNumber / dSignif)
If lTemp = (dNumber / dSignif) Then ‘already at the correct precision
vReturn = dNumber
Else
vReturn = (lTemp + 1) * dSignif
End If
End If
Exit_Proc:
On Error Resume Next
xCEILING = vReturn
Exit Function
Err_Proc:
Select Case Err.number
Case lERR_TYPE
vReturn = CVErr(xlErrValue)
Case lERR_NUM
vReturn = CVErr(xlErrNum)
Case Else
vReturn = CVErr(xlErrValue)
End Select
Resume Exit_Proc
End Function
I always make my arguments and return values Variants. If it’s to be used in a worksheet, the user should be able to enter a range reference or a value. VBA will convert range references to their values because the Value property is the default property for the Range object. But handling the conversion explicitly inside the function gives me more flexibility in returning specific errors. For return values, I need to be able to return one or more error values, so it has to be a Variant.
Note that I have to explicitly handle Boolean data types. VBA and Excel treat True in different ways. Namely, VBA assigns True the value of -1 and Excel assigns it the value of 1. Even if the True comes from Excel (like in a worksheet function argument), VBA still treats it as -1, so I use the Abs function to make it 1.
What did I learn? I learned that I didn’t really understand integer division like I thought I did. I originally used the operator because I thought it was the same as Int(x/y), but not so. I guess I’ve only every used it with Integer operands. When I passed it a Double divisor, it was converted to an Integer. Since the Integer it was converted to was zero, I got an error. So my lesson was “don’t use integer division unless you’re dividing integers”.
Now extend it to handle array formulae!
Not only does it fail to handle arrays, it also mishandles string arguments that could be converted into numbers.
=CEILING(“1.23?,1) returns 2
but
=xCEILING(“1.23?,1) returns #VALUE!
Specifications are such a pain!
Wow, – I’ve only ever put error handles into a one UDF – i figure that if it’s a UDF then the people should be passing it the right things! – maybe in the accounts would thats not good enough?
Dick, you forgot the cases where TypeName(number.Value) = “Error” and TypeName(significance.Value) = “Error”.
In my view, you need to bubble these up to the caller.
AS SIMPLE as
Public Sub Ceiling()
Range(“A1?) = Application.Ceiling(12, 3)
End Sub