3

This question has already been asked for the C++ language but I need a function for VBA. I tried converting the C++ function to VBA, but it doesn't return the right values.

I need a function that does the following:

RoundUp(23.90, 5)
'return 25

RoundUp(23.90, 10)
'return 30

RoundUp(23.90, 20)
'return 40

RoundUp(23.90, 50)
'return 50

RoundUp(102.50, 5)
'return 105

RoundUp(102.50, 20)
'return 120

Here's what I have so far. It works most of the time, but returns incorrect values for numbers that are less than .5 less than the multiple. So the problem seems to be a rounding problem with how I'm calculating the remainder value.

Public Function RoundUp(dblNumToRound As Double, lMultiple As Long) As Double
    Dim rmndr As Long
    rmndr = dblNumToRound Mod lMultiple
    If rmndr = 0 Then
        RoundUp = dblNumToRound
    Else
        RoundUp = Round(dblNumToRound) + lMultiple - rmndr
    End If
End Function

For Example:

RoundUp(49.50, 50)
'Returns 49.50 because rmndr = 0
Ken White
  • 123,280
  • 14
  • 225
  • 444
HK1
  • 11,941
  • 14
  • 64
  • 99
  • 1
    What's the host for your VBA code? Excel? – RBarryYoung Sep 07 '13 at 21:38
  • 1
    Also, what's "Up"? Is `RoundUp(-2.4, 1)` gonna be `-3` or `-2`? – sehe Sep 07 '13 at 21:41
  • In my case the function won't be used for negative values but your question may be very important in other contexts. The VBA host is MS Access. I'll retag accordingly. – HK1 Sep 07 '13 at 22:07

3 Answers3

6

I'd simply divide by the lMultiple, round up and multiply again.

Assuming you indeed always want to round up (also for negative numbers):

Public Function RoundUp(dblNumToRound As Double, lMultiple As Long) As Double
    Dim asDec   as Variant
    Dim rounded as Variant

    asDec = CDec(dblNumToRound)/lMultiple
    rounded = Int(asDec)

    If rounded <> asDec Then
       rounded = rounded + 1
    End If
    RoundUp = rounded * lMultiple
End Function

I'm not actually a VBA programmer, so this might need a tweaked comma or two. However the important thing is:

  • Use Decimal (variant subtype) for precision
  • Let VB do the math for you
HK1
  • 11,941
  • 14
  • 64
  • 99
sehe
  • 374,641
  • 47
  • 450
  • 633
  • Yeah, yours does need quite a bit of modification to work. CDec is not a valid function and values cannot be assigned to variables in the same line as their dimension declaration. All that aside, I'm still getting incorrect values from your function. I'm suspicious this may have to do with Banker's rounding in VBA but I'm not sure. – HK1 Sep 07 '13 at 21:56
  • I'm not using a round function... Also, [`CDec` is a valid VBA function](http://www.techonthenet.com/excel/formulas/cdec.php), but perhaps it's specific to the host (Excel) – sehe Sep 07 '13 at 21:58
  • Oh, **don't** use `CInt`, since it will indeed do rounding. I wrote `Int` for a reason. You might have luck with `Fix` (see e.g. [here](http://msdn.microsoft.com/en-us/library/xh29swte(v=vs.90).aspx)) or something like `Floor` if you can find it – sehe Sep 07 '13 at 22:00
  • Yeah, OK. I see that CDec is a valid function, contrary to what I wrote. I'm using MS Access 2010, BTW. – HK1 Sep 07 '13 at 22:02
  • I'm not sure what I was doing wrong before but it's working beautifully now. I did modify your answer to make it valid VBA. The let statements were invalid and "Then" is required in the If statement. – HK1 Sep 07 '13 at 22:04
  • @HK1 Feel free to edit my answer to reflect your changes! Ah, I see you did. Cheers. – sehe Sep 07 '13 at 22:06
0

Worth trying WorksheetFunction.Ceiling method (Excel)

WorksheetFunction.Ceiling(27.4,5)

Above example will return 30. Here is Link: https://learn.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.ceiling

nnaitik
  • 21
  • 3
-2

A far simpler solution is to add .5 to the number before rounding:

1.1 -> Round(1.1+.5, 0) -> 2
Zoe
  • 27,060
  • 21
  • 118
  • 148
Joe Sweeney
  • 1
  • 2