Consulting

Results 1 to 6 of 6

Thread: Need Help Getting Started for VBA Macro in Word

  1. #1
    VBAX Regular
    Joined
    Nov 2018
    Location
    Denver
    Posts
    7
    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
    3,741
    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(i / 1000000, 2), "#.00") & "B"
          Case Else: .Text = Format(Round(i / 1000, 2), "#.00") & "M"
        End Select
        .Collapse wdCollapseEnd
        .Find.Execute
      Loop
    End With
    Application.ScreenUpdating = True
    End Sub
    Cheers
    Paul Edstein
    [MS MVP - Word]

  3. #3
    VBAX Regular
    Joined
    Nov 2018
    Location
    Denver
    Posts
    7
    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
    3,741
    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(i / 1000000, 2), "#.00") & "B"
          Case Else: .Text = Format(Round(i / 1000, 2), "#.00") & "M"
        End Select
        .Collapse wdCollapseEnd
        .Find.Execute
      Loop
    End With
    Application.ScreenUpdating = True
    End Sub
    Cheers
    Paul Edstein
    [MS MVP - Word]

  5. #5
    VBAX Regular
    Joined
    Nov 2018
    Location
    Denver
    Posts
    7
    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
    3,741
    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
    [MS MVP - Word]

Posting Permissions

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