The Excel VBA routine below will check the spelling of the text in the current selected cell, and return either TRUE or FALSE in the adjacent cell to the right:
Sub SpellChecksub()
Dim Checkword As String, Result As Boolean
Checkword = Selection.Value
Result = Application.CheckSpelling(Checkword)
Selection.Offset(0, 1) = Result
End Sub
Now it would be convenient to have this routine in a UDF, but the code below always returns FALSE:
Function SpellCheck(CheckRange As Variant) As Boolean
Dim Checkword As String
Checkword = CheckRange(1, 1).Value
SpellCheck = Application.CheckSpelling(Checkword)
End Function
Can anyone tell me why?
Newton Excel Bach, not (just) an Excel Blog 
I think CheckRange(1, 1) is null
LikeLike
No, in the example shown CheckRange(1,1) returns “tree” if you step through the function.
LikeLike
dougaj4,
after reading the post I remembered this problem has already been discussed on Daily Dose of Excel some time ago and as far as I know it is still an open question:
http://www.dailydoseofexcel.com/archives/2005/03/31/spellchecking-one-cell/
See the second last comment by Doug Jenkins.
It is an interesting question but I guess as long as nobody at DDOE has an answer…
LikeLike
Robert – Thanks, – Doug Jenkins is me :). I had an e-mail about the DDoE post yesterday, so I thought I’d re-post it here.
LikeLike
Doug,
I am sorry, I didn’t know that. I followed the link assigned to your comment on DDOE, but it took me to your other website, not to Newton Excel Bach. I should have noticed though that the code in your DDOE comment and here is exactly the same.
Mea culpa. I’ll pay more attention.
LikeLike
Doug,
your question did not let me rest and I played around a bit.
If you define another (new) Excel Application object, it seems to work in a UDF as well. Here is my code:
Dim objXLS As New Excel.Application
Function SpellCheck(CheckRange As Variant) As Boolean
Dim Checkword As String
Checkword = CheckRange(1, 1).Value
SpellCheck = objXLS.CheckSpelling(Checkword)
End Function
What do you think?
LikeLike
Doug,
Try this function by typing it into any cell and in the immediate window:
Function SpellCheck() As Boolean
SpellCheck = Application.CheckSpelling(“pizza”)
End Function
Any cell gives False while the immediate window gives True. This most likely shows that Excel turns off the function during its internal recalculate event for cells. Maybe a design flaw, licensing issues, etc.
LikeLike
Robert – thanks, that seems to work.
I had to put the line:
Dim objXLS As New Excel.Application
inside the function.
Was there a reason you had it outside?
Another strange thing is that in Excel 2000 this new function works, but the sub doesn’t work at all. I get:
“Method ‘CheckSpelling’ of object ‘_Application’ failed
Gary – yes, I found that works as described.
Also in Excel 2000 the function always returns FALSE, but the immediate window gives the same error as the sub.
I haven’t tried it in Excel 2003.
LikeLike
Doug,
I declared objXLS as module-wide variable outside the function because this speeds up calculation noticeably. Not for one single cell, but if you are using the udf in a list of – let’s say – 20 or more items.
I am using Excel 2007 (in compatibility mode to Excel 2003) and it is working fine. What was the problem with the module-wide variable?
I can’t tell you anything about the strange error with Excel 2000. I have no Excel 2000 installation available.
LikeLike
Robert – OK, I found a noticeable delay even with just one cell, and putting the declaration at the top of the module definitely speeds things up.
My problem previously was that I didn’t put it up the top!
Thanks for your input.
LikeLike
Thanks to Robert for the info. I’ve previously posted this question on 2 other VB sites – both of which usually elicit reasonable answers reasonably quickly – with no response at all. Well done Robert!
Still, having to create a whole new excel app is some workaround. I first tried it with the new application being created and destryed in the function itself, because that seemed neater & I don’t like global variables. That was really slow. Using the global var takes about 40ms per function on my machine which is still pretty slow, but a lot better.
I still wonder why it does not simply work properly in the first place.
Tony
LikeLike
Tony,
thank you very much for your kind words.
I agree, it is quite a workaround and it is very slow even with the global variable. In a real application you would probably want to use the udf for larger lists and I guess the udf will be too slow for this.
I am not happy with this solution either, but it was the only idea I had…
Let’s wait and see, maybe someone else will be inspired by the post and comments and will show up with a better and more efficient solution.
LikeLike
I think the main problem maybe the CheckSpelling method starts a low-level thread that can’t be accessed during a recalculate event that is tied to the current Application thread (Just speculation). When you started a new application thread, you accessed its CheckSpelling method. This separate Application thread is not undergoing a recalculate event, so its CheckSpelling method works just fine.
LikeLike
Doug
I suspect that checkspelling is classified as command equivalent, and so can’t be called from a UDF.
One option would be for your function to set a timer and the timer trigger a command to check the spelling in a seconds time or whatever.
LikeLike
@ Robert
thanks for the link..
LikeLike