none
Format document with a macro RRS feed

  • Question

  • Hi, 

    I need to format the document with macro, and I am using "Exit For" function since  it seems much faster..

    So I need to format entire document using this macro, however so far I am unable to do it. 


    Kindly go through below macro and assist me if it is possible..

    (In this example I am searching for paragraph followed by a bulleted paragraph and apply a specific color)

    Sub Test1()


    Application.ScreenUpdating = False

    Dim Tbl As Table, Rng As Range, para As Paragraph
    With ActiveDocument
    For Each Para In .Paragraphs
    Set Rng = .Range(Activedocument.paragraphs)  ' this is where I am having a issue

    For Each para In Rng.Paragraphs
    With para.Range

       If .Characters.First = Chr(149) Then Exit For
       If .ParagraphFormat.FirstLineIndent < InchesToPoints(0) Then Exit For
       If .Bold = True Then Exit For
       If .Italic = True Then Exit For
          .Font.Color = 26265

       If .Font.Color <> 26265 Then Exit For
       If .Next.Characters.First <> Chr(149) Then Exit For
       If .Next.ParagraphFormat.LeftIndent + .Next.ParagraphFormat.FirstLineIndent <> InchesToPoints(0) Then Exit For
          para.Next.Range.Font.Color = wdColorBrightGreen

    End With
    Next

    Next

    End With
    Application.ScreenUpdating = True
    End Sub

    Thank you in advance      

    Regards, Supun Samarakoon

    Wednesday, October 26, 2016 5:16 AM

Answers

  • You're looping through all paragraphs, and within that loop, looping through all paragraphs again.

    It's much more efficient to look only at paragraphs that are bulleted or numbered.

    Sub Test1()
        Application.ScreenUpdating = False
        Dim para As Paragraph
        Dim para2 As Paragraph
        Dim s As String
        For Each para In ActiveDocument.ListParagraphs
            s = para.Range.ListFormat.ListString
            Select Case s
                Case "0" To "9", "a" To "z", "A" To "Z"
                    ' Numbering, ignore
                Case Else
                    ' Probably a bullet
                    Set para2 = para.Previous
                    If para2.Range.ListParagraphs.Count = 0 Then
                        para.Range.Font.Color = wdColorBrightGreen
                        para2.Range.Font.Color = 26265
                    End If
            End Select
        Next para
        Application.ScreenUpdating = True
    End Sub



    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Supunsam Wednesday, October 26, 2016 9:40 AM
    Wednesday, October 26, 2016 7:56 AM
  • Using a double loop as in your first post is not the solution.

    The code in your reply uses a single loop; with those complex conditions it will always take a lot of time, you'll have to live with that.

    To avoid this in the future, use styles to format your documents instead of applying direct formatting.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Supunsam Wednesday, October 26, 2016 9:40 AM
    Wednesday, October 26, 2016 9:31 AM

All replies

  • You're looping through all paragraphs, and within that loop, looping through all paragraphs again.

    It's much more efficient to look only at paragraphs that are bulleted or numbered.

    Sub Test1()
        Application.ScreenUpdating = False
        Dim para As Paragraph
        Dim para2 As Paragraph
        Dim s As String
        For Each para In ActiveDocument.ListParagraphs
            s = para.Range.ListFormat.ListString
            Select Case s
                Case "0" To "9", "a" To "z", "A" To "Z"
                    ' Numbering, ignore
                Case Else
                    ' Probably a bullet
                    Set para2 = para.Previous
                    If para2.Range.ListParagraphs.Count = 0 Then
                        para.Range.Font.Color = wdColorBrightGreen
                        para2.Range.Font.Color = 26265
                    End If
            End Select
        Next para
        Application.ScreenUpdating = True
    End Sub



    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Supunsam Wednesday, October 26, 2016 9:40 AM
    Wednesday, October 26, 2016 7:56 AM
  • Hi Hans,

    Thanks much for your quick respond. Only problem I'm having in my document is bullet lists are not formatted as actual lists. It's in text format. 

    Anyway the reason I tried this "exit for" method is to speed up my existing macro. Because looping through every paragraph and checking dozens of conditions take long time to execute. I thought it wold make a difference if I choose reverse methodology by exiting every time the condition is not met. Below is a example of my code:

    Sub para()
    Dim aPar As Paragraph

    For Each aPar In ActiveDocument.Paragraphs

    If aPar.Range.Characters.First = Chr(149) Then
    If aPar.FirstLineIndent = InchesToPoints(-0.25) Then
    If aPar.LeftIndent = InchesToPoints(0.5) Then
    If aPar.Previous.Range.Characters.Last.Previous = Chr(58) Then
       aPar.Range.Font.ColorIndex = wdBlue
    End If
    End If
    End If
    End If

    '2nd If Condition set
    '3rd If Condition set
    '4th If Condition set
    '5th If Condition set
    '6th If Condition set
    'Etc.......

    Next aPar

    End Sub

    This takes a considerable time to run in large documents and I would also like to know about that is there any way to use case statement with paragraph properties like left indent and first line indent.


    Regards, Supun Samarakoon

    Wednesday, October 26, 2016 8:35 AM
  • Using a double loop as in your first post is not the solution.

    The code in your reply uses a single loop; with those complex conditions it will always take a lot of time, you'll have to live with that.

    To avoid this in the future, use styles to format your documents instead of applying direct formatting.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Supunsam Wednesday, October 26, 2016 9:40 AM
    Wednesday, October 26, 2016 9:31 AM
  • Hi Hans,

    Appreciate your advice. This document is messed up big time. That's why I'm trying to format this with macros. AS you have mentioned it seems I have to live with time obstacle. Do you think breaking my code into multiple macros and call each of them with "call" is gonna help. I'm going to try that. Thank you very much for your advice. And finally can you tell me how to use Select case with paragraph properties. I mean paragraph properties always invloves unit conversions. So how can I deal with that factor. 

    I'm gonna mark your answer as the correct answer. Thank you very much for your time.


    Regards, Supun Samarakoon

    Wednesday, October 26, 2016 9:40 AM
  • If you break the code into multiple macros, each individual macro would run a bit faster than the one that tries to do it all. But if you execute all those macros from a single macro using Call, it would be just as slow again, or even slower.

    You could use code like this:

            Select Case para.LeftIndent
                Case InchesToPoints(0)
                    ...
                Case Is <= InchesToPoints(0.5)
                    ...
                Case Is <= InchesToPoints(1)
                    ...
                Case Else
                    ...
            End If


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, October 26, 2016 12:09 PM
  • Hi Hans,

    Thank You very much for your help. I think your opinion is correct. It is slower than expected. I think i have to ignore the time factor on this one. Thanks for the Demo of Case Statement. Appreciate it...


    Regards, Supun Samarakoon

    Wednesday, October 26, 2016 1:46 PM