Need Help Getting Started for VBA Macro in Word

[Alex Cheng]

Hi Everyone,

I need a little help getting started with a VBA macro I'm trying to program in MS Word. I write reports that deal with large numbers (millions and billions) and I need a macro to round and shorten/abbreviate these large numbers in my reports. For example, if a number is in the millions, I need that number to be rounded in abbreviated in a format like "123.43MM" or if the number is in the billions I need that number rounded and abbreviated in a format like "123.43B". I do have some limited programming experience so hopefully I can do all the fine tuning myself, but just need a little help to get started. Thanks in advance to anyone who can help me with this.

[macropod]

Try:

Sub Demo()
Application.ScreenUpdating = False
Dim i As Long
With ActiveDocument.Range
With .Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "<[0-9,.]{6,}"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.MatchWildcards = True
.Execute
End With
Do While .Find.Found
If .Characters.Last Like "[,.]" Then .End = .End - 1
i = Round(.Text / 1000, 0)
Select Case i
Case Is < 1000
Case Is > 999999: .Text = Format(Round(.Text / 1000000000, 2), "#.00") & "B"
Case Else: .Text = Format(Round(.Text / 1000000, 2), "#.00") & "M"
End Select
.Collapse wdCollapseEnd
.Find.Execute
Loop
End With
Application.ScreenUpdating = True
End Sub

[Alex Cheng]

Wow! Thanks, Paul.

Just curious, is there a way this code can be modified to work only on selected text instead of the whole document?

[macropod]

Try:

Sub Demo()
Application.ScreenUpdating = False
Dim i As Long, Rng As Range
Set Rng = Selection.Range
With Selection.Range
With .Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "<[0-9,.]{6,}"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.MatchWildcards = True
.Execute
End With
Do While .Find.Found
If .InRange(Rng) = False Then Exit Do
If .Characters.Last Like "[,.]" Then .End = .End - 1
i = Round(.Text / 1000, 0)
Select Case i
Case Is < 1000
Case Is > 999999: .Text = Format(Round(.Text / 1000000000, 2), "#.00") & "B"
Case Else: .Text = Format(Round(.Text / 1000000, 2), "#.00") & "M"
End Select
.Collapse wdCollapseEnd
.Find.Execute
Loop
End With
Application.ScreenUpdating = True
End Sub

[Alex Cheng]

Outstanding! The code works perfectly. Thank you very much, Paul.

Just for my curiosity, is there any good online documentation for VBA functions you could recommend to me? I'd like to be able to understand and modify this code moving forward.

[macropod]

They're pretty well all documented in the VBA help file, but you still need to work out how to apply them to a particular situation. My own learning in this area has been through studying code written by others and developing solutions for people like yourself.

[Alex Cheng]

Hey Paul,

Can you help me debug a small issue I'm seeing using your code? When I process the number 14,565,156 the code is sending back 14.56MM when I'm expecting 14.57MM. As far as I can tell, the code logic looks right, and when I duplicate the math using Excel, I get the expected 14.57MM. If I edit the original number to 14,566,156 (adding 10,000 to the original number) I get back the correct value of 14.57MM. It seems to me that the code isn't rounding up if the third digit from the decimal is 5, but it does if it's 6. Any idea why this is happening?

[macropod]

Code edited. Try it now.

[Alex Cheng]

That seems to have fixed it. Thanks again.