Consulting

Results 1 to 9 of 9

Thread: Need Help Getting Started for VBA Macro in Word

  1. #1
    VBAX Regular
    Joined
    Nov 2018
    Location
    Denver
    Posts
    9
    Location

    Need Help Getting Started for VBA Macro in Word

    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.

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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
    Last edited by macropod; 02-27-2019 at 02:10 PM.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    VBAX Regular
    Joined
    Nov 2018
    Location
    Denver
    Posts
    9
    Location
    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?

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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
    Last edited by macropod; 02-27-2019 at 02:08 PM.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    VBAX Regular
    Joined
    Nov 2018
    Location
    Denver
    Posts
    9
    Location
    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.

  6. #6
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  7. #7
    VBAX Regular
    Joined
    Nov 2018
    Location
    Denver
    Posts
    9
    Location
    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?

  8. #8
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Code edited. Try it now.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  9. #9
    VBAX Regular
    Joined
    Nov 2018
    Location
    Denver
    Posts
    9
    Location
    That seems to have fixed it. Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •