Using a custom function in a macro

[betty77]

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?

 

[Dave Peterson]

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

 

[skatonni via OfficeKB.com]

Seems to work fine. I had to put the periods before three Finds and one
Substitute.

 

[betty77]

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