"Round" not found

A

Alan

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
 
D

Don Guillett

try

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

Bob Phillips

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

Bob Phillips

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

Michael Hopwood

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

FxM

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
Click to expand...


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
 
F

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
Click to expand...

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
 
B

Bob Phillips

Michael,

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

Bob
 
D

Dave Peterson

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.)
 
A

Alan

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
 
B

Bob Phillips

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

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
 
A

Alan

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)




.
Click to expand...
 
B

Bob Phillips

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

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

Bob Phillips

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
 
M

Michael Hopwood

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