opening file with worksheetfunction
 

Hello everybody.
I'm trying to create a worksheetfunction in Excel 97 that should ope
another excel-file.

This is how it looks now (in a module):

Public Function AZ (iUserInput as Integer) as Integer
workbooks.open "c:\etc\etc.xls"
AZ = iUserInput * 2
End Function

Now when I type in a worksheet in the same workbook
=AZ(3)
I get #VALUE!

The code simply doesn't open the file. On the other hand it does wor
when I start the function from the module. And I tested it as a sub: i
worked. I have put the code in a worksheet event: it worked again.

Why is it not possible to use a self-defined worksheetfunction to ope
a file

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

opening file with worksheetfunction
 

Hi
a worksheet formula is not able to change the Excel
environment (e.g. changing formats or opening other
files). So this is not possible.

You may consider using the HYPERLINK formula. e.g.
=HYPERLINK("c:\etc\etc.xls")

or an alternative would be to use the worksheet_change
event to open another file

-----Original Message-----
Hello everybody.
I'm trying to create a worksheetfunction in Excel 97 that
should open
another excel-file.

This is how it looks now (in a module):

Public Function AZ (iUserInput as Integer) as Integer
workbooks.open "c:\etc\etc.xls"
AZ = iUserInput * 2
End Function

Now when I type in a worksheet in the same workbook
=AZ(3)
I get #VALUE!

The code simply doesn't open the file. On the other hand
it does work
when I start the function from the module. And I tested
it as a sub: it
worked. I have put the code in a worksheet event: it
worked again.

Why is it not possible to use a self-defined
worksheetfunction to open
a file?


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

.

opening file with worksheetfunction
 

simple:

a function cannot do anything but return a value to the cells(s) or
procedure is was called from.

why? because that's the function of a function.




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


modest wrote:

Why is it not possible to use a self-defined worksheetfunction to open
a file?

opening file with worksheetfunction
 

OK, that's clear. Thank you!
Yet I'm still curious why I can put this code in a worksheet event, an
after checking if target = "=AZ([integer])" performing actions that ar
not allowed in a worksheetfunction.
I mean: is there a serious reason why I shouldn't use a worksheeteven
for opening other files

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

opening file with worksheetfunction
 

Hi
simply a design decision. Functions used in cells ONLY
return values. To achive something like opening files
Microsoft provided worksheet_events

One can argue if this is a good spreadsheet dsign to open
another file depending on a cell value but that really
depends on your requirements. No technical problem with
this

-----Original Message-----
OK, that's clear. Thank you!
Yet I'm still curious why I can put this code in a
worksheet event, and
after checking if target = "=AZ([integer])" performing
actions that are
not allowed in a worksheetfunction.
I mean: is there a serious reason why I shouldn't use a
worksheetevent
for opening other files?


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

.