VBA round

[Guest]

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?

 

[Guest]

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!

 

[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

 

[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)

 

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

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

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)

[Image]

 

[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)

 

[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

 

[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 ?