# Solved: VB,Excel Rounding(Up or Down?)



## urwethe (Jul 28, 2004)

I have a number , 360.5 . I need to round up to 361 in Visual Basic. 

When I round this number in Visual Basic, I have

Round(360.5,0) = 360 

When I try the Roundup function, I get:

Compile Error: Sub or Function Not Defined

When I round this number in Excel, I have

Round(360.5,0) = 361

Or, Alternatively, I have

Roundup(360.5,0) = 361

What the hockey puck is going on here??? How can I Roundup to 361 in Visual Basic?

Thanks for your help!


----------



## Zack Barresse (Jul 25, 2004)

Hi there,

How about like so ...


```
Option Explicit

Sub RoundUpPlease()
    Dim i As Single
    i = 360.5
    MsgBox WorksheetFunction.RoundUp(i, 0)
End Sub
```


----------



## bomb #21 (Jul 1, 2005)

What do you get with:

WorksheetFunction.RoundUp(360.5, 0)

? (I don't think RoundUp has a specific VBA equivalent, or whatever they're called)


----------



## cristobal03 (Aug 5, 2005)

Do you always want to round up? Doing some testing in the Immediate window of the VBA Editor shows me that the Round() method will round a *.5* number down, accurate to [edit] _about_ [/edit] 13 decimal places. That is,

Round(360.50000000000001) = 360

Round(360.5000000000001) = 361

[edit]
Ignore this: If you want to round up, why not just trim everything after the decimal and add 1?

I don't know what I was thinking. The appropriate way to change a decimal to an integer in VBA would be to use a conversion method, like *CInt* or *CLng*. The conversion methods have the same decimal limitation as the Round method.

Anyway, seems like this doesn't apply to OP's needs, so.
[/edit]

chris.


----------



## cristobal03 (Aug 5, 2005)

[bump]

Or use bomb's suggestion, which works much better than mine.

chris.

[edit]
And Zack's too, which I did not see.

Wow.
[/edit]


----------



## Zack Barresse (Jul 25, 2004)

Note: ROUNDING IN VBA IS DIFFERENT THAN AN EXCEL WORKSHEET FUNCTION ROUNDING.


----------



## urwethe (Jul 28, 2004)

Thanks for all your answers! I will use the worksheet function to do this. I could certainly use CInt(x) + 1 but would rather use an existing function to do the rounding. I am dealing with values not more than 2 decimal cases. In my research, I think the standard is to round down, but the company I work for always round up as Excel does. It certainly is confusing when excel handles the round function one way and VB handles the same function differently. I will update this thread after I try the suggested code tomorrow (8AM EST) . Thanks again!!


----------



## urwethe (Jul 28, 2004)

Let me explain how I solved this problem. First of all, I only need to round up if the value is .5 or greater, I need to round down if the value is .4 or less. 

The VB round function rounds down on .5, the excel function rounds up on .5. 

So, First I checked the checkbox in References for the Microsoft Excel Object Library.

Then, I used

worksheetfunction.round(360.5,0) RESULT: 361

Now my VB programs are consistent with the excel spreadsheets that the accountants are using and everyone is happy. 

(I will mark this thread as satisfied in one day after more people view it)


----------



## cristobal03 (Aug 5, 2005)

A note: if you're using *WorksheetFunction.Round* as a _substitute_ for VBA's Round method, your code won't port to other applications unless you include a reference in your compiler. If you need a modular way to replace VBA's Round method, you might want to think about writing a function to accommodate your needs.

Such a function might look a little something like this (not terribly efficient but will get the job done):


```
Public Function RoundMyNumber(ByVal dblNum As Double) As Long

' Notice that the argument data type and the return data type are
' different.  This isn't absolutely necessary, but it's my personal
' preference to use two containers if there's a conversion.

  Dim strTmp As String
  Dim intNumToAdd As Integer

  ' Make the floating-point a string to parse out the decimal
  strTmp = CStr(dblNum)

  ' Only want to run the code if there is a decimal in the number,
  ' otherwise there will be an error.
  If InStr(strTmp, ".") > 0 Then

    ' If the first character after the decimal is less than 5
    If Right(Mid(strTmp, InStr(strTmp, "."), 2), 1) < 5 Then
      intNumToAdd = 0
    Else
      intNumToAdd = 1
    End If

  ' If there is no decimal in the number, convert it to a long
  ' integer to satisfy the return data type.
  Else
    RoundMyNumber = CLng(dblNum)
    Exit Function
  End If

  ' Grab the digits before the decimal
  strTmp = Left(strTmp, Len(strTmp) - _
                        (Len(strTmp) - InStr(strTmp, ".") + 1))
  
  RoundMyNumber = CLng(strTmp) + intNumToAdd
End Function
```
That'll take any double floating-point and convert it to a long integer, rounding up at *.5* and down at *< .5*, and works in any application. But like I said, it's not very efficient.

chris.

[edit]
Commented my code a bit, and changed *CInt* to *CLng* in the no-decimal escape function routine.
[/edit]


----------



## urwethe (Jul 28, 2004)

Thanks for the input! I will test this out.


----------



## jbrisko (Nov 15, 2005)

Why don't you just use the Math.Ceiling Function?? Always rounds up to whole numbers, and Math.Floor always rounds down?


----------



## cristobal03 (Aug 5, 2005)

I thought I'd post just a bit more info that I've learned since I last hit this thread. The VB/VBA function *Round* does *not* always round down when the argument's floating-point is exactly .5, it rounds to _the nearest even number_. Apparently this is a mathematical convention they don't teach you in grade school. So, for example,

*Round(0.5) = 0*

and

*Round(1.5) = 2*

It's supposed to produce consistent rounding, though to my uneducated brain, always rounding up on exactly .5 is also consistent. Anyway, the code I popped in provides more "grade-school" type rounding.

That's all I had, I guess.

chris.


----------



## jbrisko (Nov 15, 2005)

For his situation, won't the Math.Ceiling and Math.Floor Functions work though? Because he is always Rounding to whole numbers? So I believe that should work for him.


----------



## cristobal03 (Aug 5, 2005)

I'm pretty sure in the OP's particular application, it wouldn't work to explicitly define the rounding direction. That is, OP didn't want to use Math.Ceiling on, say, 3.42, because he wanted to round up only at *>= .5*.

VBA's *Round* already does that, for the most part, but with the understanding that any value exactly equal to .5 will round to the nearest even number, not to the next greatest integer. Excel's *Round* worksheet function doesn't follow the same convention, and behaves like OP wanted. I was merely providing a (poor) solution to get the same functionality as the *Round* worksheet function without requiring the Excel object reference.

As an aside, all VBA's number conversion methods follow the same convention as VB *Round*.

chris.


----------

