VBA user defined function needs lower case string

[Ricardo Caicedo]

Hello again </SPAN>


I am having problems trying to convert a string variable inside a user defined function to lower case. The function pass two string variables (word & Name) and I assign them to a temp variable. The StrComp() function is not compiling, it give me a error …Any Help….</SPAN>




Function WordMatch(Word, Name)</SPAN>
Dim i As Integer</SPAN>
Dim NumWords As Integer</SPAN>
Dim StrTrim As String, StrAux As String, StrWLow As String, StrNLow As String</SPAN>

'Count the words inside the cell</SPAN>

StrTrim = WorksheetFunction.Trim(Word) 'Trim spaces from Word</SPAN>
StrAux = WorksheetFunction.Substitute(StrTrim, " ", "") 'Substitute spaces for nothing</SPAN>
NumWords = Len(StrTrim) - Len(StrAux) + 1 'Number spaces plus one is the number of words</SPAN>

StrWLow = StrConv(Word, VbStrConv.LowerCase)</SPAN></SPAN>
StrNLow = StrConv(Name, VbStrConv.LowerCase)</SPAN></SPAN>


If NumWords = 1 Then</SPAN>
If StrComp(StrWLow, StrNLow, vbTextCompare) Then</SPAN>
WordMatch(Word, Name) = "True"</SPAN>
End If</SPAN>
End If</SPAN>


End Function</SPAN>

 

[AlphaFrog]

Try this...

StrWLow = StrConv(Word, [COLOR=#ff0000]vbLowerCase[/COLOR])
StrNLow = StrConv(Name, [COLOR=#ff0000]vbLowerCase[/COLOR])

 

[Rick Rothstein]

Try this...

StrWLow = StrConv(Word, [COLOR=#ff0000]vbLowerCase[/COLOR])
StrNLow = StrConv(Name, [COLOR=#ff0000]vbLowerCase[/COLOR])

Or even this...

StrWLow = LCase(Word)
StrNLow = LCase(Name)

 

[Rick Rothstein]

I am having problems trying to convert a string variable inside a user defined function to lower case. The function pass two string variables (word & Name) and I assign them to a temp variable. The StrComp() function is not compiling, it give me a error …Any Help….

Function WordMatch(Word, Name)
Dim i As Integer
Dim NumWords As Integer
Dim StrTrim As String, StrAux As String, StrWLow As String, StrNLow As String

'Count the words inside the cell

StrTrim = WorksheetFunction.Trim(Word) 'Trim spaces from Word
StrAux = WorksheetFunction.Substitute(StrTrim, " ", "") 'Substitute spaces for nothing
NumWords = Len(StrTrim) - Len(StrAux) + 1 'Number spaces plus one is the number of words

StrWLow = StrConv(Word, VbStrConv.LowerCase)
StrNLow = StrConv(Name, VbStrConv.LowerCase)

If NumWords = 1 Then
If StrComp(StrWLow, StrNLow, vbTextCompare) Then
WordMatch(Word, Name) = "True"
End If
End If

End Function

Is the purpose of all that code to see if the two arguments are identical ignoring letter casing? If so, I think this simpler function will work for you...

Function WordMatch(Word As String, Name As String) As Boolean
  WordMatch = LCase(Word) = LCase(Name)
End Function