How to fix #VALUE! when UDF references another workbook

Question

I have defined a function in the Module1 of my workbook that looks like this:

Function Header(r As Range) As String
    For i = 1 To r.Row
        If r.Offset(-i, -1).Value = "" Then
            Header = r.Offset(-i).Value
            Exit For
        End If
    Next
End Function

and I call it with a reference to another workbook's cell like this:

=Header('[OtherWorkbook.xlsx]Sheet1'!C34)

and what I get is #VALUE!... until, I open the other workbook, at which point the value magically appears.

how can I force the spreadsheet to fetch the values from the other workbook, even though it isn't open?

I'm pretty sure that you can't (or couldn't at least) get data from a close workbook, so you'll need to test and open it before (in code) if it's necessary and then close it! — R3uK, Apr 20 '15 at 15:45
A Range object in VBA can only be created from an open workbook. — Rory, Apr 20 '15 at 15:50
[This approach](http://stackoverflow.com/a/30179303/2165759) allows to get a value from a closed workbook via UDF. — omegastripes, May 15 '15 at 11:55
Does this do the trick? http://stackoverflow.com/questions/9259862/executeexcel4macro-to-get-value-from-closed-workbook — PaulG, May 21 '15 at 20:23

score 1 · Answer 1 · answered Apr 20 '15 at 15:45

1

Specifying the full path of the closed Excel workbook should do the trick.

Instead of

=Header('[OtherWorkbook.xlsx]Sheet1'!C34)

Try

=Header('C:\Users\your_username\Desktop\[OtherWorkbook.xlsx]Sheet1'!C34)

Substituting the path of the closed workbook in for the path above.

answered Apr 20 '15 at 15:45

Soulfire

4,218
23
33

well, I've noticed that if I enter the formula without the path, Excel fills out the path for me. still, path or no path, if the workbook is closed I get no values – ekkis Apr 20 '15 at 19:40
I see that you are passing in a range object into the function. I'm not sure you can do this with a closed workbook, as Rory's comment suggests. You may have to reconsider what you are passing in to the function. One option would be to programmatically open the workbook, and you can do this without displaying it to the user if you like. – Soulfire Apr 20 '15 at 19:46

score 0 · Accepted Answer · edited May 23 '17 at 10:26

0

so the answer is: it can't be done. to get the values, opening the workbook is required.

there is an alternative, supplied by @omegastripes above, though is seems rather complicated:

How can I pick values from an Excel workbook and return them by function on active workbook

edited May 23 '17 at 10:26

Community

1
1

answered May 21 '15 at 19:38

ekkis

9,804
13
55
105

How to fix #VALUE! when UDF references another workbook

2 Answers2