Hello guys,

This is my first post here and i really need help.

I'm writing a VBA function for excel that should parse its arguments
from one sheet based on tables on an entirly different Workbook file.

So, I wrote:
---------------------------
Public Function foo(...) as String
...
Workbooks.open Filename:= "path\filename.xls"
...
foo = result
End Function
---------------------------

But the thing isn't working, it doesn't open the file, though it
doesn't give any error messages. It just continue until it reach the
stage where it tries to read from the file and terminate.
This happens even when I manually open the file before hand!

The really ODD thing is that once I replace "Public Function/End
Function" with "Sub/End Sub" the file magically open!!!
So, what makes the difference?

PS: I really need it has a function.

PPS: I use Office 2000 on Win2000

regards,
Ali


---
Message posted from http://www.ExcelForum.com/

Hi Ali

This depends on where you call the function from. A function acting as a cell formula has
very limited power, it can't change its own environment. Called from VBA code, it's like a
sub and can do just everything.

--
HTH. Best wishes Harald
Followup to newsgroup only please.

"Ali Ahmed " wrote in message
...
Hello guys,

This is my first post here and i really need help.

I'm writing a VBA function for excel that should parse its arguments
from one sheet based on tables on an entirly different Workbook file.

So, I wrote:
---------------------------
Public Function foo(...) as String
..
Workbooks.open Filename:= "path\filename.xls"
..
foo = result
End Function
---------------------------

But the thing isn't working, it doesn't open the file, though it
doesn't give any error messages. It just continue until it reach the
stage where it tries to read from the file and terminate.
This happens even when I manually open the file before hand!

The really ODD thing is that once I replace "Public Function/End
Function" with "Sub/End Sub" the file magically open!!!
So, what makes the difference?

PS: I really need it has a function.

PPS: I use Office 2000 on Win2000

regards,
Ali


---
Message posted from http://www.ExcelForum.com/