MRound
 

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

MRound
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

MRound
 

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

MRound
 

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

MRound
 

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

MRound
 

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

MRound
 

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

MRound
 

"pb" wrote:
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.

MRound
 

On Thu, 13 Dec 2012 09:19:41 -0800 (PST), pb wrote:

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)