#1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 16
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 16
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 173
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 16
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 173
Default 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
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 16
Default 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
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 829
Default 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.

  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,045
Default 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)

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
MROUND emz486 Excel Worksheet Functions 3 April 22nd 09 07:40 PM
MROUND Brewer Excel Discussion (Misc queries) 2 June 27th 08 09:44 AM
MROUND Rob Connolly Excel Discussion (Misc queries) 4 August 3rd 07 11:33 PM
MROUND cheeser83 Excel Discussion (Misc queries) 7 January 6th 07 04:07 AM
Mround ynissel Excel Discussion (Misc queries) 1 June 2nd 05 05:32 PM


All times are GMT +1. The time now is 09:33 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"