VBA round
 

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?

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!
--
Gary's Student


"Jeff" wrote:

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?

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


In article ,
"Jeff" wrote:

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?

Round exists in VBA so this sounds like another problem.

Check the references in the VBE, ToolsReferences. 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)


"Gary's Student" wrote in message
...
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!
--
Gary's Student


"Jeff" wrote:

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?

Bob,

I looked in ToolsReferences 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" schreef in bericht
...
Round exists in VBA so this sounds like another problem.

Check the references in the VBE, ToolsReferences. 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)


"Gary's Student" wrote in message
...
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!
--
Gary's Student


"Jeff" wrote:

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?

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 Sons wrote:

Bob,

I looked in ToolsReferences 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" schreef in bericht
...
Round exists in VBA so this sounds like another problem.

Check the references in the VBE, ToolsReferences. 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)


"Gary's Student" wrote in message
...
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!
--
Gary's Student


"Jeff" wrote:

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?



[Image]

--

Dave Peterson

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)


"Jack Sons" wrote in message
...
Bob,

I looked in ToolsReferences 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" schreef in bericht
...
Round exists in VBA so this sounds like another problem.

Check the references in the VBE, ToolsReferences. 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)


"Gary's Student" wrote in
message
...
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!
--
Gary's Student


"Jeff" wrote:

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?

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




"Jeff" wrote in message
...
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?

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 ?


Henry Wrote:
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




"Jeff" wrote in message
...
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?


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=401192