User Defined Function Not working

[Naresh Goud]

Dear Sir,
i am very new to VBA.... i thought of writing one simple code to learn how to write user defined function , i went into the google found a small vba code of making user defined function .

" CountWords Function will count words in single cell, or range of cells."

The Code
Function CountWords(rRange As Range) As Long
Dim rCell As Range, lCount As Long

'''''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid.com'''''''''''''''''''
'Count whole words
'''''''''''''''''''''''''''''''''''''''''

For Each rCell In rRange
lCount = lCount + _
Len(Trim(rCell)) - Len(Replace(Trim(rCell), " ", "")) + 1
Next rCell

CountWords = lCount
End Function</pre>
To use this UDF push Alt+F11 and go Insert>Module and paste in the code. Push Alt+Q and save. The Function will appear under "User Defined" in the Paste Function dialog box (Shift+F3). Use the Function in any cell as shown below.

=CountWords(A1) OR =CountWords(A1:A10).







i did the same how it was appeared in the website.., but when i press shift+F3 .. i am unable to see " User defined " option in function box.
i am unable to find out the solution .. please help


thanking you .

 

[Naresh Goud]

Dear VoG,

thanks a lot .. thank you very much .. thanks thanks thanks,.


Now .. i hope my dream comes true to write my own vba code and user defined functions..


thank you

Regards
Naresh

 

[chiji]

Thanks for this Naresh. Helped me

 

[Goobersdad]

I have a similar problem - I've copied the following code into a module but the function just isn't appearing:

Function getformula(r As Range) As String
Application.Volatile
If r.HasArray Then
getformula = "<-- " & "{" & r.FormulaArray & "}"

Else

getformula = "<-- " & " " & r.FormulaArray

End If


End Function

Any ideas what I've done wrong please?

 

[RoryA]

Which module did you put it in?

 

[rlv01]

It works for me when paste your code into a general code module. Are you sure you sure it's a code module as opposed to code for a form or sheet?

btw,I recommend you use code tags for clarity when posting code

 

[Goobersdad]

Thank you for taking the time to reply - sorry about the CODE thing, I'll bear it in mind in any future posts.

Sorry if this is a numb question, but how do I know what kind of module it is?

I just did the 'normal' "Insert" > "Module" and put the code in there

 

[Goobersdad]

I'd add a screenshot, but I'm not permitted to add attachments

 

[rlv01]

Yes, "Insert Module" will insert a general code module, usually named "Module1" if it is the first one you've added.

 

[RoryA]

Which version of Excel do you have?