vba Excel 函数中的单个单词拼写检查
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10776191/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Spellcheck a single word in Excel function
提问by WhiskerBiscuit
This little Excel VBA function always returns false, no what word is passed in.
这个小小的 Excel VBA 函数总是返回 false，没有传入什么词。
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.
事实上，在 IDE 中我可以验证 Application.CheckSpelling("hello") 失败，尽管 Excel 拼写检查器确实检测到拼写错误。
What I'm trying to do is get a T/F value for each word if it is spelled correctly.
如果拼写正确，我想要做的是为每个单词获取 T/F 值。
采纳答案by Siddharth Rout
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.CheckSpellingwill not correct or offer to correct a misspelled word, it only returns Trueor False
Application.CheckSpelling不会更正或提供更正拼写错误的单词，它只会返回True或False
I tested
我测试过
?Application.CheckSpelling("hello")
?Application.CheckSpelling("hello")
in immediate window and it returned True
在即时窗口中，它返回 True
EDIT: Calling Application.CheckSpellingfrom 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. :)
编辑：Application.CheckSpelling从 UDF调用将始终返回False。上次我检查的时候，它仍然是一个错误，没有办法解决它。如果最近有更新，那么我不知道。:)
MORE EDIT
更多编辑
Here is your function slightly modified which will work as a UDF as well :)
这是您稍微修改的函数，它也可以用作 UDF :)
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
回答by Endre Both
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.
需要注意的一个陷阱是 Application.CheckSpelling 将返回 True 对于任何包含字符在您进行拼写检查的语言的代码页之外的文本。
For instance, checking n?in English returns True. Apparently Excel hasn't yet (as of version 2010) fully arrived in the Unicode world.
例如，检查n? 在英语中返回 True。显然 Excel 还没有（截至 2010 版）完全进入 Unicode 世界。
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):
如果这是你的应用程序中的一个问题，你要么必须事先筛选出代码页之外的字符，或者你可以借用Word的拼写检查功能，它没有这个错误，例如这样（改编自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:
现在 Unicode 检查正常，理论上，您可以提供字典文件路径作为 CheckSpelling 函数的参数，以检查您拥有字典文件的任何语言：
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).
然而，实际上检查是使用默认语言的主词典（在文件/选项/语言中设置）完成的，而不管您指定的词典（在 Word 2010 中检查，不确定以前的版本）。您只能手动更改该设置（并且必须重新启动 Word 才能使更改生效）。
The default language setting is governed by a registry key. In Office 2010:
默认语言设置由注册表项控制。在 Office 2010 中：
HKEY_CURRENT_USER\Software\Microsoft\Office.0\Common\LanguageResources\InstallLanguage
So in theory, you could automate the language change as well using VBA wrappers to Windows Scripting, WMIor WinAPIto 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.
因此，理论上，您也可以使用 VBA 包装器将语言更改自动化到Windows 脚本、WMI或WinAPI以更改注册表（然后重新启动 Word），但是在启用了 UAC 的 Windows 7 上我遇到了权限问题，这就是我放弃了这个实验。不过，我只尝试了 WinAPI 路线。
回答by Endre Both
While the bug in using the Excel Application objectstill exists, a UDF that requires it for the Application.CheckSpellingmethod can benefit from Early Bindingand a Staticvariable declaration.
虽然使用Excel Application 对象的错误仍然存在，但Application.CheckSpelling方法需要它的 UDF可以从早期绑定和静态变量声明中受益。
Function spellCheck(str As String) As Boolean
Static xlApp As New Excel.Application
spellCheck = xlApp.CheckSpelling(str)
End Function
The early binding speeds up the creation of the Excel.Application object. When used within Excel's VBA there is no need to use the CreateObject functionas the reference library exists.
早期绑定加快了 Excel.Application 对象的创建。在 Excel 的 VBA 中使用时，不需要使用CreateObject 函数，因为存在参考库。
The Static variable declaration continues to exist in its assigned state after the function has been exited and is not recast on subsequent uses of the UDF. This makes situations like using the UDF to fill down a long column or as the determination formula in a conditional formatting rule more efficient.
函数退出后，静态变量声明继续以其分配的状态存在，并且不会在 UDF 的后续使用中重新转换。这使得使用 UDF 填充长列或作为条件格式规则中的确定公式等情况更有效。
回答by panda-34
I bet you didn't do
我打赌你没有做
Application.SpellingOptions.DictLang = 1033
回答by WhiskerBiscuit
You are correct about the UDF. This little jobber helps though.
你对 UDF 是正确的。不过这个小工作人员有帮助。
Sub SpellCheckColumn()
Dim rRng As Range
Set rRng = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each rCell In rRng
If Not Application.CheckSpelling(rCell) Then
rCell.Offset(, 1) = "Checkspell Error"
Next rCell
End Sub
