This little Excel VBA function always returns false, no what word is passed in.
Function SpellCheck(SomeWord As String)
SpellCheck = Application.CheckSpelling(SomeWord)
End Function
In fact, in the IDE I can verify that Application.CheckSpelling("hello") fails, though the Excel spellchecker does detect misspellings.
What I'm trying to do is get a T/F value for each word if it is spelled correctly.
Note: To quickly add a word to a dictionary, right click the word in a document, and select Add to dictionary. The word is added to your default dictionary.
First, make sure your settings are correct by going to Tools → Spelling and Grammar → Options and make sure the box Check spelling as you type is checked.
Like I mentioned in my comment it works.
Option Explicit
Sub Sample()
MsgBox SpellCheck("hello") '<~~ Returns True
MsgBox SpellCheck("daasd") '<~~ Returns False
End Sub
Function SpellCheck(SomeWord As String) As Boolean
SpellCheck = Application.CheckSpelling(SomeWord)
End Function
Application.CheckSpelling
will not correct or offer to correct a misspelled word, it only returns True
or False
I tested
?Application.CheckSpelling("hello")
in immediate window and it returned True
EDIT: Calling Application.CheckSpelling
from UDF would always return False
. Last time I checked, it was still a bug and there was no way around it. If there is a recent update on that then I am not aware of it. :)
MORE EDIT
Here is your function slightly modified which will work as a UDF as well :)
Got the idea from this link
Function SpellCheck(rng As Range) As Boolean
Dim oxlAp As Object
Set oxlAp = CreateObject("Excel.Application")
SpellCheck = oxlAp.CheckSpelling(rng.Value)
oxlAp.Quit
Set oxlAp = Nothing
End Function
One pitfall to watch out for is that Application.CheckSpelling will return True for any text that has a character outside the codepage of the language for which you do the spellchecking.
For instance, checking nő in English returns True. Apparently Excel hasn't yet (as of version 2010) fully arrived in the Unicode world.
If that's a problem in your application, you either have to screen out text with characters outside the codepage beforehand, or you can borrow Word's spellchecking function, which doesn't have this bug, for instance like this (adapted from www.vb-tec.de):
Public Function CheckSpellingWd( _
ByRef Text As String, _
Optional ByVal IgnoreUpperCase As Boolean = False, _
Optional ByVal ReUse As Boolean = True _
) As Boolean
'Reuse Word object on next call
Static wd As Word.Application
If Len(Text) > 0 Then
'create Word object on first call
If wd Is Nothing Then
Set wd = New Word.Application
wd.DisplayAlerts = wdAlertsNone
End If
'Do spellcheck
CheckSpellingWd = wd.CheckSpelling(Text, , IgnoreUpperCase)
Else
'Return True on empty string
CheckSpellingWd = True
End If
End Function
Now Unicode is checked fine, and in theory, you can provide a dictionary file path as a parameter to the CheckSpelling function to check in any language you have a dictionary file for:
Application.CheckSpelling(Word, CustomDictionary, IgnoreUppercase, MainDictionary, _
CustomDictionary2, CustomDictionary3, CustomDictionary4, CustomDictionary5, _
CustomDictionary6, CustomDictionary7, CustomDictionary8, CustomDictionary9, _
CustomDictionary10)
In reality however the check is done using the main dictionary of the default language (as set in File/Options/Language) regardless of the dictionary you specify (checked in Word 2010, not sure about previous versions). You can only change that setting manually (and have to restart Word for the change to come into effect).
The default language setting is governed by a registry key. In Office 2010:
HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Common\LanguageResources\InstallLanguage
So in theory, you could automate the language change as well using VBA wrappers to Windows Scripting, WMI or WinAPI to change the registry (and then restart Word), but on Windows 7 with UAC enabled I ran into permission problems, and this is where I gave up on the experiment. I only tried the WinAPI route though.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With