Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
"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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
MROUND | Excel Worksheet Functions | |||
MROUND | Excel Discussion (Misc queries) | |||
MROUND | Excel Discussion (Misc queries) | |||
MROUND | Excel Discussion (Misc queries) | |||
Mround | Excel Discussion (Misc queries) |