vba - Excel UDF: retrieving value from arbitrary, closed, external workbook -

i looking way return value arbitrary workbook (the workbook not open @ time of running udf), defined based on calculations in udf.

pseudo code:

start calling =somefunc(currentcell) in cell  function somefunc(adr range) region_eval = "c" & range(adr).row ' column c contains string entries, of have corresponding sub-dir (see filereference). networklocation = activeworkbook.path networkpath = networklocation & "\locations\" filereference = networkpath & region_eval & "\productlist.xlsx"  workbook.open filereference readonly     perform index/match call against sheet in workbook somefunc = returned value close workbook , end function 

this desired behavior.

the logic return desired values ok, have tried in simpler formula, , in udf relies on file being opened manually:

index(locationlist_$a$5000, match(masterlist_a1, locationlist_$b$5000))   

i have, after hours of hair-pulling, discovered functionality not directly available in udf designed work on workbooks aren't opened manually, , intended microsoft's side. have discovered there possible workaround!

ref:
1. https://stackoverflow.com/a/27844592/4604845
2. http://numbermonger.com/2012/02/11/excel-pull-function-creating-dynamic-links-to-closed-workbooks/

these solutions require hardcoded file paths, defeats purpose intended usage.

is there has insight how achieve achieved in of 2 above links, arbitrary filepath (as in, contained in cell neighbouring cell udf being called from)?

note: tried doing heavy lifting in sub, , call sub first line in udf, set result global var, , set udf return value same var after sub finished, either crashed , burned pretty heavily or excel saw through trick , denied it.

edit:

here's sub/func combo.

option explicit  public networklocation string, networkpath string, filereference string, c_formula string public sheet_src worksheet, sheet worksheet, wb_src workbook, wb workbook public region_eval string, sheetname_src string, sheetname string, regionpath string, filename string  sub findproductstatus(adr range)     networklocation = activeworkbook.path     networkpath = networklocation & "\locations\"     sheetname_src = "sheet1"     sheetname = "sheet1"      set wb_src = thisworkbook     set sheet_src = wb_src.sheets(sheetname_src)      region_eval = range("i" & adr.row)     regionpath = networkpath & region_eval     'filereference = regionpath & "\productlist.xlsx"     filename = "productlist.xlsx"     chdir regionpath      workbooks.open filename:=filename, readonly:=true      'set wb = workbooks.open(filename:=thisworkbook.path & "\locations\test\productlist.xlsx", readonly:=true)     set wb = workbooks("productlist.xlsx")     set sheet = wb.sheets(sheetname)      c_formula = application.worksheetfunction.index(sheet.range("$k$2:$k$5000"), application.worksheetfunction.match(sheet_src.range("a" & adr.row), sheet.range("$a$2:$a$5000"), 0))  end sub   function getproductstatus(adr range) string      call findcoursestatus(adr)     getcoursestatus = c_formula     wb.close  end function 

i haven't tested sub/func combo against open file, when of code inside function , file in question opened manually, worked flawlessly. stepping through code , using debug.print, see though "workbooks.open ..." goes through without discernible error, workbook doesn't opened, , thus, when try use workbook object set sheet, function/sub terminates.

this can achieved combination of udf() , event macro.

to retrieve data closed workbook, need 4 things:

1. the path
2. the filename
3. the sheetname
4. the cell address

the thing udf display these items in specific format:

public function somefunc() string     dim wbpath string, wbname string     dim wsname string, cellref string     dim ret string      wbpath = "c:\testfolder\"     wbname = "abc.xls"     wsname = "xxx"     cellref = "b9"      somefunc = "'" & wbpath & "[" & wbname & "]" & _           wsname & "'!" & range(cellref).address(true, true, -4150) end function 

take note of position of single quotes.

we use calculate event macro detect udf's execution , retrieve data:

private sub worksheet_calculate()   dim r1 range, r2 range   set r1 = range("c3")   set r2 = range("c4")   r2.value = executeexcel4macro(r1.value) end sub 

the calculate macro needs know udf returning string (c3) , needs know put retrieved data (c4).