One of the beauties of Office automation is that you can use
functions from other Office products. While I could write custom
code for an Access Ceiling function, I prefer to use Excel's, like
this:
Public Function XLCeiling(dblNum As Double, dblUp As Double) As
Double Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
XLCeiling = objXL.WorksheetFunction.Ceiling(dblNum, dblUp)
End Function
I'd suggest using a static variable inside that function, and
checking if it Is Nothing, and only then initializating it. That
way, you won't be starting up Excel every time you call it (and
you're not shutting it down, so you'd end up with a bunch of
orphaned invisible instances of Excel with your code, no?). The code
should probably also have an optional flag to tear down Excel, so
I'd probably write it something like this (though it needs error
handling):
Public Function XLCeiling(ByVal dblNum As Double, _
ByVal dblUp As Double, _
ByVal Optional bolClose As Boolean) As Double
Static objXL As Object
If bolClose Then
Set objXL = Nothing
Exit Function
End If
If objXL Is Nothing Then
Set objXL = CreateObject("Excel.Application")
End If
XLCeiling = objXL.WorksheetFunction.Ceiling(dblNum, dblUp)
End Function
