Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a custom function in a macro
This will work if you're using xl2k or higher. It relies on VBA's Split command
that was added in xl2k. Option Explicit Function Get_Word(text_string As String, nth_word) As String Dim mySplit As Variant Dim TotalWords As Long Dim myWord As String myWord = "" 'remove any leading/trailing/multiple embedded spaces text_string = Application.Trim(text_string) If text_string = "" Then 'do nothing Else mySplit = Split(text_string, " ") TotalWords = UBound(mySplit) - LBound(mySplit) + 1 'mySplit is 0 to (words - 1) If (nth_word - 1) UBound(mySplit) Then 'do nothing Else myWord = mySplit(nth_word - 1) End If End If Get_Word = myWord End Function Sub testme() MsgBox Get_Word("this is a test", 2) End Sub betty77 wrote: 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 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a custom function in a macro
Seems to work fine. I had to put the periods before three Finds and one
Substitute. betty77 wrote: 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? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200608/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a custom function in a macro
Thank you very much for your help. I tried your new function code and it works great. I created the below code but it did not work. Can anybody help me to check what was wrong with the code? Thanks. Sub macro3() Dim a1 As String Dim a2 As String Dim i As Integer For i = 1 To 10 a1 = Get_Word("A" & i, 1) a2 = InStr(1, "B" & i, a1) If a2 0 Then Range("C" & i).Value = a1 Else End If Next End Sub -- betty77 ------------------------------------------------------------------------ betty77's Profile: http://www.excelforum.com/member.php...o&userid=37092 View this thread: http://www.excelforum.com/showthread...hreadid=569338 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom macro and custom button | New Users to Excel | |||
2003 - 2007 custom macro and custom button restore. | Excel Discussion (Misc queries) | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Adding a custom function to the default excel function list | Excel Programming | |||
Custom button and macro function | Excel Programming |