#Excel Short and Sweep Tip #10 (Add Custom AutoCorrect with UDF) by David Hager

I was reading articles at exceloffthegrid.com and I came across this technique to add an autocorrect replacement in Excel.

https://exceloffthegrid.com/autocorrect-hack-to-speed-up-data-entry/

I thought that it would be useful to make a way to automate this process. So, I created the following VBA function procedure (put in a general module in VBE).

Function AutoCorrectAdd(ReplaceWhat As String, ReplaceWith As String)

Application.AutoCorrect.AddReplacement What:=ReplaceWhat,Replacement:=ReplaceWith

End Function

So, if cell A2 contained RobC and B2 contained https://powerpivotpro.com/, then the formula

= AutoCorrectAdd(A2,B2), run in a worksheet UDF

would add that autocorrect replacement the built-in AutoCorrect list. If you then type RobC, it would be replaced by https://powerpivotpro.com/.

You could also use this function in a Sub procedure. Say that you had an Excel list of nicknames and their corresponding e-mail addresses. You could create a Sub that operated on each row of the two column list with the AutoCorrectAdd function to convert the list to autocorrect replacements (not shown, a challenge to the reader 😊).

Hope that you find this useful.

Advertisements

One thought on “#Excel Short and Sweep Tip #10 (Add Custom AutoCorrect with UDF) by David Hager

  1. Pingback: #Excel Super Links #30 – shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s