Problem calling function on worksheet

[pong]

I have created this function to open csv file and look for a data in the file by the criteria of Date.
I created the ato test function and everything was good. The right answer came out

In the test ,call GetPrice(1, "2011-05-30"), one is the file name and the date is the line of the data i wanna get

But I tried using both =GetPrice(1, "2011-05-30") and =GetPrice(1, 2011-05-30) on worksheet, none of them could work

Function GetPrice(ByVal Stock_code As String, ByVal Stockdate As String)

Dim Fsys As New FileSystemObject
Dim FileStream As TextStream
Dim myws As Worksheet
Dim Row_ As Integer
Dim TempStr As String
Dim TempArray() As String
Dim myStockCode As String
Dim i As Integer

'Filelocation
myStockCode = ToRic(Stock_code)
Set FileStream = Fsys.OpenTextFile("C:\Users\Desktop\VBA\VBA self learning\Data\" & myStockCode & ".csv")
'OpenTextwhile: Make sure you know where the path is

Set myws = ThisWorkbook.Sheets("sheet1")
Row_ = 1

While FileStream.AtEndOfStream = False
TempStr = FileStream.ReadLine
TempArray = Split(TempStr, ",")

'lookitem

If InStr(TempStr, Format(Stockdate, "yyyy/mm/dd")) > 0 Then
For i = 0 To UBound(TempArray)
Debug.Print TempArray(UBound(TempArray))


Exit Function

Next
End If
Row_ = Row_ + 1

Wend
FileStream.Close

End Function

I have created this sub to call problem and find the price of the stock I wanna get

Sub test()
Call GetPrice(1, "2011-05-30")
End Sub

 

[pong]

does anybody have any suggestions for solving the problem?

 

[GlennUK]

Well, a little impatient aren't we? You waited all of 2 minutes before reposting to your thread. I've barely had time to read through it.

 

[Jonmo1]

Sorry pong,

A function called from a cell formula cannot open another file.
It can only return a value to the cell containing the function.
It can't even modify other cells, like color another cell red, or put a value in another cell.

 

[pong]

Thank you guys

To GlennUK
Haha, I just read through my post again and realized i didnt ask the question

To Jonmo1
Thanks a lot.May be I will keep all the procedure in a sub
I really learnt something from your reply

 

[Jonmo1]

Glad to help, thanks for the feedback...

If you really want to keep it as a Cell UDF, the question you need to ask is..
"How can I extract and use data from a CSV file without opening it?

I'm not 100% sure it can be done, but it sounds doable..

Thank you guys

To GlennUK
Haha, I just read through my post again and realized i didnt ask the question

To Jonmo1
Thanks a lot.May be I will keep all the procedure in a sub
I really learnt something from your reply