MRound

[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

 

[Claus Busch]

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

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


Regards
Claus Busch

 

[pb]

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

 

[Ben McClave]

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

 

[pb]

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

 

[Ben McClave]

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

 

[pb]

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

 

[joeu2004]

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

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.

 

[Ron Rosenfeld]

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

In Excel 2003 VBA, MROUND is part of the ATP, not native to Excel. So you need to set a reference to atpvbaen.xls, and then call it directly.


Tools/References Select: atpvbaen.xls

Then, in the macro,

Result = Mround(Number, Mltplr)