Solved: Round a number up in Word VBA

[sandam]

Been searching this morning for a method to round a number up in word but to no avail. eg. 17.453 => 17.46, That is I need to round up to two decimals places everytime.

Any help with this would be greatly appreciated,

Thanks
Andrew;?

[Steiner]

Youl could use
[VBA]? Format(17.459+0.005,"###0.00")[/VBA]

where the + 0.005 makes sure that all values are rounded up by raising each value above the magical .005 from where it's always rounded up. And don't worry if the value gets to 17.464, because this will be rounded down to 17.46, so you won't have a difference there.

Very crude, I know. But it serves my needs.

Daniel

[sandam]

that may be crude but its the best idea i've seen so far. I wish Word VBA would incorporate the ceiling function available in Excel, that would make life that much easier... I guess its too much to ask from microsoft. Thanks for your help. I'll incorporate it as soon as i get this documentation I'm now working on done (like maybe 2011 if i'm lucky).

Thanks again
Andrew;?

[Steiner]

You're welcome, and if you find a better solution, I'd be interested, too.

Daniel

[Tommy]

How about Round(17.459, 2)?

[Steiner]

In Word-VBA? At least in Word97 I can't find that one.

[Tommy]

It's in VBA.Math in Word 2000. It will not round up like requested unless
[VBA]
Round(17.459+0.005,2)
[/VBA]
as you showed. The only real difference is Format returns a string the Round returns a Double/Single. Which can be gotten around by
[VBA]
CDbl(Format(17.459+0.005,"###0.00"))
Val(Format(17.459+0.005,"###0.00"))
[/VBA]

[MOS MASTER]

Hi,

Yes VBA 6 does have a round function if it was there in 97...don't remember that..(have to check)

Remember that the Round function we all know off in Excel has a different result as the VBA Round function. (So results may differ from expectation)

More background: http://support.microsoft.com/default...b;en-us;194983

But you can still make custom Round function deppending on you're needs.
More backgound: http://support.microsoft.com/kb/196652/EN-US/

Enjoy!

[Steiner]

That borught me to an idea, but it just does not work as expected and I have no idea why:

[VBA]Public Function RoundUp(val As Double, digits As Integer)
Dim mult As Double, i%, res As Double

mult = -1
For i = 1 To digits
mult = mult * 10
Next i

res = (Int(val * mult) / mult)

RoundUp = res
End Function
[/VBA]

This should make use of the fact, the Int for negative numbers always round away from the 0. But it just does not do what it should, example:
? roundup(17.323,3)
17.324

If I go through the calculation stepts it should be:
17.323 * (-1000) = -17323
Int(-17323) = -17323
-17323 / (-1000) = 13.323

But if I combine the first 2 steps I get:
? int(17.323*(-1000))
-17324

Where's the problem??

By the way, this one should work correct:
[VBA]Public Function RoundUp(val As Double, digits As Integer)
Dim mult As Double, i%, res As Double

mult = -1
For i = 1 To digits
mult = mult * 10
Next i

res = val * mult
res = (Int(res) / mult)

RoundUp = res
End Function[/VBA]

But I still would like to know where the difference above comes from.

[sandam]

So many interesting things to consider. Thank you all for your help and suggestions. Steiner I think your problem may be related to the way VBA evaluates the brackets in the equation. I can't be sure though but that would be my first guess.

Thanks again to everyone
Andrew;?

[TonyJollans]

Hi Steiner,

I think your problem comes from the inherent inaccuracies in floating point arithmetic and there isn't much you can do about it.

[johnske]

How about:[vba]Sub RoundUp()
Dim M As Double, N As Double
N = 3.1415926 '< example
'use 10 for 1 dec place, 100 for 2 dec places, 1000 for 3
'(NOTE: to round off {not up}, omit the "+ 0.5")
M = Int(N) + ((100 * (N - Int(N)) + 0.5) \ 1) / 100
End Sub[/vba]

[sandam]

Thank you all for the great ideas.

I still think Ceiling should be incorporated into all VBA versions, it does exactly what is needed . Anyways. I think this thread is quite solved so I'm going to mark it so.



Andrew;?

[mark007]

A couple of generic rounding functions I use:

[VBA]
Function RoundDec(ByVal x As Double, Optional Direction As Integer = 0, Optional DecimalPlaces As Long = 0) As Double
x = x * 10 ^ DecimalPlaces
If Direction < 0 Then
RoundDec = Int(x)
ElseIf Direction > 0 Then
If x = Int(x) Then
RoundDec = Int(x)
Else
RoundDec = Int(x) + 1
End If
Else
If x - Int(x) < 0.5 Then
RoundDec = Int(x)
Else
RoundDec = Int(x) + 1
End If
End If
RoundDec = RoundDec / 10 ^ DecimalPlaces
End Function

Function RoundSF(ExactValue As Double, SigFigs As Integer, Optional Direction As Integer = 0) As Double

Dim x As Integer
x = Int(VBA.Math.Log(ExactValue) / VBA.Math.Log(10)) + 1
RoundSF = Int(RoundDec(ExactValue / 10 ^ (x - SigFigs), Direction)) * 10 ^ (x - SigFigs)

End Function
[/VBA]

For rounding up, down, nearest to decimal places or significant figures.

[MOS MASTER]

Hi,

Let's Round things up!

One thing is not mentioned in this subject. There is also a simple way to achieve the same rounding accuracy as Excel has...USE EXCEL!

A Simple function to take advantage off what's allready there:[VBA]
Public Function ExcelRound(dValue As Double, dDec As Double) As Double
Dim oExcel As Object
Set oExcel = CreateObject("Excel.Application")

With oExcel.WorksheetFunction
ExcelRound = .Round(dValue, dDec)
End With
Set oExcel = Nothing
End Function

Sub TestDouble()
MsgBox ExcelRound(10.543, 2)
MsgBox ExcelRound(10.547, 2)
End Sub
[/VBA]
Have fun testing all the options!

[mark007]

Hi Mos,

The problem with that approach is it will run very slow!

[MOS MASTER]

Hi Mos,

The problem with that approach is it will run very slow!

Hi Mark,

True..but intention was to add to the diversity off the discussion.
It still is a good possibility and it depends on what you wish to achieve if it will be slow or not...
(not suitable for batch programming off course)

See Yah!