VBA round

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

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

[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


In article <[email protected]>,
"Jeff" <[email protected]> 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 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)


"Gary's Student" <[email protected]> wrote in message
news:[email protected]...
> 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?

[Jack Sons]

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" <[email protected]> schreef in bericht
news:[email protected]...
> 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)
>
>
> "Gary's Student" <[email protected]> wrote in message
> news:[email protected]...
>> 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?
>
>

[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 Sons wrote:
>
> 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" <[email protected]> schreef in bericht
> news:[email protected]...
> > 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)
> >
> >
> > "Gary's Student" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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

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


"Jack Sons" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> schreef in bericht
> news:[email protected]...
> > 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)
> >
> >
> > "Gary's Student" <[email protected]> wrote in
message
> > news:[email protected]...
> >> 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?
> >
> >
>
>
>

[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




"Jeff" <[email protected]> wrote in message
news:[email protected]...
>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]

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 ?

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" <[email protected]> wrote in message
news:[email protected]...
>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?