Éxcel-VBA Open Workbook in macro that have been called in a cell

Question

I have some trouble with a function that I call from a cell in Excel. The macro shall open a workbook get some data and then return a mathematical results to the cell.

But when I use the the following code it does not open the wordbook, just return #VALUE! to the cell. It break out of the code right after I tried to open the workbook.

' This Interpolation function is used to get data from other Excel sheets
Public Function DatasheetLookup(ExcelFile As String, ExcelSheet As String, xVal As Double, Optional isSorted As Boolean = True) As Variant
    ' abosolute or relative path?
    If Not (Left(ExcelFile, 3) Like "[A-Z]:\") Or (Left(ExcelFile, 2) = "\\") Then
        ExcelFile = ThisWorkbook.path & "\" & ExcelFile
    End If

    ' does file exits?
    If Dir(ExcelFile, vbDirectory) = vbNullString Then
        DatasheetLookup = "No such file!"
        Exit Function
    End If

    ' open the source workbook, read only
    Dim Wbk As Workbook
    Dim WS As Worksheet
'    Application.ScreenUpdating = False ' turn off the screen updating
    Set Wbk = Workbooks.Open(ExcelFile)
       ' Run through all sheets in the source workBook to find "the one"
        For Each WS In Wbk.Worksheets     ' <-- Here it exit the code and return #VALUE!
            If WS.Name <> ExcelSheet Then
                DatasheetLookup = "Sheet not found!"
            Else
                Dim xRange As Range
                Dim yRange As Range
                xRange = WS.Range("A1", "A" & WS.UsedRange.Rows.Count)
                yRange = WS.Range("B1", "B" & WS.UsedRange.Rows.Count)



                Dim yVal As Double
                Dim xBelow As Double, xAbove As Double
                Dim yBelow As Double, yAbove As Double
                Dim testVal As Double
                Dim High As Long, Med As Long, Low As Long

                Low = 1
                High = WS.UsedRange.Rows.Count

                If isSorted Then
                    ' binary search sorted range
                    Do
                        Med = Int((Low + High) \ 2)
                        If (xRange.Cells(Med).Value) < (xVal) Then
                        Low = Med
                        Else
                        High = Med
                        End If
                    Loop Until Abs(High - Low) <= 1
                Else
                    ' search every entry
                    xBelow = -1E+205
                    xAbove = 1E+205

                    For Med = 1 To xRange.Cells.Count
                        testVal = xRange.Cells(Med)
                        If testVal < xVal Then
                            If Abs(xVal - testVal) < Abs(xVal - xBelow) Then
                                Low = Med
                                xBelow = testVal
                            End If
                        Else
                            If Abs(xVal - testVal) < Abs(xVal - xAbove) Then
                                High = Med
                                xAbove = testVal
                            End If
                        End If
                    Next Med
                End If

                xBelow = xRange.Cells(Low): xAbove = xRange.Cells(High)
                yBelow = yRange.Cells(Low): yAbove = yRange.Cells(High)
                DatasheetLookup = yBelow + (xVal - xBelow) * (yAbove - yBelow) / (xAbove - xBelow)
                Exit For
            End If

        Next WS
    Wbk.Close Savechanges:=False
    Set Wbk = Nothing
    Application.ScreenUpdating = True
End Function

Have you tried to debug this using the VBA debugger? My guess is that you are performing a mathematics operation on a string value. I think it will be easy to find with debugging. — laylarenee, Jan 07 '15 at 21:35
Yes VBA debugger have been used! - Function Exit right after `For Each WS In Wbk.Worksheets` and returns #VALUE! - so it does never get to that part. — Søren Juul, Jan 07 '15 at 22:17
What do you mean it exits the code? It does not see any sheets in the Excel file? Have you looked at the locals window to see if this workbook is created correctly? — cronos2546, Jan 07 '15 at 22:29
Tip 1: add a watcher to the variable `Wbk` just before executing the line where the function escapes, then tell us what it's in there. Tip 2: make sure the variable `ExcelFile` it's of kind `name+extension` and not only `name`. To be clearer, make sure you pass `MyWorkbook.xlsx` and not only `MyWorkbook`. — Matteo NNZ, Jan 07 '15 at 22:37
Tip1: Wbk = Nothing. Tip2: it is the correct path ExcelFile = "C:\test\test.xlsx" — Søren Juul, Jan 08 '15 at 00:13
@cronos2546 I mean escapes - I think when i use the `Workbooks.Open` it gennerate an error, but because the function have been called from a cell, I do not get any error messages. But how to fix it ? :-) — Søren Juul, Jan 08 '15 at 00:21

score 0 · Accepted Answer · edited May 23 '17 at 11:50

0

I am not sure the reason for this specifically, but you cannot open a file in a user defined function. There are many additional actions that cannot be performed in a Function as well. This is also discussed in this Stack Overflow answer here.

However, in your case, you can easily cheat this limitation by opening the file you want to read before you call the function. I prepared a very basic demonstration of this, you will need to modify the code as needed to fit your particular example:

Code in "ThisWorkbook":

' when the workbook opens, also open the companion spreadsheet so it is available to use
Private Sub Workbook_Open()
    Set Wbk = Workbooks.Open("C:\Users\lrr\Desktop\Myworkbook.xlsx")
End Sub

Code in "Module1":

Global Wbk As Workbook

Public Function testFunc()
    ' the workbook is already opened, so you may perform this iteration operation w/o any problems.
    For Each WS In Wbk.Worksheets
        testFunc = 1
        Exit Function
    Next WS
End Function

Code in Cell A1:

=testFunc()

edited May 23 '17 at 11:50

Community

1
1

answered Jan 08 '15 at 16:12

laylarenee

3,276
7
32
40

You nailed it :-) - that was the problem... `Workbooks.Open` does not work when called from a cell in excel. Your workaround works, but my problem with this method, is that I do not know the path/filename of the workbooks at that point. I guess I need to make a macro that check every cell that call my `DatasheetLookup` and the open the relevant workbooks (hidden), but if you introduced a new workbook it will only work if you close and open the "master" workbook - or do you have better suggestion? – Søren Juul Jan 08 '15 at 19:54
I'm not certain of the tasks you are doing so I don't think I can be of much more help. I think that a macro-based solution might be a good way to handle this. Perhaps a "RECALCULATE" button that runs a macro to refresh all the data on the page? – laylarenee Jan 08 '15 at 20:20
This article can explain how to get data from a closed file, but the method has some stipulations on how its used: http://spreadsheetpage.com/index.php/site/tip/a_vba_function_to_get_a_value_from_a_closed_file/ – laylarenee Jan 08 '15 at 20:21

Éxcel-VBA Open Workbook in macro that have been called in a cell

1 Answers1

Linked