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

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


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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

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)