LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Using a custom function in a macro


I inserted this code as a module, and then I tried to use it in a Macro,
but it did not work. Can anyone help me to check this?
----------------------------------------------------------
Option Compare Text
Function Get_Word(text_string As String, nth_word) As String
Dim lWordCount As Long

With Application.WorksheetFunction
lWordCount = Len(text_string) - Len(.Substitute(text_string, "
", "")) + 1

If IsNumeric(nth_word) Then
nth_word = nth_word - 1
Get_Word = Mid(Mid(Mid(.Substitute(text_string, " ", "^",
nth_word), 1, 256), _
Find("^", .Substitute(text_string, " ", "^",
nth_word)), 256), 2, _
Find(" ", Mid(Mid(.Substitute(text_string, " ", "^",
nth_word), 1, 256), _
Find("^", .Substitute(text_string, " ", "^",
nth_word)), 256)) - 2)
ElseIf nth_word = "First" Then
Get_Word = Left(text_string, .Find(" ", text_string) - 1)
ElseIf nth_word = "Last" Then
Get_Word = Mid(.Substitute(text_string, " ", "^",
Len(text_string) - _
Len(.Substitute(text_string, " ", ""))), .Find("^",
Substitute(text_string, " ", "^", _
Len(text_string) - Len(.Substitute(text_string, " ", ""))))
+ 1, 256)
End If
End With

End Function
----------------------------------------------------------






sub macro ()

dim r as string
For i = 1 to 10
r = get_word("A" & i, 6)
Next
...

Is there anything wrong with the way I use this funtion?


--
betty77
------------------------------------------------------------------------
betty77's Profile: http://www.excelforum.com/member.php...o&userid=37092
View this thread: http://www.excelforum.com/showthread...hreadid=569338

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Custom macro and custom button VLExcel New Users to Excel 1 April 10th 10 12:16 AM
2003 - 2007 custom macro and custom button restore. Scott Sornberger Excel Discussion (Misc queries) 11 May 23rd 08 02:41 PM
Emulate Index/Match combo function w/ VBA custom function Spencer Hutton Excel Worksheet Functions 2 May 2nd 05 05:26 PM
Adding a custom function to the default excel function list DonutDel Excel Programming 3 November 21st 03 03:41 PM
Custom button and macro function Andy Excel Programming 4 October 7th 03 07:25 PM


All times are GMT +1. The time now is 06:15 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"