# Parts of Speech of the WordList in excel using vba



## sanits591 (Jun 24, 2011)

I have come across another code, which does the similar function in MS Word, like antonyms, but it does for "Parts of Speech".

Now, what i would be requesting to have the code in excel VBA, that there is a WordList in Col A, and all the parts of speech are marked on the top row in the columns to the right, e.g. Col-B-Adjective, Col-C-Adverb, Col-D-Conjunction and so on.

As soon as the code is run, then all the cells from the Col-b to right gets filled up with appropriate word with respect to the header row, i.e. Parts of Speech of the Col A.

The below code does this in MS Word, probably this would be assisting in developing in Excel.

Anticipatory thanks!


```
Sub parts_of_speech()

Set mySynInfo = Selection.Range.SynonymInfo
If mySynInfo.MeaningCount <> 0 Then
    myList = mySynInfo.MeaningList
    myPos = mySynInfo.PartOfSpeechList
    For i = 1 To UBound(myPos)
'wdAdjective, wdAdverb, wdConjunction, wdIdiom, wdInterjection, wdNoun, wdOther, wdPreposition, wdPronoun, and wdVerb.
        Select Case myPos(i)
            Case wdAdjective
                 pos = "adjective"
            Case wdNoun
                 pos = "noun"
            Case wdAdverb
                 pos = "adverb"
            Case wdVerb
                 pos = "verb"
            Case wdConjunction
                 pos = "Conjunction"
            Case wdIdiom
                pos = "Idiom"
            Case wdInterjection
                pos = "Interjection"
            Case wdPreposition
                pos = "Preposition"
            Case wdPronoun
                pos = "Pronoun"

            Case Else
                 pos = "other"
        End Select
        MsgBox myList(i) & " found as " & pos
    Next i
Else
    MsgBox "There were no meanings found."
End If

End Sub
```


----------



## Ruddles (Jun 24, 2011)

I suggest you try this code in Word first. I'm not sure it works in quite the way you're expecting.


----------



## sanits591 (Jun 25, 2011)

Yes, the below code works fine in MS Word, need to paste in the MS Word VBA Standard module.


I am seeking for a similar way, as jonmo1 resolved for Antonyms in the earlier post.


Thanks!


```
Sub parts_speech()
Set mySynInfo = Selection.Range.SynonymInfo
If mySynInfo.MeaningCount <> 0 Then
myList = mySynInfo.MeaningList
myPos = mySynInfo.PartOfSpeechList
For i = 1 To UBound(myPos)
Select Case myPos(i)
Case wdAdjective
pos = "adjective"
Case wdNoun
pos = "noun"
Case wdAdverb
pos = "adverb"
Case wdVerb
pos = "verb"
Case Else
pos = "other"
End Select
MsgBox myList(i) & " found as " & pos
Next i
Else
MsgBox "There were no meanings found."
End If
End Sub
```


----------



## Ruddles (Jun 27, 2011)

Try this. Paste the code into a new general code module and add a reference to the Microsoft Word Object Library. Put your lookup words in column A. Select the ones you want to look up and run the macro.

Any good?


```
Option Explicit
 
Public Sub PartsOfSpeech()
 
  Dim mObjWord As Word.Application
  Dim mySynInfo As Word.SynonymInfo
  Dim myList As Variant
  Dim myPos As Variant
  Dim i As Integer
  Dim iMax As Integer
  Dim thisPos As String
  Dim oCell As Range
 
  Set mObjWord = CreateObject("Word.Application")
 
  iMax = 1
 
  For Each oCell In Selection
    oCell.Offset(0, 1).Resize(1, 99).ClearContents
    If oCell.Column = 1 And Not IsEmpty(oCell) Then
      Set mySynInfo = SynonymInfo(Word:=oCell.Value, LanguageID:=wdEnglishUS)
      oCell.Offset(0, 1) = "'(" & CStr(mySynInfo.MeaningCount) & ")"
      If mySynInfo.MeaningCount <> 0 Then
        myList = mySynInfo.MeaningList
        myPos = mySynInfo.PartOfSpeechList
        If i > iMax Then iMax = i
        For i = 1 To UBound(myPos)
          Select Case myPos(i)
            Case wdAdjective
              thisPos = "adjective"
            Case wdNoun
              thisPos = "noun"
            Case wdAdverb
              thisPos = "adverb"
            Case wdVerb
              thisPos = "verb"
            Case wdConjunction
              thisPos = "conjunction"
            Case wdIdiom
              thisPos = "idiom"
            Case wdInterjection
              thisPos = "interjection"
            Case wdPreposition
              thisPos = "preposition"
            Case wdPronoun
              thisPos = "pronoun"
             Case Else
              thisPos = "other"
          End Select
          oCell.Offset(0, i + 1) = myList(i) & " (" & thisPos & ")"
        Next i
      Else
        oCell.Offset(0, 2) = "No meanings found"
      End If
    End If
  Next oCell
 
  For i = 3 To iMax
    Columns(i).EntireColumn.AutoFit
  Next i
 
End Sub
```


----------



## sanits591 (Jun 27, 2011)

You really did it. Ruddles, you are always been a very helpful guy in resolving my problems.

Thanks a Lot!


----------



## Ruddles (Jun 27, 2011)

Thanks for the feedback. The invoice is going out to you in tonight's post! 

On a serious note, I've learned something because I had no idea how to do this before you asked. So thanks to you too!


----------



## makiwara (May 26, 2018)

Hi! I 
- insert my word list to column "A"
- and add the word office library as reference 
- and in the end I select the cells in column "A" which I want to examine.

But the code crashes: "User defined type not defined" and then highlights this row:
Dim *mObjWord As Word.Application*

Any idea? Thank you for your answer have a nice day! 



Ruddles said:


> Thanks for the feedback. The invoice is going out to you in tonight's post!
> 
> On a serious note, I've learned something because I had no idea how to do this before you asked. So thanks to you too!


----------



## DanDanTheCheerleadingMan (Nov 20, 2018)

Just seconding @makiwara's comment as well.


----------



## Ruddles (Nov 20, 2018)

Have you added a reference to the Microsoft Word Object Library - *Tools *> *References *in the VB Editor?


----------



## Macropod (Nov 21, 2018)

See also: https://www.mrexcel.com/forum/excel...ent-parts-speech-post4700778.html#post4700778


----------



## sanits591 (Jun 24, 2011)

I have come across another code, which does the similar function in MS Word, like antonyms, but it does for "Parts of Speech".

Now, what i would be requesting to have the code in excel VBA, that there is a WordList in Col A, and all the parts of speech are marked on the top row in the columns to the right, e.g. Col-B-Adjective, Col-C-Adverb, Col-D-Conjunction and so on.

As soon as the code is run, then all the cells from the Col-b to right gets filled up with appropriate word with respect to the header row, i.e. Parts of Speech of the Col A.

The below code does this in MS Word, probably this would be assisting in developing in Excel.

Anticipatory thanks!


```
Sub parts_of_speech()

Set mySynInfo = Selection.Range.SynonymInfo
If mySynInfo.MeaningCount <> 0 Then
    myList = mySynInfo.MeaningList
    myPos = mySynInfo.PartOfSpeechList
    For i = 1 To UBound(myPos)
'wdAdjective, wdAdverb, wdConjunction, wdIdiom, wdInterjection, wdNoun, wdOther, wdPreposition, wdPronoun, and wdVerb.
        Select Case myPos(i)
            Case wdAdjective
                 pos = "adjective"
            Case wdNoun
                 pos = "noun"
            Case wdAdverb
                 pos = "adverb"
            Case wdVerb
                 pos = "verb"
            Case wdConjunction
                 pos = "Conjunction"
            Case wdIdiom
                pos = "Idiom"
            Case wdInterjection
                pos = "Interjection"
            Case wdPreposition
                pos = "Preposition"
            Case wdPronoun
                pos = "Pronoun"

            Case Else
                 pos = "other"
        End Select
        MsgBox myList(i) & " found as " & pos
    Next i
Else
    MsgBox "There were no meanings found."
End If

End Sub
```


----------



## Randyrewls (May 20, 2020)

Hi All, thanks for the fantastic solution above, it was EXACTLY what I was looking for -nice one Ruddles! However, since this was the only place *in existence* with the correct solution on how to successfully achieve this, I find myself coming back to the well to see if you all might be able to help solve another very closely related task....  which is:

*To auto-pull the DEFINITION of the word in Column A of the Spreadsheet, from the "Smart Lookup" Office object, which pulls this information via web search (Bing), and insert that paragraph of text into Column B, using a VBA macro or formula. *

I'm still very much learning VBA, so it's not immediately intuitive to me where to look, but I cannot find ANY DOCUMENTATION on the "Smart Lookup" object(s) _anywhere, _but my common sense tells me that if Excel and Word are able to pull this information and display it so readily on the screen, we should be able to do the same within any cells we want! So why is this so hard to find?!? I'm pulling my hair out! 

Can you brilliant problem-solvers help? I would be incredibly grateful!


----------



## Ruddles (May 22, 2020)

I'm not having much luck finding any information about using VBA to capture the results of the *Smart Lookup* function.  If you can find anything helpful on the Web, I don't mind having a look at it and trying to apply it in practice.  Perhaps I'm just not hitting the right combination of search keywords.


----------



## Randyrewls (May 22, 2020)

I searched HIGH and LOW and couldn't find a single thing across the entire internet... couldn't even find anything within Microsoft's documentation. The problem I'm having is the NAME. Because they decided to call it "*Smart Lookup*" and it has "*Lookup*" in the name, if it is out there... it's getting absolutely BURIED under every single other VLOOKUP, HLOOKUP, XLOOKUP, etc. 

I was praying you guys might have some inherent knowledge of Microsoft's Object Library, or at least how to explore it? Now I'm starting to lose hope...


----------



## Eric W (May 22, 2020)

One feature of Google is that if you put your search terms in quotes, it looks for those words together.  So try using this to search with:

VBA "Smart Lookup"

and you should get some better hits.  Unfortunately, there's still not a lot out there.  Here are 2 interesting ones:









						Macro
					

Hi I have around 5000 words which I want to see them in excel Smart Look Up for meaning. Instead of clicking every single word to see the details, I would like to run a macro that would put the



					answers.microsoft.com
				












						Replicate Smart Lookup using a macro
					

How to replicate the Smart Lookup feature in Outlook using a different search engine.




					www.slipstick.com
				





The first one basically says that Smart Lookup isn't available via VBA yet.  The second one shows a way to replicate the functionality.  I haven't got time to actually work through it right now, but maybe you'll be able to figure it out.  Good luck!


----------



## Randyrewls (May 22, 2020)

Thanks Eric. I came across both of those this past Monday  This "Smart Lookup" is admittedly, not very smart. I honestly still don't understand how it could be an Object within the application that allows Office to pull beautifully formatted definitions from the web (regardless of search engine, i.e. Bing, Google, etc. which I could care less) to display them within the Insights Pane, and yet not be a referenceable object that we would be able to display within a Cell. 

I've been trying to crack this problem for the past 5 days with a few all-nighters now and I am pretty darn close to admitting defeat. Will = Broken.


----------



## Ruddles (May 22, 2020)

Rather than *Smart Lookup*, if you're just looking for the definition(s) of a word, could you not use any of the existing online English dictionaries?


----------



## Randyrewls (May 22, 2020)

Not exactly...  since many of the entries in Column A will actually end up being Common Phrases using a dictionary definition wouldn't suffice. So... I suppose I wasn't that fair when I said that the "Smart Lookup" wasn't that smart. It's actually a LOT smarter than a simple English dictionary, which is why I want it so badly.


----------



## iammurphy (Nov 10, 2021)

Ruddles said:


> Try this. Paste the code into a new general code module and add a reference to the Microsoft Word Object Library. Put your lookup words in column A. Select the ones you want to look up and run the macro.
> 
> Any good?
> 
> ...


Can you list out detailed instructions bcoz excel is giving me a syntax error. Kindly point out what part of the code is to be edited as per user requirements. I only need synonyms separated by comma in Column B of words listed in Column A. If posible please attach a working macro enabled excel file. Thank you.


----------



## Ruddles (Nov 11, 2021)

I'm not able to look at this in detail at the moment but first of all, please could you check that after pasting the code in, make sure you remove any occurrences of the text *FONT=Fixedsys* and any square brackets which surround it, then try it again.

If it fails again, please post the wording of the error message and the line of code where execution stopped.  That sort of information is always helpful when trying to track down a problem.


----------

