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

my goal implement of functions give them parameters of power, frequency , speed of electric motor, , in workbook (in have motor data) , return size, shaft diameter , other motor details.

as have not mastered vba tried implement function goes cell in workbook , returns value:

function test() string dim name string     workbooks.open("d:\exceltest\wbsource.xlsm").sheets("sheet1")       name = .cells(2, 3)    end    test= name    activeworkbook.save   activeworkbook.close  end function 

the problem gives me #value! error, each variable used defined string , cells has general format (if change cells format text gives me same message).

try might, not workbooks.open work in function, if function calls sub. open catalogue file in workbook open event, , close again in before close event.

in vproject explorer, right click on "thisworkbook," , "view code".
in pick list @ top, select workbook, , sub workbook_open() procedure should created. if not, select "open" in right pick list. put in following:

application.workbooks.open ("d:\exceltest\wbsource.xlsm") thisworkbook.activate 'restores "focus" worksheet 

then click right pick list , select "beforeclose" , put in

on error resume next 'this keeps crashing if catalogue closed first workbooks("wbsource.xlsm").close 

as long worksheet opens wbsource file first, function work.