CODEWITHSUNDEEP
excelvbafunctionevaluate
Y2kShea
Y2kShea

Reputation: 45

Evaluate() Function Returning Error 2023, however there is no error

I am using the Evaluate() function to test a value for errors prior to placing into a cell. In my example below, this is returning Error 2023, However when I paste the string into a cell, it reads correctly with no error (will return exactly the value referenced in the external spreadsheet). The example is as follows: 

Evaluate("='H:\@Projects\@Dev[BudgetTracking_ProjectName_MSTR_FK_2.xlsm]Budget Estimate (2)'!F44")

Upvotes: 2

Views: 4400

Answers (1)

Axel Richter
Axel Richter

Reputation: 61870

The Error 2023 is the xlErrRef #REF!. The Evaluate function can't directly evaluate from closed workbooks. Open the H:\@Projects\@Dev\BudgetTracking_ProjectName_MSTR_FK_2.xlsm and 

Evaluate("='H:\@Projects\@Dev\[BudgetTracking_ProjectName_MSTR_FK_2.xlsm]Budget Estimate (2)'!F44")

will work. Note the "\" behind the directory name "@Dev".

But then of course

Evaluate("='[BudgetTracking_ProjectName_MSTR_FK_2.xlsm]Budget Estimate (2)'!F44")

will suffice.

To get values from closed workbooks you have to use an Excel4Macro functionality. See: http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/

Upvotes: 2

Related Questions