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

Question

My goal is to implement some of functions where I give them parameters of power, frequency and speed of an electric motor, and look in another workbook (in which I have motor data) and return the size, shaft diameter and other motor details.

As I have not mastered much VBA I tried to implement a function that simply goes to a cell in another workbook and returns the value:

Function Test() As String
Dim name As String 

  With Workbooks.Open("D:\ExcelTest\WbSource.xlsm").Sheets("Sheet1")  
    name = .Cells(2, 3) 
  End With

  Test= name

  ActiveWorkbook.Save
  ActiveWorkbook.Close

End Function

The problem is that it gives me a #VALUE! error, but each variable used is defined as a string and the cells has general format (if I change cells format to text it gives me the same message).

Based on what you've written, the first thing that stands out is that you're assigning a range type (`.Cells(2, 3)`) to a string variable. Try changing this to `.Cells(2, 3).Value`. Can you also tell us which line throws the error? — 314UnreadEmails, May 11 '15 at 16:48
Functions called from worksheet cells are limited in what they can do, other than return a value back to the calling cell. https://support.microsoft.com/en-us/kb/170787 Opening anoyher workbook from a UDF isn't listed, but I wouldn't be surprised if it was bloacked. — Tim Williams, May 11 '15 at 16:52
Why don't you want to access the values using conventional links in worksheet formula, like `='D:\ExcelTest\[WbSource.xlsm]Sheet1'!R2C3`? You can place those formulas on the auxiliary worksheet for futher computations. — omegastripes, May 11 '15 at 23:56

score 1 · Answer 1 · answered May 11 '15 at 18:21

Try as I might, I could not get workbooks.open to work in a function, even if the function calls a sub. You could open the catalogue file in the workbook open event, and close it again in the before close event.

In the VProject Explorer, right click on "ThisWorkBook," and "View code".
In the pick list at the top, select Workbook, and the sub Workbook_open() procedure should be created. If not, select "Open" in the right pick list. Put in the following:

Application.Workbooks.Open ("D:\ExcelTest\WbSource.xlsm")
ThisWorkbook.Activate 'restores the "focus" to your worksheet

Then click the right pick list and select "beforeClose" and put in

On Error Resume Next 'this keeps it from crashing if the catalogue is closed first
Workbooks("WbSource.xlsm").Close

As long as the worksheet opens the wbsource file first, the function will work.

score 1 · Answer 2 · edited May 23 '17 at 12:32

Here is an approach with scheduling UDF execution in queue, and processing outside UDF that allows to get rid of UDF limitations. So the value from the closed workbook got via ExecuteExcel4Macro() by a link.

Put the following code into one of the VBAProject Modules:

Public Queue, QueueingAllowed, UDFRetValue

Function UDF(ParamArray Args())
    If IsEmpty(Queue) Then
        Set Queue = CreateObject("Scripting.Dictionary")
        UDFRetValue = ""
        QueueingAllowed = True
    End If
    If QueueingAllowed Then Queue.Add Application.Caller, (Args)
    UDF = UDFRetValue
End Function

Function Process(Args)
    If UBound(Args) <> 4 Then
        Process = "Wrong args number"
    Else
        ' Args(0) - path to the workbook
        ' Args(1) - filename
        ' Args(2) - sheetname
        ' Args(3) - row
        ' Args(4) - column
        On Error Resume Next
        Process = ExecuteExcel4Macro("'" & Args(0) & "[" & Args(1) & "]" & Args(2) & "'!R" & Args(3) & "C" & Args(4))
    End If
End Function

Put the following code into ThisWorkbook section of VBAProject Excel Objects:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Dim Item, TempFormula
    If Not IsEmpty(Queue) Then
        Application.EnableEvents = False
        QueueingAllowed = False
        For Each Item In Queue
            TempFormula = Item.FormulaR1C1
            UDFRetValue = Process(Queue(Item))
            Item.FormulaR1C1 = TempFormula
            Queue.Remove Item
        Next
        Application.EnableEvents = True
        UDFRetValue = ""
        QueueingAllowed = True
    End If
End Sub

After that you can get the values from closed workbook via worksheet formula using UDF:

=UDF("D:\ExcelTest\";"WbSource.xlsm";"Sheet1";2;3)

Anyway you can add Workbooks.Open() or any other stuff into Function Process(Args) to make it to work the way you want. The code above is just an example. I've answered the similar questions here and here, so that descriptions might be helpful.

score 0 · Answer 3 · answered May 11 '15 at 17:04

I suggest:

open WbSource.xlsm either manually or via VBA outside the UDF.
pass the parameters to the UDF
have the UDF search down the columns of the newly opened workbook to find the correct record
have the UDF pass the row number back to the worksheet
in the worksheet, use Match()/Index() formulas to retrieve other data.

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

3 Answers3

Linked