Tyler Cowan February 2016

VBA EVALUATE() Misbehavior

I have a set of code that seeks out vlookups within formulas within a range of a sheet and calculates them. This worked perfectly and then one morning I came in and it didnt. Offending Code here:

sValue = Application.Evaluate(sVLOOKUP)
If Len(sValue) = 0 Then sValue = "#N/A"

for example

=VLOOKUP(Box,A1:B2,2,0)

in table

A    B
Box  1
Car  2

When manually highlighting the VLOOKUP within the in question sheet and calculating all is fine, however, this code always pulls NA because the evaluation always evaluates to ERROR 2042

My question then is what could cause the EVALUATE function to always evaluate to ERROR 2042 (NA) when the vlookup is without a doubt evaluatable?

EDIT

it would seem that the underlying issue is that I am using a form tool to reference the desired sub something to do with

Dim OutputFolder As String, GetBook As String, BookCopy As String, wb As Workbook
OutputFolder = GetFolder("C:\")
GetBook = ActiveWorkbook.Name
BookCopy = OutputFolder & "\Client Copy " & GetBook

ActiveWorkbook.SaveCopyAs BookCopy

Set wb = Workbooks.Open(BookCopy)

Dim wsDataLoad As Worksheet, wsEconomics As Worksheet, wsUI As Worksheet, wsTypeCurves As Worksheet, wsSWM As Worksheet, wsCC As Worksheet, wsSummary As Worksheet

Set wsDataLoad = wb.Sheets("DataLoad")

interaction with the formtool vs running the sub directly alters the desired output of the sub

Answers


Tim Williams February 2016

Application.Evaluate has a counter-part Worksheet.Evaluate - you should use the latter if you want to be sure your string is evaluated in the context of a specific sheet, and doesn't just default to whatever sheet is active when you run your code.

Since you've stated the string sVLOOKUP holds the correct value, this seems to be the likely explanation for what you're seeing.

Post Status

Asked in February 2016
Viewed 2,129 times
Voted 8
Answered 1 times

Search




Leave an answer