Lima Mike, you can access a lot of functions via Application.WorkSheetFunction. Try something like
Best regards,Code:Application.WorksheetFunction.RoundUp(Range("A1").Value)
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 ...
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
Lima Mike, you can access a lot of functions via Application.WorkSheetFunction. Try something like
Best regards,Code:Application.WorksheetFunction.RoundUp(Range("A1").Value)
Barrie Davidson
"You're only given a little spark of madness. You mustn't lose it." - Robin Williams
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.
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
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
Be responsible for the questions you post. If you don't reply to answered questions, be it just a simple "yes, that worked," or even "thanks," the time someone took to help you goes unrewarded.
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
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.
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:
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.
Like this thread? Share it with others