Thread: Returning value from a function to a sub procedure - Help Needed

Guys,

I have a problem, Chk out the code below. I have called function rowPosition from a sub procedure. I need to return the value of search text. But the value of searchtext in the sub procedure does not change. Can some one help me out

Sub search()

Dim searchtext As String
searchtext = "Display diagonal"
rowPosition searchtext
MsgBox (searchtext)
End Sub

Function rowPosition(ByVal searchtext As Variant, _
            Optional ByVal stLookAt As XlLookAt = xlWhole) As Long
    
            Dim rPos As Long, rS As Range
    
            rPos = 0
    
            Set rS = Cells.Find(searchtext, LookIn:=xlValues, LookAt:=stLookAt)
    
            If Not rS Is Nothing Then rPos = rS.Row
    
            rowPosition = rPos
            MsgBox (rS.Row & "-" & rS.Column)
            MsgBox (Worksheets("sheet1").Cells(rS.Row, rS.Column + 1).Value)
            searchtext = Worksheets("sheet1").Cells(rS.Row, rS.Column + 1).Value
            MsgBox ("inside routine - " & searchtext)
            
            
End Function

Cheers,
Ragav

A function should pass back a result and you should avoid using the same variable name internally for the function.

So you should, in the Sub, declare a variable (let's say x, for the sake of argument) as Long and call your function thus:

x=rowPosition(searchtext)

It is not clear on what you are trying to do. Generally functions do not change global variables. The searchstring in the function is only local to the function. Changing it there will not change searchstring in the sub.

You function returns the row in which the first occurance of "Display diagonal" was found. Your message box in function is displaying the cell on the same row next to the cell where Display diagonal was found????? ---- Not clear on what you are trying to do.

I am attaching a spreadsheet that fixes (my guess) your orignal code and I have add to other subs/function guessing what you were after... It does have some comments and may be useful in formulating what you were after. Be sure to enable macros and then press the buttons to activate the various subs.....

Hope this helps,

Ted

Originally Posted by tkowal

It is not clear on what you are trying to do. Generally functions do not change global variables. The searchstring in the function is only local to the function. Changing it there will not change searchstring in the sub.

Unless you change:

Function rowPosition(ByVal searchtext As Variant, _
            Optional ByVal stLookAt As XlLookAt = xlWhole) As Long

To:

Function rowPosition(ByRef searchtext As Variant, _
            Optional ByVal stLookAt As XlLookAt = xlWhole) As Long

Then changes to searchtext within the function will modify the value of the variable in the sub, but this isn't really how functions should be written.

thanks a lot guys... It helped me out...