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 .
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 .