确定单元格是否通过Excel中的VBA链接到QueryTable [英] Determining if a cell is linked to a QueryTable via VBA in Excel

问题描述

我正在使用效果很好的QueryTables将Excel工作表链接到数据源.我想介绍一下逻辑来检查给定的单元格并确定该单元格是否是现有QueryTable的左上角.这在概念上看似微不足道，但在实践中已被证明难以捉摸.如果我尝试检查与QueryTable相关的单元格的任何属性，则会收到错误消息.即如果rng未链接到QueryTable，则rng.querytable引发错误1004.因此，根据在网上找到的一些讨论，我搞砸了rng.ListObject is nothing的测试.事实证明，即使单元格是表，即使该表不是QueryTable，rng.ListObject也是如此.所以那里没有骰子.

I'm linking an Excel worksheet to a data source using QueryTables which works well. I'd like to introduce logic to check a given cell and determine if that cell is the top left corner of an existing QueryTable. This seemed trivial in concept, but has proven elusive in practice. If I try to check any properties of the cell that relate to QueryTable I get an error. i.e. rng.querytable throws error 1004 if rng does not link to a QueryTable. So I messed around with testing if rng.ListObject is nothing based on some discussions I found online. It turns out that rng.ListObject is something if the cell is a table even if that table is not a QueryTable. So no dice there.

那我该如何测试目标单元格是否包含QueryTable?

So how do I test if a target cell contains a QueryTable?

这是我从先生获得的函数的部分. Excel :

Public Function IsRangeInQueryTable(rngCheck As Range) As Boolean
    Dim QT As QueryTable

    For Each QT In rngCheck.Parent.QueryTables

        If Not Intersect(rngCheck, QT.ResultRange) Is Nothing Then
            IsRangeInQueryTable = True
            Exit Function
        End If

    Next QT

    'If it got to here then the specified range does not lie
    'in the result range of any query
    IsRangeInQueryTable = False

End Function

上面的函数在许多情况下都可以工作，但是如果我的QueryTable已从其目标分离(这似乎在实际中发生)，则该代码将引发错误1004，因为QueryTable没有目标.这是当QueryTable与目标解耦时手表显示的内容:

The function above works in many cases, but if I have QueryTables that have become detached from their destination (which seems to happen in practice) the code throws error 1004 because the QueryTable has no destination. Here's what the watch shows when the QueryTable becomes decoupled from a destination:

FWIW，我还尝试过相反的方法，并遍历工作表中的每个QueryTable.事实证明，是否有一个QueryTable的目标已删除，要求QueryTable的QueryTable.Destination引发应用错误.因此，我也找不到一种可靠的方法来执行该方法.

FWIW, I also tried approaching this the reverse way and iterating through every QueryTable in a worksheet. It turns out if there's a QueryTable which has had its destination deleted, asking that QueryTable for its QueryTable.Destination throws an app error. So I could not figure out a reliable way to do that approach either.

推荐答案

这比需要的要复杂，但是由于"OnError"语句会重定向所有错误，因此我想消除意外处理错误错误的可能性，例如rng是Nothing.

This is wordier than it needs to be, but because the "OnError" statement redirects all errors, I want to eliminate the possibility of accidentally handling the wrong error, for example if rng was Nothing.

Public Function cell_has_query(rng As Range) As Boolean

    If rng Is Nothing Then
       cell_has_query = False
       Exit Function
    End If

    If rng.ListObject Is Nothing Then
        cell_has_query = False
        Exit Function
    End If

On Error GoTo ErrHandler
    If Not rng.ListObject.QueryTable Is Nothing Then
        cell_has_query = True
    End If
    Exit Function

ErrHandler:
    If Err.Number = 1004 Then 'Application-Defined or Object-Defined Error
        cell_has_query = False
    Else
        On Error GoTo 0
        Resume
    End If
End Function

@JDLong VBA错误处理很奇怪.错误的默认设置为On Error GoTo 0，这意味着(取决于您的VBA IDE设置；工具->选项->常规)将弹出一个未处理错误的消息框.如果要显式捕获并处理错误，请创建标签(例如"ErrHandler")，然后通过以Exit Function结尾的函数来确保正常无法访问代码段.在标签后面的代码块中，您可以检查Err对象属性，然后选择Resume重试导致错误的代码行，Resume Next运行以下行错误的代码，或简单地处理错误并让函数正常退出的代码.您还可以通过将模式设置回On Error GoTo 0，然后Resume重新设置该行来重新出现该错误.

@JDLong VBA error handling is odd. The default setting for errors is On Error GoTo 0, which means that (depending upon your VBA IDE settings; Tools-->Options-->General) will pop up a message box for unhandled errors. If you want to explicitly catch and handle errors, you create a label (e.g. "ErrHandler") and then make sure that section of code is unreachable normally by ending the function with Exit Function. In the block of code after the label, you can inspect the Err object properties and choose to Resume to retry the line of code that caused the error, Resume Next to run the line following the one that errored, or simply handle the error and let the function exit normally. You can also reraise the error by setting the mode back to On Error GoTo 0 and then Resumeing the line.

这篇关于确定单元格是否通过Excel中的VBA链接到QueryTable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！