The Custom Dictionary

[hatman]

I've done some searching on the board, and couldn't really turn up much in the way of hardcore manipulation of Custom Dictionaries. Sure, threads about using the CheckSpelling method for Ranges and Worksheets... but what about automatically populating the dictionary with a slew of words. Or what about spell checking OTHER objects?

Anyway, let's discuss a couple of methods here:

Populate the Dictionary:

    Dim Nms As Range
    Dim Item
    Dim Cnt As Long
    Dim Dic As String
    Dim FS As Object
    Dim Flder_Exists As Boolean
    
    Set FS = CreateObject("scripting.filesystemobject")
    
    Dic = Replace(Application.StartupPath, "Excel\XLSTART", "Proof")
    
    Flder_Exists = FS.folderexists(Dic)
    
    Set FS = Nothing
    
    If Not Flder_Exists Then
    
        Exit Sub
        
    End If
    
    Dic = Dic & "\CUSTOM.DIC"
    
    Set Nms = Application.Range("Names")
    
    For Cnt = 1 To Nms.Rows.Count
    
        For Each Item In Split(Nms(Cnt, 1).Value, " ")
        
            If Not Application.CheckSpelling(Item, , True) Then
                
                Open Dic For Append As #1
                
                Print #1, Item
                
                Close #1
                
            End If
        
        Next Item
    
    Next Cnt
    
    Set Nms = Nothing

Where the contents of the first column of the named range contains these values:

Al Lamas
Bob Meskill
Bob Palmieri
Brian Shannon
Dale Cloud
Darren Samplatsky
DCMA (Jessica Albernaz)
Donna Grossman
Ed O'Connor
Ed Taddey
Galen Kulp
Glenn Sitler
Hugh Chan
Javier Cisneros
Jeff Doerschler
Jessica Albernaz
John Howe
John Mann
Ken Case
Larry Gonyea
Maria Keilich
Mark Bonadies
Mike Mickiewicz
Minh Nguyen
Paul Sasur
Rick Aikman
Scott Girard
Steve Giangrande
Teri Kulakowski
Tony Gebhart
Wayne Caillouette

The code is actually determining whether the individual names appear in any one of the dictionaries that the user has selected. If not, it gets added. From the above list, the Custom.dic gets populated with this list:

Meskill
Palmieri
Samplatsky
Albernaz)
Taddey
Kulp
Sitler
Doerschler
Albernaz
Gonyea
Keilich
Bonadies
Sasur
Giangrande
Kulakowski
Gebhart
Caillouette

Which seems appropriate at first glance, until one notices the double entry of Albernaz and Albenraz). Hmm, What could be going on? When the Spell Checker looks for Albernaz), it ignores the ), but since my loop variable has the ) in it, that is the string that gets populated to the file. In this case, he double entry is harmless... but what if I have a case where the double entry will NOT occur? I have a mismatch problem, that's what. I'm still working on how to handle this... if anyone has any suggestions, I'd be very interested.

Spell Check Another Object:

This seems like a no-brainer, at first glance... but somehow it got a little more complicated than I expected... and when I finished, the result was just very cool, from the POV of the user.

This code sits behind the Done button of the userform:

    If Me.TextBox3.Value <> "" Then
    
        Set App = New Excel.Application

        Set Wbk = App.Workbooks.Add
        
        Set Rnge2 = App.Sheets(1).Range("A1")
        
        Rnge2.Value = Me.TextBox3.Value
        
        Rnge2.CheckSpelling IgnoreUppercase:=True, AlwaysSuggest:=True
        
        Me.TextBox3.Value = Rnge2.Value

        Wbk.Saved = True
        
        Wbk.Close
        
        App.Quit
        
        Set Rnge2 = Nothing
        
        Set Wbk = Nothing
        
        Set App = Nothing

    End If

I expected to be able to do this using this:

set rnge2 = new range

But the nature of the Range object didn't allow it.

Next, I tried assigning some arbitrary blank cell in one of my worksheets to Rnge.

set rnge2 = activesheet.range("iv1")

For some weird reason I can't fathom, the spell checker decided to check spelling for the ENTIRE worksheet. That wasn't acceptable (nor do I have or desire a blank worksheet in my book), so I ended up with the more complicated solution above. The upshot is that at least it's transparent to the user, though the overhead of creating a new Application Object and a new Workbook object just to get an isolated Range object seems wasteful. Again, any suggestions are encouraged.

Other Neat Stuff:

This is my first attempt at doing anything with the Dictionaries through code. If anyone has any other cool tools or methods, I'd be interested in seeing them.

Edit: Forgot to do the cleanup of the new Wrokbook and Application... fixed above.