Public function vba

Question

I am trying to set a public function that searches for an identifier in some worksheet column A and returns its row number so i put this function like this:

Public Function Row_Index_CellID(ws As Worksheet, CellID As String) As Long
    Dim FindRowNumber As Long
    Dim FindRow As Range
    ws.Activate
    With ws
            Set FindRow = ws.Range("A1").Find(CellID, LookIn:=xlValues, LookAt:=xlWhole)
    FindRowNumber = ws.Cells.Find(What:=CellID, After:=Cells(1, 1), LookIn:=xlFormulas, _
                            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                            MatchCase:=False, SearchFormat:=False).Row - 2
    End With
    Row_Index_CellID(ws, CellID) = FindRowNumber
End Function

But I had the error "Function Call on left hand side of assignment must return a variant or object" at the line:

Row_Index_CellID(ws, CellID) = FindRowNumber

so when i declared the function as variant:

Public Function Row_Index_CellID(ByRef ws As Worksheet, ByRef CellID As String) As Variant

I had the error "Out of stack space" at the same line as before! can you help me please?

Answer 1

To set the value returned by the function use the function name only:

Row_Index_CellID = FindRowNumber

In the first case it's the syntax error, in the second case you call the function recurrently until the stack exhausts. The second case is syntaxial correct because the returning Varian can be a reference to the object (e. g. Range) which can accept a value.

UPD

Row_Index_CellID returns 0 if ws.Cells.Find(...).Row returns 2.

Optimized code:

Public Function Row_Index_CellID(ws As Worksheet, CellID As String) As Variant
  Dim FindRow As Range
  Set FindRow = ws.UsedRange.Find(What:=CellID, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
  Row_Index_CellID = IIf(FindRow Is Nothing, CVErr(xlErrNA), FindRow.Row)
End Function

If the search area is the column "A" only:

Public Function Row_Index_CellID(ws As Worksheet, CellID As String) As Variant
  Dim FindRow As Range
  With ws.Columns(1)
    Set FindRow = .Find(What:=CellID, After:=.Cells(1), LookIn:=xlFormulas, _
      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False)
  End With
  Row_Index_CellID = IIf(FindRow Is Nothing, CVErr(xlErrNA), FindRow.Row - 2)
End Function