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 .

 

[VoG]

Press Shift + F3, in the Select a Category dropdown select User Defined.

You don't need to do that in order to use the UDF.

 

[Naresh Goud]

Dear VoG,
thanks for giving me the reply , i knew to select down the user defined function and then proceed with my function ...


i have simply taken the code from google .. copied into the VBA module .. then pressed Alt + Q the when i tried to check my countfunction which we defined as user defined one .. its not working . .. either its not displaying also.. then i pressed Shift+ F3 and thought of checking the function is listed in user defined option .. its not displaying any user defined function in the list ..

is there any Excel Addins need to be installed to show user defined function in the formula list

 

[VoG]

No add-ins should be necessary.

It works for me and also shows up when pressing Shift + F3.

 

[RoryA]

Which module did you put it in? It has to be in a normal module, not in ThisWorkbook or a worksheet code module.

 

[Naresh Goud]

Dear voG,
i had tried some exercises with excel .. why it shows you and why not me in the function box.. .. i am using excel 2007.. Pressed ALT + FI
went into the excel options .clicked on Add-ins.... and in manage i selected Excel Add-ins then i pressed go...

it displayed me one box.. of analysis tool park.. and few options.. i selected all and clicked ok ..

it took some time .. installed some stuff.. ..then later when i checked it ..

it now displaying ... ..now i am able to see user defined option in function box..

Thanks any way for your reply , ... Only sharing can increase knowledge .

thanks again.

 

[Naresh Goud]

Can u teach me how to write user defined function

Dear VoG,
i am happy to receive responses from one of the excel MVP.
i dream of becoming like you guys one day , due to the financial problems cannot able to effort quality of education .. and training outside.. .. what ever i have learned i had learned from internet ..

i should say thanks to Mike Grivin .Professor of Highline community college.. whose videos have thought me advanced excel .. i have downloaded his videos and practise it at home ...Now writing functions had been easy with the help of Mike .

But i would like to write VBA and create my own user defined functions.. .. again i pasted the code.. pressed shift + f3 .. selected user defined function but under it my created function doest appear.. and i am not able to select it ..or else write the function directly .. ..

i will be very thankful for you .. if you can help me to write down my first user defined function and some what in VBA..

Please send a sample of user defined function to my email id .. please..

thank you.

 

[RoryA]

Re: Can u teach me how to write user defined function

Please note that I have removed your email address from your post, since posting an email address in an open forum like this is liable to get you flooded with spam.

 

[VoG]

This should help you http://www.fontstuff.com/vba/vbatut01.htm

Also, take a look at Hiker's List http://www.mrexcel.com/forum/showpost.php?p=2676997&postcount=5

 

[Naresh Goud]

Hi Rory,
i am sorry .. and thanks .. then .. what would be the solution.. where i can learn the vba from basics to writing of user defined functions.

can u tell me few websites .. where i can learn better.

anyway .. thank you very much.