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)...

 

[Rick Rothstein]

Will that be responsive to a change in col A?

No, because I screwed up the change (and made the mistake of not testing it before posting). I think this version works correctly...
[table="width: 500"]
[tr]
[td]

Public Function Find_First(FindString As String) As String
  Dim ColA As String, TextUptoFindString As String, RowNum As Long, WS As Worksheet
  Set WS = Application.Caller.Parent
  ColA = Join(Application.Transpose(WS.Range("A1", Cells(Rows.Count, "A").End(xlUp))), Chr(1))
  RowNum = UBound(Split(Left(ColA, InStr(1, ColA, FindString, vbTextCompare)), Chr(1))) + 1
  If RowNum Then
    Find_First = WS.Cells(RowNum, "A").Value
    MsgBox "Found at: A" & RowNum
  Else
    Find_First = "Not found"
    MsgBox ("Not found")
  End If
End Function

[/td]
[/tr]
[/table]

 

[shg]

How does it know to recalculate when you make a change in col A?

 

[Rick Rothstein]

How does it know to recalculate when you make a change in col A?

I forgot the Application.Volatile, didn't I?
[table="width: 500"]
[tr]
[td]

Public Function Find_First(FindString As String) As String
  Dim ColA As String, TextUptoFindString As String, RowNum As Long, WS As Worksheet
  Application.Volatile
  Set WS = Application.Caller.Parent
  ColA = Join(Application.Transpose(WS.Range("A1", Cells(Rows.Count, "A").End(xlUp))), Chr(1))
  RowNum = UBound(Split(Left(ColA, InStr(1, ColA, FindString, vbTextCompare)), Chr(1))) + 1
  If RowNum Then
    Find_First = WS.Cells(RowNum, "A").Value
    MsgBox "Found at: A" & RowNum
  Else
    Find_First = "Not found"
    MsgBox ("Not found")
  End If
End Function

[/td]
[/tr]
[/table]

 

[shg]

I've had many days like that ...

 

[Rick Rothstein]

I've had many days like that ...

And they are no fun, are they? Thanks for herding me along to a workable solution (well, at least I hope it works on the OP's Mac the same way it works on my PC).

 

[shg]

I some corner of my brain is a recollection that InStrRev doesn't (or didn't) work on Mac.

 

[shg]

In ...

 

[Rick Rothstein]

I some corner of my brain is a recollection that InStrRev doesn't (or didn't) work on Mac.

First off, I didn't use InStrRev in my code. However, I'd be surprised if InStrRev did not work... I can understand Find as that is a method that belongs to Excel itself and I could believe the Excel object model (which is an "add on" to the core VB language) could differ for different operating systems, but InStrRev is a built-in VB core function that was added to the VB/VBA languages in Version 6... it would be hard to believe they added a core function to one operating system's version of VB and not the other. I mean, you could be right, but it would surprise me greatly if you are.

 

[shg]

First off, I didn't use InStrRev in my code.

Umm -- yes, I know. I thought we'd beaten that topic to death.

... it would be hard to believe they added a core function to one operating system's version of VB and not the other.

From http://dmcritchie.mvps.org/excel/strings.htm:

Material introduced in VB6 with Excel 2000 not available on Macs (#vb6)

The following string functions are not available in VB5 use before Excel 2000 and are not available on MACS.

Function

Description

Join Used to join arrays elements.
Split Split a string into a variant array.
InStrRev Similar to InStr but searches from end of string.
Replace To find a particular string and replace it.
Reverse To reverse a string.

MSKB 188007 -- How To Simulate Visual Basic 6.0 String Functions in VB5

Don't have (and never used) a Mac, so I have no way to check.

 

[Rick Rothstein]

First off, I didn't use InStrRev in my code.

Umm -- yes, I know. I thought we'd beaten that topic to death.

We discussed this before?

From http://dmcritchie.mvps.org/excel/strings.htm:

The link appears to be dead.