Friday, January 18, 2008

Some small macros to ease repetitive tasks

Here are a few macros that I use to eliminate repetitive tasks. Note that I have recorded all these macros in MS Word 2003 (11.9134, SP2), and am releasing them here in good faith – it goes without saying that you use them at your own risk (not that I think they are risky in any way ) I do not write macros for a living; I just record and use them; so I'm sure there are better ways of writing and optimizing them. Should you wish to share small macros that you use with others, post them to me at honyakushaatgmaildotcom (after changing the "at" to its usual symbol, and replacing "dot"; you know what I mean), and I'll reproduce them here for the benefit of all.

1. Convert to lower case

Select phrase/sentence/sentences and run the macro.

Converts a sentence with upper case characters or title case characters to lower case characters.

Ex. QUICK BROWN FOX DIDN'T JUMP BUT HE DID HOP => quick brown fox didn't jump but he did hop

Here's the macro:

Sub ChangeToLowerCase()

'

' Changes UPPER CASE and Title Case to lower case characters

' Macro recorded 2007/08/28 by Gururaj Rao

Selection.Range.Case = wdLowerCase

End Sub

2. Convert to title case

Converts sentence or phrase to Title Case.

Select phrase/sentence/sentences and run the macro.

Ex. WHO STEPPED ON THE CAT? => Who Stepped on the Cat?

Note that after recording the macro, I added intelligent parsing such that the words "on" and "the" and several others are not capitalized, thanks to Allan Wyatts for his tip. Common words in Title Case that will not be capitalized include "over of the by to this is from a on in under for at."

Here's the macro:

' Changes UPPER CASE to Title Case characters

' Macro recorded 2007/08/28 by Gururaj Rao

'Modified to include intelligent parsing of common words

    Dim wlist As String
    Dim wrd As Integer
    Dim wCheck As String
 
    ' list of lowercase words, surrounded by spaces
    wlist = " over of the by to this is from a on in under for at"
 
    Selection.Range.Case = wdTitleWord
 
    For wrd = 2 To Selection.Range.Words.Count
        wCheck = Trim(Selection.Range.Words(wrd))
        wCheck = " " & LCase(wCheck) & " "
        If InStr(wlist, wCheck) Then
            Selection.Range.Words(wrd).Case = wdLowerCase
        End If
    Next wrd

End Sub

3. Convert hidden, formatted text to plain visible text

I often use this to copy sentence or phrase in hidden source text when translating a document using a CAT tool.


Here's the macro:

Sub Unbold()

'

' Unbold Macro

' 記録日 2003/11/03 記録者 Gururaj Rao

'Converts hidden text to non-hidden text

With Selection.Font

.NameFarEast = "MS 明朝"

.NameAscii = "MS 明朝"

.NameOther = "Times New Roman"

.Name = "MS 明朝"

.Size = 10.5

.Bold = False

.Italic = False

.Underline = wdUnderlineNone

.UnderlineColor = wdColorAutomatic

.StrikeThrough = False

.DoubleStrikeThrough = False

.Outline = False

.Emboss = False

.Shadow = False

.Hidden = False

.SmallCaps = False

.AllCaps = False

.Color = wdColorAutomatic

.Engrave = False

.Superscript = False

.Subscript = False

.Spacing = 0

.Scaling = 100

.Position = 0

.Kerning = 1

.Animation = wdAnimationNone

.DisableCharacterSpaceGrid = False

.EmphasisMark = wdEmphasisMarkNone

End With

End Sub

4. Create a 5-row by 5-column fixed width, colorful table

Place the cursor at the beginning of a newline and run the macro. This is what you get:


























Here's the macro:

Sub Create5ByFiveColorfulTable()

'

' Create5ByFiveColorfulTable Macro

' Macro recorded 2008/01/18 by Gururaj Rao

ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=5, NumColumns:= _

5, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _

wdAutoFitFixed

With Selection.Tables(1)

If .Style <> "Table Colorful 1" Then

.Style = "Table Colorful 1"

End If

.ApplyStyleHeadingRows = True

.ApplyStyleLastRow = True

.ApplyStyleFirstColumn = True

.ApplyStyleLastColumn = True

End With

End Sub

5. Sorting a table by selecting the column to be sorted, no header row

Place the cursor on the column in the table you wish to sort, and run the macro.

For example, place cursor in the first column of the table below and run it, you get the table on the right.







Here is the macro:

Sub SortTableDescNoHeaderRow()

'

' SortTableNoHeaderRow Macro

' Macro recorded 2008/01/18 by Gururaj Rao

Selection.Sort ExcludeHeader:=False, FieldNumber:="Column 1", _

SortFieldType:=wdSortFieldJapanJIS, SortOrder:=wdSortOrderDescending, _

FieldNumber2:="", SortFieldType2:=wdSortFieldJapanJIS, SortOrder2:= _

wdSortOrderAscending, FieldNumber3:="", SortFieldType3:= _

wdSortFieldJapanJIS, SortOrder3:=wdSortOrderAscending, Separator:= _

wdSortSeparateByCommas, SortColumn:=False, CaseSensitive:=False, _

LanguageID:=wdJapanese, SubFieldNumber:="Paragraphs", SubFieldNumber2:= _

"Paragraphs", SubFieldNumber3:="Paragraphs"

End Sub

4 comments:

Aseem Kumar said...

Changing text to UPPER CASE or lower case or First Letter In Upper Case And All Else In Lower Case can be accomplished by selecting the text and pressing Shift+F3...!!!

Gururaj Rao said...

Hi Aseem,
Thanks for your comment.
I recorded this macro to make smart changes over a huge document with hundreds of captions. It changes captions like "FRESH PAINT ON THE DOOR" to "Fresh Paint on the Door". Shift + F3 will change it to "Fresh Paint On The Door" which I didn't want. For stray cases, I am sure your suggestion of Shift+F3 is more than adequate. Have a great day!

Ryan Ginstrom said...

The title caps macro is clever. It's long annoyed me that Microsoft couldn't get title case right with the shift+F3 command.

Alejandro Moreno-Ramos said...

Thanks, you saved me some time...