2

I am writing an application within Excel that is pulling data from several databases and presenting this data within the worksheets of an Excel 2010 workbook. Some of the data that I'm recalling form the databases are in the form of Excel 2010 formulas (e.g. ='Budget Estimate'!E46).

In the process of populating individual cells in a worksheet from this dataset, I need to be able to evaluate if the formula I'm downloading will generate an #Ref! error in advance of placing the value in the cell. In the above example, placing that value in the cell via VBA works great provided the worksheet it references exist. If it doesn't, I have a way of finding it, but first I need to detect if it will create an error so the end user doesn't have to deal with the dialog boxes that pop up asking for the path to this orphaned worksheet.

L42
  • 19,427
  • 11
  • 44
  • 68
Y2kShea
  • 45
  • 1
  • 6
  • 1
    http://stackoverflow.com/questions/16105884/how-to-capture-ref-in-excel, this questian has an answer already – bto.rdz Jan 20 '15 at 05:25
  • `I need to be able to evaluate if the formula I'm downloading will generate an #Ref! error in advance of placing the value in the cell` so I think that the provided answer in the link doesn't cover that @TimWilliams – L42 Jan 20 '15 at 05:35
  • @L42 You're right - retracted my close vote – Tim Williams Jan 20 '15 at 06:09

2 Answers2

2
Sub Tester()

    Dim v
    v = Application.Evaluate("=Sheet999!A1")

    If IsError(v) Then
        Select Case v
            Case CVErr(xlErrRef): Debug.Print "#ref!"
            Case CVErr(xlErrDiv0): Debug.Print "#div by zero!"
            'etc
        End Select
    End If

End Sub

See: http://www.cpearson.com/excel/ReturningErrors.aspx

Community
  • 1
  • 1
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Tim - thank you. This is ideal, and will incorporate this subroutine in the event I need to test for specific errors in the future. Really appreciate your response so quickly, and your expertise. Thank you again. – Y2kShea Jan 20 '15 at 23:19
0

You may test your formula using Evaluate. Try:

If IsError(Evaluate("='Budget Estimate'!E46")) Then
    'other cool stuff here
End If

This will capture Errors before you put it in a cell however not specific to Reference Error.
This will capture all errors on the formula. Not really 100% the answer you're looking for but HTH.

L42
  • 19,427
  • 11
  • 44
  • 68
  • TTH! (That Totally Helps!) - I know my question was specific to that particular error, but reality is I don't care what the error is as long as I can detect that there is one in advance, and the function you provided above does this perfectly. Thank you! – Y2kShea Jan 20 '15 at 23:06
  • @Y2kShea Follow up is allowed as long as it does not deviate so much from your question. Well it seems that `Evaluate` doesn't work with closed workbooks. I tested it and was able to replicate what you described. If that is the case, you will have to use another approach. But what you want is still possible. :-) – L42 Jan 22 '15 at 05:38