Page 1 of 2 12 LastLast
Results 1 to 10 of 12

RoundUp and RoundDown in VBA

This is a discussion on RoundUp and RoundDown in VBA within the Excel Questions forums, part of the Question Forums category; Dear All, I tried to use the functions roundup and rounddown in VBA but it does not work. In a ...

  1. #1
    Board Regular
    Join Date
    Jun 2003
    Posts
    70

    Default RoundUp and RoundDown in VBA

    Dear All,

    I tried to use the functions roundup and rounddown in VBA but it does not work.
    In a spreadsheet they do but not in VBA.
    Can't find help in HELP.

    Anybody that can help?

    Lima Mike

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330

    Default

    Lima Mike, you can access a lot of functions via Application.WorkSheetFunction. Try something like
    Code:
    Application.WorksheetFunction.RoundUp(Range("A1").Value)
    Best regards,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    Board Regular
    Join Date
    May 2003
    Location
    Katy, Texas
    Posts
    3,829

    Default

    Or, you could try using a little math:

    To ROUNDUP(A1) numbers >= 0, use:
    A1 = INT(A1+.999999)

    To ROUNDUP(A1) numbers < 0, use:
    A1 = INT(A1)

    =================
    To ROUNDDOWN(A1) numbers >= 0, use:
    A1 = INT(A1)

    To ROUNDDOWN numbers < 0, use:
    A1 = INT(A1+0.9999)
    Ralph A. Esquivel, Excel 97 SR2 (Ver. 8.0), Windows XP, Home Edition, Service Pack 2, HP LaserJet 4L printer.

  4. #4
    Board Regular
    Join Date
    Jun 2003
    Posts
    70

    Default

    Thanks.
    Both options do the trick.
    I can use both

    A = Application.WorksheetFunction.RoundDown(theCalculation, 0)

    A = Int(theCalculation)

    Don't know why I countn't find INT in the HELP of Excel.
    Checked it and is indeed a normal function.
    What does INT mean anyway?

    Lima Mike

  5. #5
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    16,980

    Default

    Integer

    This is from the help file:

    Int, Fix Functions


    Returns the integer portion of a number.

    Syntax

    Int(number)

    Fix(number)

    The required number argument is a Double or any valid numeric expression. If number contains Null, Null is returned.

    Remarks

    Both Int and Fix remove the fractional part of number and return the resulting integer value.

    The difference between Int and Fix is that if number is negative, Int returns the first negative integer less than or equal to number, whereas Fix returns the first negative integer greater than or equal to number. For example, Int converts -8.4 to -9, and Fix converts -8.4 to -8.

    Fix(number) is equivalent to:

    Sgn(number) * Int(Abs(number))
    Office 2007/2010

  6. #6
    MrExcel MVP parry's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    3,355

    Default

    Hi, note that a variable defined as an Integer can only have a value between -32,768 to 32,767. So if your value exceeds these you will get an overflow error. If your value is likely to be larger than these limits then use Long instead which is a bigger integer data type.

    Code:
    Sub Example()
    Dim A As Long, B As Integer, theCalculation As Double
    
    theCalculation = 75324.567
    
    'This will work fine because A is an Long Integer
    A = Application.WorksheetFunction.RoundDown(theCalculation, 0)
    A = Int(theCalculation)
    MsgBox A
    
    'This will fail with an overflow error because B is an Integer
    B = Application.WorksheetFunction.RoundDown(theCalculation, 0)
    B = Int(theCalculation)
    MsgBox B
    End Sub

  7. #7
    Board Regular
    Join Date
    May 2003
    Location
    Katy, Texas
    Posts
    3,829

    Default

    Hi, Parry:
    I am not into VBA, so, in spreadsheets, I only work with formulas, basically.

    I came up with BASIC, back in 1978, ending in QuickBASIC 4.5. I went on to Visual BASIC, but have reverted back to QuickBASIC.

    As to spreadsheets, I started by learning Lotus 123, version "a" (if I remember correctly), back in 1983 or so, then ended with Excel.

    Now, the integers straddled by the original 64K memory limit still stands in BASIC (DOS?), which allows integers from 0 to 32,767 (0 plus 32,767 positive numbers), and -1 to -32,768 negative numbers, for a total of 65,536 numbers.

    In my VisualBASIC 6.0 (VB6), I can enter, directly, a number with up to 15 significant digits, and, I get all 15 significant digits in my INT() function, as in:
    =INT(123456789012345), for which I see the answer as
    123456789012345, or as in: =INT(1234567890123.45), which shows up as 1234567890123 (the whole 13 integer digits), and so on.

    The exact, same thing as occurs in my VB6, above, also occurs in my Excel 97!

    So, using the INT() function directly in the Excel spreadsheet seems to work for a 15-digit number in Excel, vs. a 16-digit number for long in BASIC and QuickBASIC). It seems that Excel uses "almost" long integers for its workings?

    BASIC was justified in defining the simple integer and the long integer, because the first uses only 2 bytes, while the second used 4 bytes. And, in the early days, memory was very limited and programmers had to juggle their data, code and algorithms to be as small as possible, in order not to exceed the meager (64K, or 65,536 bytes) memory space available at the time.

    Perhaps the 15-digit numbers are not used in VBA, VBA being a metamorphosis of the old BASIC, maybe still retaining the concepts of "integer" with 6 significant figures and the "long integer" with 16 significant figures? Or, perhaps, it is a form of VB6 or VB7.1, and also uses 15-digit-long "integers"?

    It would be somewhat interesting, to me, to know if, indeed, VBA still uses simple integers and long integers. Sounds like an anachronism, to me, if it does. What do you think?
    Ralph A. Esquivel, Excel 97 SR2 (Ver. 8.0), Windows XP, Home Edition, Service Pack 2, HP LaserJet 4L printer.

  8. #8
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default

    The INT function is a whole 'nother thing than the Integer/Long data type.

    The function simply returns the integer - read whole number portion - of its numeric argument, which can be of type Integer, Long, Double, Currency, Single or Decimal.

    The Integer data type is limited to values -32,768 to 32,767 while a Long can be from -2,147,483,648 to 2,147,483,647.
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  9. #9
    Board Regular
    Join Date
    May 2003
    Location
    Katy, Texas
    Posts
    3,829

    Default

    just_jon:
    Hmm, "...a Long can be from -2,145,483,648 to 2,147,483,647". But, since in Excel spreadsheets I can write the number 214,784,836,481,234, which has 15 digits, versus the above long integers, with only 10 digits, why use long integers at all, since Excel provides 15 integers already?
    Ralph A. Esquivel, Excel 97 SR2 (Ver. 8.0), Windows XP, Home Edition, Service Pack 2, HP LaserJet 4L printer.

  10. #10
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default

    Quote Originally Posted by RalphA
    just_jon:
    Hmm, "...a Long can be from -2,145,483,648 to 2,147,483,647". But, since in Excel spreadsheets I can write the number 214,784,836,481,234, which has 15 digits, versus the above long integers, with only 10 digits, why use long integers at all, since Excel provides 15 integers already?
    Integers take half the storage as longs, and 1/4 that of doubles - it's mainly a storage matter, I suppose [ who says size doesn't matter ].
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com