1

I found this on a related subject - Excel function to make SQL-like queries on worksheet data?

But I was wondering if there is any way to use an excel workbook/file like a function in a separate workbook/file?
So I have an excel workbook that has a control page - where I can input parameters 1,2,3. And based on those parameter, the outpage page will display the correlating data. I know I can duplictae the output page to show the outputs for all three parameters and link these to my other workbook.

However, is there any way to (in the other excel file) do something like FILENAME_otherfile.function(1).range(A1) or something? to extract the cell A1 with parameter 1 as an input?
And in the same file also call FILENAME_otherfile.function(2).range(A1)?

Community
  • 1
  • 1
Chris Vee
  • 23
  • 2
  • Actually, is there any way to use an excel sheet as a function in the same way within the same workbook? – Chris Vee Jul 01 '14 at 13:50
  • 2
    I don't understand what you mean by "as a function" ... you just want to acces values? – smagnan Jul 01 '14 at 13:52
  • yes, but the way the data file is made, instead of having an output page for parameters 1,2,3, it has one output table where it displays the outputs for a given parameter (1,2,3) that is put into a cell. so what I want to do is be able to insert a line in a file saying: 1)insert value of cell C1 given the parameter input as 1 2) while also in another part of the work book insert a line saying "insert value of C1 given the parameter is 2. – Chris Vee Jul 01 '14 at 14:59

1 Answers1

2

There is a very inelegant way of doing it, but we can keep the interface as pretty as possible.

  1. Open the workbook with a Application.Workbooks.Open()
  2. Set its attribute to Hidden for aesthetic reasons.
  3. Say you want to manipulate cells on "Sheet1" then Set InputRange and OutputRange (cells where you'll see the outputs) to the appropriate ranges in the opened workbook.
  4. Change InputRange.Cells(m,n).Value

  5. Get into some variable what happens, for e.g. 

    Dim MyAnswer as Double 
MyAnswer =  OutputRange.Cells(x,y)

  6. Close the workbook, preferably not saving it (as your programming logic requires) and use the MyAnswer value as your 'function' output.

smagnan
  • 1,197
  • 15
  • 29
hnk
  • 2,216
  • 1
  • 13
  • 18
  • Actually I get this now - but this won't make it so the value is dynamic right? – Chris Vee Jul 01 '14 at 15:40
  • I'm not sure if I understand this correctly. Do you wish to change a value in a spreadsheet and use the other spreadsheet as a function and see the answer back in your sheet? If so, you'll need to open FunctionWorkbook, modify the input-cell in it so it's replaced by a formula "=MyDisplaySheetInputCellReference" and then add a reference to FunctionWorkbook in your DisplaySheet. IMHO, not worth it. It's far more efficient to re-encode it, or at worst, do it via a macro... and to make it dynamic, maybe you can trap a Worksheet_Change() event so your macro runs whenever the input cells change. – hnk Jul 01 '14 at 16:47
  • Hi, was the answer posted above useful? If so, please accept and vote on it. Thanks – hnk Jul 02 '14 at 17:52