"Round" not found

[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

 

[Don Guillett]

try

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

 

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

 

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

 

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

 

[Chip Pearson]

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


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

 

[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

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

 

[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

 

[Bob Phillips]

Michael,

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

Bob

 

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

 

[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

 

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

 

[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

 

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




.

 

[Bob Phillips]

LOL. I can try

Bob

Can't lose them all

Thanks again.

Alan

 

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

 

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

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

 

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