User Defined Function Not working

Naresh Goud

New Member
Joined
Jul 23, 2011
Messages
17
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 .
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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.
 
Upvote 0
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

moz-screenshot.png
moz-screenshot-1.png
moz-screenshot-2.png

moz-screenshot-3.png
 
Upvote 0
No add-ins should be necessary.

It works for me and also shows up when pressing Shift + F3.
 
Upvote 0
Which module did you put it in? It has to be in a normal module, not in ThisWorkbook or a worksheet code module.
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited by a moderator:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,592
Messages
6,179,774
Members
452,942
Latest member
VijayNewtoExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top