# 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. ## 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. Lima Mike, you can access a lot of functions via Application.WorkSheetFunction. Try something like
Code:
`Application.WorksheetFunction.RoundUp(Range("A1").Value)`
Best regards,

3. 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)

4. 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. 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))

6. 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. 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?

8. 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.

9. 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?

10. 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 ].

Page 1 of 2 12 Last

#### Posting Permissions

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