Round function in Word 2003

[bkirkman56]

I am trying to iterate through selected text in Word 2003 and if it's a number round it to a whole number.

I would love to use Excel's Round() function but I don't see how to apply
it in a Word document.

Below I posted what has been the closest I've come to a solution. Problem I am encountering is that it does not move to the next selected word when I hit my first "number" in the selected text.

VBA Code - This is in a Sub :
...
' Round numbers
MsgBox Selection.Words.Count & " words are selected"
Dim wrd As Range
Dim num As Integer
num = 0
For Each wrd In Selection.Words
If IsNumeric(wrd) And Not IsEmpty(wrd) Then
Dim str As Variant
str = Int(wrd.Text)
wrd.Text = str
num = num + 1
End If
Next wrd

MsgBox num & " numbers were rounded"
...

Sample text that is "selected" in the word document:

<Tstate000:q_mi> Q-MI0032
<Tstate001:q_mi> MICHIGAN
<Tstate003:q_mi> 2,965.0
<Tstate005:q_mi> 18
<Tstate007:q_mi> 90.951
<Tstate009:q_mi> 90.959
<Tstate011:q_mi> PUERTO RICO
<Tstate013:q_mi> 150.0
<Tstate015:q_mi> 1
<Tstate017:q_mi> 4.601
<Tstate019:q_mi> 4.944
<Tstate021:q_mi> USA
<Tstate023:q_mi> 145.0
<Tstate025:q_mi> 1
<Tstate027:q_mi> 4.448
<Tstate029:q_mi> 4.097

[bkirkman56]

A quick update - I changed the code to this:

' Round numbers
MsgBox Selection.Words.Count & " words are selected"
Dim wrd As Range
Dim num As Integer
Dim dnum As Double

num = 0
dnum = 0#

For Each wrd In Selection.Words
If IsNumeric(wrd) And Not IsEmpty(wrd) Then
dnum = Round(wrd.Text, 0)
'wrd.Text = dnum
num = num + 1
End If
Next wrd

MsgBox num & " numbers were rounded"

I now have a new problem - when trying to get the "words" from the selection as I am I found that the collection is 'delimiting' the "words".
For instance, when I get to 2,965.0 as the code loops thru the data I see one word as "2" the next as "965" the next as "." and the next as "0".
I sure wish this could be done in Excel but unfortunately it has to be Word. How do I get the complete number from Selection.Words ?

[macropod]

Hi bkirkman,

You could use something like:

Sub ConvertNumbers()
Dim i As Integer
Dim j As Integer
Dim wrdRange As Range
'Dim num As Integer
Dim dnum As String
'num = 0
dnum = 0
With Selection
For i = .Words.Count To 1 Step -1
If IsNumeric(.Words(i)) And Not IsEmpty(.Words(i)) Then
Set wrdRange = .Words(i)
For j = i To 1 Step -1
If .Words(j - 1).Text = "," Or .Words(j - 1).Text = "." Or IsNumeric(.Words(j - 1).Text) Then
wrdRange.MoveStart Unit:=wdWord, Count:=-1
Else: Exit For
End If
Next
i = j
dnum = Round(wrdRange.Text)
wrdRange.Text = dnum
'num = num + 1
End If
Next
End With
End SubNote that this code goes through the data backwards. That's because the word count changes every time you convert one of the numerical strings. I'm not sure what you use the 'num' variable for, so I've commented it out - delete or reinstate as appropriate.

[bkirkman56]

Macropod,
Thanks for the reply.
I tried the code but the problem is that the "word"(numbers) are not rounded to the nearest '1'.
It now assembles them correctly, for instance it does create 2,965.0 in the ouput but I would
like each number to be rounded to the nearest one if the .1 digit is .5 or greater.

I am looking at other options to do this but is there a way to correctly Round these numbers?

[Tommy]

This should work :)

Sub a()
'Round numbers
Dim mStr As String, mChk As Double
Dim mI As Long, mLineData() As String
Dim mLine() As String
mStr = Selection.Text
mLine = Split(mStr, Chr(11))
For mI = 0 To UBound(mLine, 1)
mLineData = Split(mLine(mI), " ")
If IsNumeric(mLineData(1)) Then
mChk = Val(Replace(mLineData(1), ",", ""))
mLineData(1) = IIf(mChk - Int(mChk) > 0.5, Format(Int(mChk) + 1, "#,###"), Format(Int(mChk), "#,###"))
mLine(mI) = Join(mLineData, " ")
End If
Next
Selection.Text = Join(mLine, Chr(11))
End Sub

[macropod]

Hi bkirkman,

I understood you to want whole numbers only since your own code had:
dnum = Round(wrd.Text, 0)
Consequently, my code rounds 0.5 and greater up, but less than 0.5 isrounded down.

To round the numbers to the nearest 0.5, simply change:
dnum = Round(wrdRange.Text)
to:
dnum = Round(wrdRange.Text * 2) / 2
or:
dnum = Format(Round(wrdRange.Text * 2) / 2, "#,##0.0")

[CreganTur]

Wouldn't the Round function be available if you set a reference to Microsoft Excel xx.0 Object Library? (where xx.0 is whatever version number you have).

This should make Excel's exact Round Function available for you to use in Word.

[macropod]

Hi Cregan,

Yes, but using Excel's worksheet functions takes a lot more work than simply adding a reference to Excel.

[Tommy]

All that overhead to round? IMHO I wouldn't do it, way too much for way too little.

my pennies :)