确定公式是否将返回#Ref！错误 [英] Determine if formula will return #Ref! error

问题描述

我正在Excel中编写一个应用程序，该应用程序从多个数据库中提取数据，并在Excel 2010工作簿的工作表中显示此数据.我从数据库中调用的某些数据是以Excel 2010公式的形式出现的(例如=预算估算"！E46).

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).

在从该数据集中填充工作表中的单个单元格的过程中，我需要能够评估我正在下载的公式是否会生成#Ref！将值放入单元格之前的错误.在上面的示例中，只要存在它引用的工作表，就可以通过VBA在单元格中放置该值.如果没有，我有一种找到它的方法，但是首先我需要检测它是否会产生错误，这样最终用户就不必处理弹出的对话框来询问该孤儿的路径工作表.

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.

推荐答案

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

请参阅: http://www.cpearson.com/excel/ReturningErrors.aspx

这篇关于确定公式是否将返回#Ref！错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！