Parts of Speech of the WordList in excel using vba

[sanits591]

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]

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]

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]

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]

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]

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]

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]

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]

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?

[FONT=Fixedsys]Option Explicit[/FONT]
 
[FONT=Fixedsys]Public Sub PartsOfSpeech()[/FONT]
 
[FONT=Fixedsys]  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[/FONT]
 
[FONT=Fixedsys]  Set mObjWord = CreateObject("Word.Application")
 
  iMax = 1[/FONT]
 
[FONT=Fixedsys]  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[/FONT]
 
[FONT=Fixedsys]  For i = 3 To iMax
    Columns(i).EntireColumn.AutoFit
  Next i[/FONT]
 
[FONT=Fixedsys]End Sub[/FONT]

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]

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.