I want to use "Round" in VBA. I get the message:

"Sub or Function not defined"

I can see the function in the Help. Am I missing a
reference?

Regards,
Alan

try

Sub roundit()
x = Round(2.25878899 * 4.25587, 2)
'or
'range("a1")=round(range("a2")*range("a3"),2)
End Sub

--
Don Guillett
SalesAid Software

"Alan" wrote in message
...
I want to use "Round" in VBA. I get the message:

"Sub or Function not defined"

I can see the function in the Help. Am I missing a
reference?

Regards,
Alan

Alan,

There is no Round VBA method, so you have to use the worksheetfunction


?Worksheetfunction.Round(10.2,0)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Alan" wrote in message
...
I want to use "Round" in VBA. I get the message:

"Sub or Function not defined"

I can see the function in the Help. Am I missing a
reference?

Regards,
Alan

Oops. As Don points out I am wrong, and I thought I had found that in the
past.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in message
...
Alan,

There is no Round VBA method, so you have to use the worksheetfunction


?Worksheetfunction.Round(10.2,0)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Alan" wrote in message
...
I want to use "Round" in VBA. I get the message:

"Sub or Function not defined"

I can see the function in the Help. Am I missing a
reference?

Regards,
Alan

I think it depends which version of Excel you have:

In E97 it is missing!

In E2000 it is present in the VBA.Math library

I'll bet the OP is using E97.

--
Michael Hopwood


"Bob Phillips" wrote in message
...
Oops. As Don points out I am wrong, and I thought I had found that in the
past.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in message
...
Alan,

There is no Round VBA method, so you have to use the worksheetfunction


?Worksheetfunction.Round(10.2,0)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Alan" wrote in message
...
I want to use "Round" in VBA. I get the message:

"Sub or Function not defined"

I can see the function in the Help. Am I missing a
reference?

Regards,
Alan

VBA's Round function was added in VBA6 (Excel2000+).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Bob Phillips" wrote in
message ...
Oops. As Don points out I am wrong, and I thought I had found
that in the
past.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in
message
...
Alan,

There is no Round VBA method, so you have to use the
worksheetfunction


?Worksheetfunction.Round(10.2,0)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Alan" wrote in message
...
I want to use "Round" in VBA. I get the message:

"Sub or Function not defined"

I can see the function in the Help. Am I missing a
reference?

Regards,
Alan

Alan a écrit :

I want to use "Round" in VBA. I get the message:

"Sub or Function not defined"

I can see the function in the Help. Am I missing a
reference?

Regards,
Alan


Hi Alan,

As said by other eminent colleagues round is missing under XL97.
Try :

public function round97(nb,dec)
round97 = int(nb*(10^dec)+0.5)/(10^dec)
end function

HTH
FxM

Hi Alan,

As said by other eminent colleagues round is missing under XL97.
Try :

public function round97(nb,dec)
round97 = int(nb*(10^dec)+0.5)/(10^dec)
end function

Some rounding problems I prefer not to look at ... Seems better with :
round97 = (Int((nb * (10 ^ dec)) + 0.5 + (10 ^ (dec - 4)))) / (10 ^ dec)

HTH
FxM

Michael,

I'll use that as my excuse as to why I thought it wasn't there<vbg

Bob

"Michael Hopwood" wrote in message
...
I think it depends which version of Excel you have:

In E97 it is missing!

In E2000 it is present in the VBA.Math library

I'll bet the OP is using E97.

--
Michael Hopwood


"Bob Phillips" wrote in message
...
Oops. As Don points out I am wrong, and I thought I had found that in
the
past.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in message
...
Alan,

There is no Round VBA method, so you have to use the worksheetfunction


?Worksheetfunction.Round(10.2,0)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Alan" wrote in message
...
I want to use "Round" in VBA. I get the message:

"Sub or Function not defined"

I can see the function in the Help. Am I missing a
reference?

Regards,
Alan

If you're using xl97, you could use: Application.round

But if you're using xl2k+, maybe you have a missing reference.

Tools|References
Scroll down that list looking for MISSING

If you find one, uncheck it. You'll have to decide if it was important.

(A missing reference will cause the procedure to not compile--and might point at
any old line.)

Alan wrote:

I want to use "Round" in VBA. I get the message:

"Sub or Function not defined"

I can see the function in the Help. Am I missing a
reference?

Regards,
Alan

--

Dave Peterson

Hi All,

This is my first chance to use the internet since I made
my post. Thanks for all the replies.

I'm using Excel 97 (I usually mention that in my posts.
Sorry!)

Bob's suggestion of WorksheetFunction.Round works fine.

Regards,
Alan


-----Original Message-----
I want to use "Round" in VBA. I get the message:

"Sub or Function not defined"

I can see the function in the Help. Am I missing a
reference?

Regards,
Alan
.

Hi Alan,

Serendipity eh? I was wrong, but I was correct, and I learned something.
Good day<vbg

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Alan" wrote in message
...
Hi All,

This is my first chance to use the internet since I made
my post. Thanks for all the replies.

I'm using Excel 97 (I usually mention that in my posts.
Sorry!)

Bob's suggestion of WorksheetFunction.Round works fine.

Regards,
Alan


-----Original Message-----
I want to use "Round" in VBA. I get the message:

"Sub or Function not defined"

I can see the function in the Help. Am I missing a
reference?

Regards,
Alan
.

When I type "WorksheetFunction", the Intellisense opens
and shows "Round".

When I type "Application", the Intellisense opens, but
does not show "Round".

If I search for "Round" in the Object Browser, it only
appears in "WorksheetFunction" - nowhere else (question:
is the Object Browser always complete, and shows the same
things for everybody, or does it only show what each
user's References are pointing to?).

There is no "MISSING" in References (question: would it
appear under "M" for "MISSING", or would it appear under
the initial letter of the missing reference?).

Thanks for all your suggestions.

Regards,
Alan



-----Original Message-----
If you're using xl97, you could use: Application.round

But if you're using xl2k+, maybe you have a missing
reference.

Tools|References
Scroll down that list looking for MISSING

If you find one, uncheck it. You'll have to decide if it
was important.

(A missing reference will cause the procedure to not
compile--and might point at
any old line.)

Alan wrote:

I want to use "Round" in VBA. I get the message:

"Sub or Function not defined"

I can see the function in the Help. Am I missing a
reference?

Regards,
Alan

--

Dave Peterson

.

Can't lose them all :-)

Thanks again.

Alan


-----Original Message-----
Hi Alan,

Serendipity eh? I was wrong, but I was correct, and I
learned something.
Good day<vbg

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Alan" wrote in
message
...
Hi All,

This is my first chance to use the internet since I made
my post. Thanks for all the replies.

I'm using Excel 97 (I usually mention that in my posts.
Sorry!)

Bob's suggestion of WorksheetFunction.Round works fine.

Regards,
Alan


-----Original Message-----
I want to use "Round" in VBA. I get the message:

"Sub or Function not defined"

I can see the function in the Help. Am I missing a
reference?

Regards,
Alan
.



.

LOL. I can try

Bob

"Alan" wrote in message
...
Can't lose them all :-)

Thanks again.

Alan


-----Original Message-----
Hi Alan,

Serendipity eh? I was wrong, but I was correct, and I
learned something.
Good day<vbg

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Alan" wrote in
message
...
Hi All,

This is my first chance to use the internet since I made
my post. Thanks for all the replies.

I'm using Excel 97 (I usually mention that in my posts.
Sorry!)

Bob's suggestion of WorksheetFunction.Round works fine.

Regards,
Alan


-----Original Message-----
I want to use "Round" in VBA. I get the message:

"Sub or Function not defined"

I can see the function in the Help. Am I missing a
reference?

Regards,
Alan
.



.

Alan,

Worksheetfunction is the way to do it since XL97. Apparently, Application
was the way in worked in XL95, and has been retained for compatibility. As
you not, Application does not give Intellisense, but there are also some
functions that just don't work with Worksheetfunction, so you have to resort
to Application. And the error handling is different.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Alan" wrote in message
...
When I type "WorksheetFunction", the Intellisense opens
and shows "Round".

When I type "Application", the Intellisense opens, but
does not show "Round".

If I search for "Round" in the Object Browser, it only
appears in "WorksheetFunction" - nowhere else (question:
is the Object Browser always complete, and shows the same
things for everybody, or does it only show what each
user's References are pointing to?).

There is no "MISSING" in References (question: would it
appear under "M" for "MISSING", or would it appear under
the initial letter of the missing reference?).

Thanks for all your suggestions.

Regards,
Alan



-----Original Message-----
If you're using xl97, you could use: Application.round

But if you're using xl2k+, maybe you have a missing
reference.

Tools|References
Scroll down that list looking for MISSING

If you find one, uncheck it. You'll have to decide if it
was important.

(A missing reference will cause the procedure to not
compile--and might point at
any old line.)

Alan wrote:

I want to use "Round" in VBA. I get the message:

"Sub or Function not defined"

I can see the function in the Help. Am I missing a
reference?

Regards,
Alan

--

Dave Peterson

.

I use application.match and application.vlookup (for example), because of the
way errors are handled.

And Application contains fewer letters than worksheetfunction <bg.

I use xl2k and xl2002.

Bob Phillips wrote:

Alan,

Worksheetfunction is the way to do it since XL97. Apparently, Application
was the way in worked in XL95, and has been retained for compatibility. As
you not, Application does not give Intellisense, but there are also some
functions that just don't work with Worksheetfunction, so you have to resort
to Application. And the error handling is different.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Alan" wrote in message
...
When I type "WorksheetFunction", the Intellisense opens
and shows "Round".

When I type "Application", the Intellisense opens, but
does not show "Round".

If I search for "Round" in the Object Browser, it only
appears in "WorksheetFunction" - nowhere else (question:
is the Object Browser always complete, and shows the same
things for everybody, or does it only show what each
user's References are pointing to?).

There is no "MISSING" in References (question: would it
appear under "M" for "MISSING", or would it appear under
the initial letter of the missing reference?).

Thanks for all your suggestions.

Regards,
Alan



-----Original Message-----
If you're using xl97, you could use: Application.round

But if you're using xl2k+, maybe you have a missing
reference.

Tools|References
Scroll down that list looking for MISSING

If you find one, uncheck it. You'll have to decide if it
was important.

(A missing reference will cause the procedure to not
compile--and might point at
any old line.)

Alan wrote:

I want to use "Round" in VBA. I get the message:

"Sub or Function not defined"

I can see the function in the Help. Am I missing a
reference?

Regards,
Alan

--

Dave Peterson

.


--

Dave Peterson

Dave,

Bad wording. By 'way to do it' I really meant this is the way MS would
like/expect us to do it..

Similar to Auto_Open and Workbook_Open event. The latter is the current
technology, but each has their advantages/disadvantages.

Horses for courses as ever.

Bob


"Dave Peterson" wrote in message
...
I use application.match and application.vlookup (for example), because of
the
way errors are handled.

And Application contains fewer letters than worksheetfunction <bg.

I use xl2k and xl2002.

Bob Phillips wrote:

Alan,

Worksheetfunction is the way to do it since XL97. Apparently,
Application
was the way in worked in XL95, and has been retained for compatibility.
As
you not, Application does not give Intellisense, but there are also some
functions that just don't work with Worksheetfunction, so you have to
resort
to Application. And the error handling is different.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Alan" wrote in message
...
When I type "WorksheetFunction", the Intellisense opens
and shows "Round".

When I type "Application", the Intellisense opens, but
does not show "Round".

If I search for "Round" in the Object Browser, it only
appears in "WorksheetFunction" - nowhere else (question:
is the Object Browser always complete, and shows the same
things for everybody, or does it only show what each
user's References are pointing to?).

There is no "MISSING" in References (question: would it
appear under "M" for "MISSING", or would it appear under
the initial letter of the missing reference?).

Thanks for all your suggestions.

Regards,
Alan



-----Original Message-----
If you're using xl97, you could use: Application.round

But if you're using xl2k+, maybe you have a missing
reference.

Tools|References
Scroll down that list looking for MISSING

If you find one, uncheck it. You'll have to decide if it
was important.

(A missing reference will cause the procedure to not
compile--and might point at
any old line.)

Alan wrote:

I want to use "Round" in VBA. I get the message:

"Sub or Function not defined"

I can see the function in the Help. Am I missing a
reference?

Regards,
Alan

--

Dave Peterson

.


--

Dave Peterson

It's tough to keep pace with all the different versions! My place uses
Office97 for the users and Office2k for us technical staff, it's a real pain
in the backside but nobody listens...

Oh well...

--
Michael Hopwood


"Bob Phillips" wrote in message
...
Michael,

I'll use that as my excuse as to why I thought it wasn't there<vbg

Bob

"Michael Hopwood" wrote in message
...
I think it depends which version of Excel you have:

In E97 it is missing!

In E2000 it is present in the VBA.Math library

I'll bet the OP is using E97.

--
Michael Hopwood


"Bob Phillips" wrote in message
...
Oops. As Don points out I am wrong, and I thought I had found that in
the
past.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in message
...
Alan,

There is no Round VBA method, so you have to use the
worksheetfunction


?Worksheetfunction.Round(10.2,0)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Alan" wrote in message
...
I want to use "Round" in VBA. I get the message:

"Sub or Function not defined"

I can see the function in the Help. Am I missing a
reference?

Regards,
Alan