CODEWITHSUNDEEP
vbaexcel
ekkis
ekkis

Reputation: 10226

How to fix #VALUE! when UDF references another workbook

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?

Upvotes: 0

Views: 299

Answers (2)

ekkis
ekkis

Reputation: 10226

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

Upvotes: 0

Soulfire
Soulfire

Reputation: 4296

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.

Upvotes: 1

Related Questions