Quote


A good programmer is a lazy programmer, because he writes minimum code.
--Anonymous


Sunday, January 30, 2011

AutoCorrect Macro

Friends,

When you chat you type like this "how r u? c u tmro". But when we type an official mail, we have to switch to that bad old English that we were taught in schools. I observed that whenever I type an official document, I always type "ur" then hit backspace and correct it to "your". Then I thought, "Why should I assign this correcting job to my computer?" When I thought more about it, I decided to use AutoCorrect feature that comes with MS Office.

One advantage of Office AutoCorrect is that if you make an entry in one office application, it will be available in all office applications. That means if you customize AutoCorrect in MS Word, that will be available to MS Excel, Outlook, PowerPoint, etc.

So, I opened MS Excel and added a few entries there. Here are the steps to do this in MS Office 2007.
  1. Click the Microsoft Office Button, and then click Word Options.
  2. Click Proofing.
  3. Click AutoCorrect Options.
  4. On the AutoCorrect tab, click to select the Replace text as you type check box.
  5. Click OK to close the AutoCorrect Options dialog box.
  6. Click OK to close the Word Options dialog box.
I added a few entries like please for pls, you for u, your for ur. This simplified my typing a lot, and after some time this became some thing that I cannot live without.

Then I thought of sharing this with friends. I can email them the steps to do this, but I know my friends, they are not going to spend five minutes for this. So I created an excel macro to do this.

What I did is I type all the auto correct entries I wanted to add in the Excel sheet. I  also put a button in the first worksheet of an Excel file and set text to Add.



Then I assigned the macro Button1_Click to that button. Here is the code for Button1_Click.

Option Explicit

Sub Button1_Click()
    AddAutoCorrect
End Sub

Private Sub AddAutoCorrect()
    Dim intRow As Integer
    Dim intReplacementCount As Integer
    Dim sheetAutoCorrect As Worksheet
    
    intReplacementCount = 0
    intRow = 2
    Set sheetAutoCorrect = ActiveWorkbook.Sheets("AddAutoCorrect")
    
    Do While sheetAutoCorrect.Cells(intRow, 2) <> ""
        If sheetAutoCorrect.Cells(intRow, 2) <> "" And _
                    sheetAutoCorrect.Cells(intRow, 3) <> "" Then
            Application.AutoCorrect.AddReplacement sheetAutoCorrect.Cells(intRow, 2), _
                    sheetAutoCorrect.Cells(intRow, 3)
            
            intReplacementCount = intReplacementCount + 1
        End If
        
        intRow = intRow + 1
    Loop
    
    MsgBox intReplacementCount & " enties added to Office AutoCorrect."
End Sub

Now adding the auto correct entries is as easy clicking the add button.

If you are not ready to do even this, you can download the Excel file here.

Thanks,

2 comments:

  1. how would i create a button to undo the autocorrect words that have been added?

    ReplyDelete
    Replies
    1. Good suggestion. I will implement a delete button, when I can find some time.

      Delete