MRound

  • Thread starter Thread starter pb
  • Start date Start date
P

pb

I am having a problem with MRound. At work we are using XL 2k3. I
can put "=mround(A1,.1)" into any worksheet cell and everything works
as advertised. But when I use it in a macro
"Result = Application.mround(Number, Mltplr)" I get the message
"Object doesn't support
this property or method". And yes, I have checked the "Analysis
ToolPak" and "Analysis ToolPak - VBA" on the Add-Ins window.
Any hints or suggestions as to what the problem is and a possible
solution other than writing my own UDF to do the same thing?
Thanks.
-pb
 
Hi,

Am Thu, 13 Dec 2012 09:19:41 -0800 (PST) schrieb pb:
I am having a problem with MRound. At work we are using XL 2k3. I
can put "=mround(A1,.1)" into any worksheet cell and everything works
as advertised. But when I use it in a macro
"Result = Application.mround(Number, Mltplr)" I get the message
"Object doesn't support
this property or method".
Click to expand...

try:
Result = WorksheetFunction.MRound(Number, Mltplr)


Regards
Claus Busch
 
Claus,

Thanks, but I still get the same thing. It works in a WS but not in VBA. Are there any references I might be missing or need in the VB editor? The following are checked:
* Visual Basic for Applications
* Microsoft Excel 11.0 Objects Library
* OLE Automation
* Microsoft Office 11.0 Objects Library
* Microsoft Forms 2.0 Objects Library

-pb
 
pb,

I think that you need a reference to the analysis toolpak (atpvbaen.xls).

If that doesn't work but the Round function works properly you could try this workaround:

Result = WorksheetFunction.Round(Number/Mltplr, 0)*Mltplr

Ben
 
Ben,

I kind of thought it was something like that. It has been many years since I had to do that on the home PC. But it is still not working. Do I also need to select the "funcres" reference?

-Paul
 
Paul,

I'm having trouble recreating the issue on my machine (I run 2010). Doing a quick internet search I came across a couple of possiblities. The most comprehensive discussion I found was at http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.misc/2009-08/msg04977.html.

Here are a couple of things to try.

1. Add a reference to "Microsoft Visual Basic for Applictions Extensibility"
2. Use a full reference to call the function, i.e.
Debug.Print [atpvbaen.xls].MRound(Number, Mltplr)
3. Try the Evaluate method instead:
Result = Application.Evaluate("=MRound(" & Number & ", " & Mltplr & ")")

Good luck,

Ben
 
Ben,

Thanks for the suggestions. #1 did not do anything, but #2 & #3 did.
I think I will use the DIY method of: X = Round( Y / Z, 0) * Z
It's a shame because it works on the home PC (Win 7, XL 2010).
Maybe I should get a job working for a company that will spend some money on technology to stay ahead of the stone age. *sigh*

-Paul
 
pb said:
I think I will use the DIY method of: X = Round( Y / Z, 0) * Z
It's a shame because it works on the home PC (Win 7, XL 2010).
Click to expand...

The difference is due to the fact that MROUND is part of the ATP in Excel
2003, but it is a standard function in Excel 2007 and later.

But ROUND(y/z,0)*z is more reliable anyway when z is a non-integer.

That is, it is more likely to match the internal representation of the
equivalent constant. And there are some corner-cases where MROUND provides
surprising results, IIRC.
 
Back
Top