hatman
Well-known Member
- Joined
- Apr 8, 2005
- Messages
- 2,664
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:
Where the contents of the first column of the named range contains these values:
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:
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:
I expected to be able to do this using this:
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.
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.
Anyway, let's discuss a couple of methods here:
Populate the Dictionary:
Code:
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 = NothingWhere the contents of the first column of the named range contains these values:
Code:
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 CaillouetteThe 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:
Code:
Meskill
Palmieri
Samplatsky
Albernaz)
Taddey
Kulp
Sitler
Doerschler
Albernaz
Gonyea
Keilich
Bonadies
Sasur
Giangrande
Kulakowski
Gebhart
CaillouetteWhich 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:
Code:
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 IfI expected to be able to do this using this:
Code:
set rnge2 = new rangeBut 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.
Code:
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.
