VBA round

J

Jeff

I am writing VBA.

How do I round.

I tried "Range("A1").value = Round(X)"
where I set X = 6.7

But it gave an error on the "Round()" - it said it was not defined function.
Is there a function that can round in VBA?
 
G

Gary's Student

This is a neat general solution. Anytime you need a worksheet function that
does not exist in VBA, first try:

Application.WorksheetFunction.whatever()

For example ROMAN() is not directly available in VBA, but

Application.WorksheetFunction.Roman(i)

will work just fine.

Have a good day!
 
J

JE McGimpsey

The Round method was introduced into VBA in VBA6 (XL2000 and later). If
you're using XL97 or MacXL, use

Range("A1").Value = Application.Round(X)

Note that VBA's Round and XL's ROUND treat a 5 in the last significant
digit differently - XL always rounds away from zero, VBA always rounds
to the nearest even digit:

VBA: Round(2.5, 0) ===> 2
Round(3.5, 0) ===> 4

XL: Round(2.5,0) ===> 3
Round(3.5,0) ===> 4
 
B

Bob Phillips

Round exists in VBA so this sounds like another problem.

Check the references in the VBE, Tools>References. If you see a checked item
that says Missing, uncheck and see how you get on.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Dave Peterson

Just ignore those unchecked items.

If you decide to use one (probably doing something suggested in a newsgroup
post), you'll usually see:

This requires a reference to xxxxx (like "microsoft scripting runtime").

Most will include the note so that there won't be a followup question.

Jack said:
Bob,

I looked in Tools>References which I never saw before. Only a few boxes are
checked, the miriad other boxes not. Don't I need those other items, many of
them look so important, how could I have ever lived without them? Most are
Library items, but certainly not all. See below. What to think about it?

Jack Sons
The Netherlands

Bob Phillips said:
Round exists in VBA so this sounds like another problem.

Check the references in the VBE, Tools>References. If you see a checked
item
that says Missing, uncheck and see how you get on.

--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...

[Image]
Click to expand...
 
B

Bob Phillips

If you have live this long without them, I am sure you will survive :).
Occasionally the one is required, not often.

If not showing as missing, is your Excel pre-2000?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
H

Henry

Jeff

Int() will give you just the whole number part of the input
Int(6.7) will give you 6.
If you want to round to the nearest whole number add 1/2 to your number then
do Int.
Int(X + 0.5)
NB This only works with positive numbers.
For negative numbers SUBTRACT 0.5 before doing the Int.
Int(X - 0.5)

Henry
 
B

Bryan Hessey

From the original question, relating to

Range("A1").value = Round(X)

would you not use

Range("A1").Formula = "=round(x,0)"

and continue to set x as specified ?

It would obviate the need to test for negative numbers - or is there an
unwritten 'no-no' against setting formula from VB ?
 
Top