Creating MROUND function

Hello,

MROUND is a built-in function in Excel that round a number to a specific multiple.

eg. =MROUND(21,5) rounds 21 to the nearest multiple of 5, which gives 20.

For some reason this function is not available in my Excel (2003) and so i thought it may be useful to write the function myself using VBA. However i'm not very familiar with VBA so can someone please help me to kick start my first VBA function.

Thanks!

ps. i've found out after that to add the function all i have to do is go Tools --> Add-ins and tick Analysis ToolPak in the dialog box.

Microsoft ISV Community Center Forums2

Hi

The solution is actually very simple. The following code gives the result you were looking for

Option Explicit

Function MyRound(Number As Double, Multiple As Double) As Double
MyRound = Round(Number / Multiple, 0) * Multiple
End Function

Do you need anything else

Regards

Peter Mo.

Gee thanks....

Btw just wondering what does the statement "Option Explicit" do

Hi

Option Explicit means that you have to declare all variables you use. It doesn't affect the code in this case, it's just good programming practice. If you are uncertain about anything just select it and then press F1 for help.

Regards

Peter Mo.

About the explicit feature. It may be annoying at time because you have to declare everything. You even have to decalare the variable for the For-loop. But it is better to use explicit feature. When you have a typo on the variable, it will not complie, and that's much more important. You don't want to end up using separate variable just because you got a typo.

Normally i would use the program's built-in help whenever i have queries. The thing is there are some missing pieces of my PC's VBA help file. I tried to look for solutions from Microsoft's website to fix it, but I was told to reinstall my MS Access, which i don't have in my PC!

Anyway thanks for the informative replies.

magicalclick wrote:
About the explicit feature. It may be annoying at time because you have to declare everything. You even have to decalare the variable for the For-loop. But it is better to use explicit feature. When you have a typo on the variable, it will not complie, and that's much more important. You don't want to end up using separate variable just because you got a typo.

It does seem like a hassle at first, if you're not used to it. On the other hand, if you're coming from C++ or C#, you won't even notice the difference. Requiring explicit variable definitions not only helps you avoid bugs like the one magicalclick mentioned, but it also encourages you to think ahead about how your routines will work and what resources they'll need to accomplish their goals. Another good thing is that, rather than all your implicitly defined variables being typed as Variant, you can explicitly type each one. This can also help eliminate bugs.