INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Functions

How do I simulate Excel's Ceiling/Floor Functions in VBA? by ByteMyzer
Posted: 12 Apr 04

To simulate these two Excel functions in Access Visual Basic, paste the following code into a new module and save it:

CODE

Public Function Ceiling(ByVal X As Double, Optional ByVal Factor As Double = 1) As Double
    ' X is the value you want to round
    ' is the multiple to which you want to round
    Ceiling = (Int(X / Factor) - (X / Factor - Int(X / Factor) > 0)) * Factor
End Function

Public Function Floor(ByVal X As Double, Optional ByVal Factor As Double = 1) As Double
    ' X is the value you want to round
    ' is the multiple to which you want to round
    Floor = Int(X / Factor) * Factor
End Function


Examples

Ceiling(2.5, 1) equals 3
Ceiling(2.5) equals 3
Ceiling(-2.5, -2) equals -4
Ceiling(-2.5, 2) equals -2
Ceiling(1.5, 0.1) equals 1.5
Ceiling(0.234, 0.01) equals 0.24

Floor(2.5, 1) equals 2
Floor(2.5) equals 2
Floor(-2.5, -2) equals -2
Floor(-2.5, 2) equals -4
Floor(1.5, 0.1) equals 1.5
Floor(0.234, 0.01) equals 0.23

Back to Microsoft: Access Modules (VBA Coding) FAQ Index
Back to Microsoft: Access Modules (VBA Coding) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close