Auto Correct Object

The AutoCorrect object has properties and methods that allow you to work with Excel's AutoCorrect features. The following two procedures will get you started working with AutoCorrect programmatically. The first procedure uses the ReplacementList method to retrieve common misspelled words and their automatic replacements into an array. The procedure then reads the values of this array and enters them into a worksheet so that you can print them out easily. If you need to update your AutoCorrect list with a number of new entries, you may want to enter them in a worksheet instead of working with the provided dialog box. The first column would hold the misspelled words, and the next column would contain the corrected word. You could then use the second VBA procedure shown below to update the AutoCorrect list with your new entries quickly and easily.

' this procedure generates a list of AutoCorrect entries Sub Auto_Correct()

Dim myList As Variant Dim i As Integer myList = Application.AutoCorrect.ReplacementList ActiveSheet.Cells(1, 1).Select

For i = LBound(myList) To UBound(myList) With ActiveCell

.0ffset(0, 0).Value = myList(i, 1) .0ffset(0, 1).Value = myList(i, 2) .0ffset(1, 0).Select End With


ActiveSheet.Columns("A:B").AutoFit Cells(1, 1).Select End Sub

To add a number of entries to the AutoCorrect list, type your entries in any two columns of a worksheet (as shown below) and run the procedure that follows.

advent age advantage knowl^t ¡iMQwIedqs

Sub Auto_Correct_Batch_Add() Dim myRange As Range Dim myList As Variant Dim strReplaceWhat As String Dim strReplaceWith As String

' prompt user to select data for processing ' the Type argument ensures that the return value is ' a valid cell reference (a Range object). Set myRange = Application.InputBox( _

Prompt:="Highlight the range containing your list", _

Title:="List Selection", _

If myRange.Columns.Count <> 2 Then Exit Sub

' save all the values in the selected range to an array myList = myRange.Value

' retrieve the values from the array and ' add them to the AutoCorrect replacements For i = LBound(myList) To UBound(myList) strReplaceWhat = myList(i, 1) strReplaceWith = myList(i, 2)

If strReplaceWhat <> "" And strReplaceWith <> "" Then Application.AutoCorrect.AddReplacement _ strReplaceWhat, strReplaceWith End If Next End Sub

0 0

Post a comment