RoundUp and RoundDown in VBA

Lima Mike

Board Regular
Joined
Jun 3, 2003
Messages
70
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Lima Mike, you can access a lot of functions via Application.WorkSheetFunction. Try something like
Code:
Application.WorksheetFunction.RoundUp(Range("A1").Value)

Best regards,
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
RalphA said:
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 ].
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top