excel - VBA EVALUATE() Misbehavior -

i have set of code seeks out vlookups within formulas within range of sheet , calculates them. worked , 1 morning came in , didnt. offending code here:

svalue = application.evaluate(svlookup) if len(svalue) = 0 svalue = "#n/a" 

for example

=vlookup(box,a1:b2,2,0) 

in table

a    b box  1 car  2 

when manually highlighting vlookup within in question sheet , calculating fine, however, code pulls na because evaluation evaluates error 2042

my question cause evaluate function evaluate error 2042 (na) when vlookup without doubt evaluatable?

edit

it seem underlying issue using form tool reference desired sub with

dim outputfolder string, getbook string, bookcopy string, wb workbook outputfolder = getfolder("c:\") getbook = activeworkbook.name bookcopy = outputfolder & "\client copy " & getbook  activeworkbook.savecopyas bookcopy  set wb = workbooks.open(bookcopy)  dim wsdataload worksheet, wseconomics worksheet, wsui worksheet, wstypecurves worksheet, wsswm worksheet, wscc worksheet, wssummary worksheet  set wsdataload = wb.sheets("dataload") 

interaction formtool vs running sub directly alters desired output of sub

application.evaluate has counter-part worksheet.evaluate - should use latter if want sure string evaluated in context of specific sheet, , doesn't default whatever sheet active when run code.

since you've stated string svlookup holds correct value, seems explanation you're seeing.