VBA and Excel - Workbook opening problem

[Peter]

If I have workbook "one.xls" open and try to open a second workbook from
within a subroutine using

Workbooks.Open "E:\Data\Excel\Two.xls"

"Two.xls" opens as expected.

but if I place the same line of code in a Function rather than a subroutine
"Two.xls" does not open!

Any ideas? All I'm trying to do is to extract some data from one sheet &
copy it to another.

--
Peter
To err is human but it takes a computer to really mess things up!

[Peter]

and heres another funny
If I use
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
it works fine in the subroutine but returns 1 from the Function even
when my target workbook (Two.xls) is open

AHHHHHHHHHH

--
Peter
To err is human but it takes a computer to really mess things up!

"Peter" <gam...@SPAMblueyonder.co.uk> wrote in message
news:chy1e.14216$XH5....@fe1.news.blueyonder.co.uk...

[Elef]

Are you aware of the following constraint when building functions to be
called from a worksheet formula (User Defined Functions) ?:

A function used in a formula can return a value. It cannot make any changes
to the workbook.

Hope it helps.
Bye
Elef

"Peter" <gam...@SPAMblueyonder.co.uk> ha scritto nel messaggio
news:chy1e.14216$XH5....@fe1.news.blueyonder.co.uk...

[Peter]

Thanks. I was not aware of this restriction.
Presumably using Workbooks.Open counts as a change!
I did wonder if it was a quirk of using a Function and tried calling a
Subroutine from the Function with the Workbooks.Open command in the
subroutine but that fails too.

Peter

"Elef" <m.p.b@*toglimi*rocketmail.com> wrote in message
news:E8E1e.23974$kC3....@tornado.fastwebnet.it...

[Elef]

The restriction applies to Subs and functions called by the function also.
The idea behind this restriction is that a worksheet function should never
have side effects.
Bye
Elef

"Peter" <gam...@SPAMblueyonder.co.uk> ha scritto nel messaggio

news:j7P1e.15203$XH5....@fe1.news.blueyonder.co.uk...