excel VBA function works in code but not as UDF in spreadsheet

[wwhoami]

I coded a function in a module that searches column A:A for a string (passed as an input) and returns the full content of the first cell in range A:A that contains that piece of string...

THe function works fine when I execute is from the sub test(), but I cannot make it work in the sheet directly... ie by writing =Find_First("lala"). It just returns "not found" although the piece of text is indeed in A:A

Here's my code.. Thanks for your help!!


<code>Public Function Find_First(FindString As String) As String

Dim Rng As Range
Dim TrimString As String

Application.Volatile True

TrimString = Trim(FindString)

If TrimString <> "" Then
With Sheets("Sheet1").Range("A:A")
Set Rng = .Find(What:=TrimString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Rng Is Nothing Then
Find_First = Rng.Value
MsgBox ("Found at: " & Rng.Address)
Else
Find_First = "not found"
MsgBox ("Not found ")
End If
End With
End If

End Function

Sub test()
MsgBox Find_First(" lala ")
End Sub</code>


Another thing to mention is that it seems to work on certain other computers... Maybe it is related to some settings?? I use Excel 14.5.4 (Office 2011 for MAC)...

 

[shg]

We discussed this before?

"that topic" referring to your code

The link appears to be dead.

The colon was't supposed to be part of the hyperlink, sorry:

Strings and Manipulations

 

[Rick Rothstein]

The colon was't supposed to be part of the hyperlink, sorry:

Strings and Manipulations

Thanks for the link. I do not have (and do not want:wink access to a Mac, so I cannot check his claims, but if true, the Mac community is really getting screwed by Microsoft as those String functions are major, major components of VB6. I do note one mistake (typo I presume) at the link you provided... the list of functions say Reverse is one of the missing functions... the correct function name is StrReverse). For those Mac people who want/need these string functions, this website has multiple coded equivalent for them (see the VB6 to VB5 column) with timed results for them under different scenarios...

VBspeed

In addition to those functions, there are several other functions that any readers here (not just those using a Mac) may find useful. The site is safe (I have used or referenced it on and off for about 15 years now... all the way back to the days when I volunteered answering newsgroup questions for the compiled version of VB).

 

[wwhoami]

Dear Rick, your code works perfectly on the Mac! Thank you so much. I guess I'll go ahead and have a close look at Excel for MAc's forbidden functions!