2

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")
L42
  • 19,427
  • 11
  • 44
  • 68
Y2kShea
  • 45
  • 1
  • 6
  • 1
    If this is a followup question to [Determine if formula will return #Ref! error](http://stackoverflow.com/q/28038285/445425) then use of `Evaluate` where the formula you want to test refers to a closed workbook will not work. An alternative might be to use `Application.DisplayAlerts = False` and just write the formula to the cell then test for `#Ref!`. This will suppress to File Open dialog. (Remember to `Application.DisplayAlerts = True` after) – chris neilsen Jan 22 '15 at 06:53
  • That is a great approach, Chris. The function does work, but there is another handling tier of this thing that I need to work the logic out for and this suggestion I think will be really useful. Thank you – Y2kShea Jan 26 '15 at 02:09

1 Answers1

2

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/

Axel Richter
  • 56,077
  • 6
  • 60
  • 87