Excel VBA can't open Workbook

Question

First: I'm using Excel 2007, but the code has to work for Excel 2003 as well.

My problem is the following: I need to access cells in a different workbook, which may be closed. The following code can be found all around the web:

Function Foo()
    Dim cell As Range
    Dim wbk As Workbook
    Set wbk = Workbooks.Open("correct absolute path")
    ' wbk is Nothing here so the next statement fails.
    Set cell = wbk.Worksheets("Sheet1").Range("A1")
    Foo = cell.Value
    wbk.Close
End Function

sadly, wbk is Nothing after the open statement (I'd love to give a better error message, but no idea how I'd do that; what I'd give for a real IDE and an useful language :/). The absolute path is correct and points to a valid excel xlsx file.

Also I assume the best way to do this, is to "cache" the workbook and not open/close it every time the function is called? Any possible problems with that (apart from having to handle the situation when the workbook is already open obviously)?

Image while stepping through:

Answer 1

I can reproduce this problem. It only happens to me when I attempt to paste this code into a user-defined function.

I believe this is by design (the quote is for XL 2003, but the same thing happens to me on XL 2010)

Using VBA keywords in custom functions

The number of VBA keywords you can use in custom functions is smaller than the number you can use in macros. Custom functions are not allowed to do anything other than return a value to a formula in a worksheet or to an expression used in another VBA macro or function. For example, custom functions cannot resize windows, edit a formula in a cell, or change the font, color, or pattern options for the text in a cell. If you include "action" code of this kind in a function procedure, the function returns the #VALUE! error.

http://office.microsoft.com/en-us/excel-help/creating-custom-functions-HA001111701.aspx

The only workaround I've found is to call this kind of code via a normal macro. Something like selecting the cells to apply it to, then looping over Selection or the like.