Monthly Archives: May 2018

#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