A Question of Spelling

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?

About these ads
This entry was posted in Excel, UDFs, VBA and tagged , , . Bookmark the permalink.

15 Responses to A Question of Spelling

  1. KC Cheung says:

    I think CheckRange(1, 1) is null

    Like

  2. dougaj4 says:

    No, in the example shown CheckRange(1,1) returns “tree” if you step through the function.

    Like

  3. Robert says:

    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…

    Like

  4. dougaj4 says:

    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.

    Like

  5. Robert says:

    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.

    Like

  6. Robert says:

    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?

    Like

  7. Gary Waters says:

    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.

    Like

  8. dougaj4 says:

    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.

    Like

  9. Robert says:

    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.

    Like

  10. dougaj4 says:

    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.

    Like

  11. Tony says:

    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

    Like

  12. Robert says:

    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.

    Like

  13. Gary Waters says:

    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.

    Like

  14. Simon says:

    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.

    Like

  15. Myea Amelia says:

    @ Robert
    thanks for the link..

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s