vba - I need to vlookup in a close book without opening it. But i only get #VALUE -

function csimplex(codigo string)      dim rg range      set rg = workbooks.open("c:\users\mvela\documents\prueba.xlsm").worksheets("simplex").range("a:z")      csimplex = application.worksheetfunction.vlookup(codigo, rg, 3, false)  end function 

you cannot refer closed workbook in vba, not vlookup, not evaluate. solution snap workbook open, return value close workbook.

function csimplex(codigo string) variant      dim wb workbook, rng range      set wb = workbooks.open("c:\users\mvela\documents\prueba.xlsm", readonly:=true)     set rng = wb.worksheets("simplex").range("a:z")      csimplex = application.vlookup(codigo, rng, 3, false)      wb.close savechanges:=false   `<~~ close workbook!  end function 

an .xlsb smaller otherwise identical .xlsm , load, retrieve , close faster. if large number of times, build static array of values , use that.