#Excel: Generate a List of Antonyms Using an User-Defined Function (UDF)

It really is amazing what you can do with an Excel user-defined function (UDF). Here is a list (not comprehensive) of articles from my website that demonstrate clever and unusual uses of UDFs.

https://dhexcel1.wordpress.com/2017/07/12/excel-short-and-sweet-tip-26-showing-an-userform-with-a-worksheet-udf-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/07/excel-short-and-sweet-tip-25-playing-a-random-sound-with-a-worksheet-udf-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/01/automating-word-and-powerpoint-from-excel-with-a-worksheet-udf-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/19/excel-short-and-sweet-tip-23-open-windows-file-explorer-with-worksheet-udf-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/07/excel-exchange-rate-udf-with-symbol-lookup-by-david-hager/

https://dhexcel1.wordpress.com/2017/06/03/creating-an-excel-translator-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/23/excel-udf-using-google-api-to-return-the-elevation-of-an-address-by-david-hager/

https://dhexcel1.wordpress.com/2017/05/12/excel-creating-a-udf-with-the-vba-environ-function-and-using-it-to-make-a-table-of-environmental-variables-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/29/excel-worksheet-udf-that-adds-a-comment-to-any-cell-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/19/excel-modifying-shapes-from-an-udf-in-a-worksheet-cell-by-david-hager/

In this article, I will show how to use an Excel UDF to return a delimited string of antonyms. It uses Word VBA, so in order for the code to work, you must add a reference to the Microsoft Word Object library in the VBE, as shown below.

Antonym_VBE

Then, the following code for the UDF can be placed in a general module in the VBE.

Function AllAntonyms(TheWord As String)

Dim Alist

Alist = SynonymInfo(Word:=TheWord, LanguageID:=wdEnglishUS).AntonymList

For i = 1 To UBound(Alist)

If i = UBound(Alist) Then

DList = DList & Alist(i)

Else

DList = DList & Alist(i) & “,”

End If

Next

AllAntonyms = DList

End Function

The result for using this UDF in a worksheet cell with the word “excited” as the lookup for antonyms in shown in the following figure.

Antonym1

There are a number of possibilties for extending/modifying this example to other useful UDFs. I hope that you find this useful in that regard.

You can download the workbook here.

 

Thesaurus

 

 

Advertisements

3 thoughts on “#Excel: Generate a List of Antonyms Using an User-Defined Function (UDF)

  1. Pingback: New Links – 2018 | Newton Excel Bach, not (just) an Excel Blog

  2. Pingback: The New Microsoft Filter and Sort Functions are Great, but What About the Old Ones No One Know Existed? | 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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s