如何判断Excel中Sheet Tab区域可见工作表标签及解决UIAutomation判断失效问题

问题现状 #

• Worksheet.Tab对象没有直接标识标签是否可见的属性
• 使用UIAutomationElement的IsOffScreen属性时，无论工作表标签是否被遮挡，始终返回false，无法判断可见性（测试代码如下）：

Sub Test()
    Dim AppObj As UIAutomationClient.IUIAutomationElement
    Dim ouAutomation As New CUIAutomation
    Set AppObj = WalkEnabledElements("Excel")
    
    ' 定位目标工作表标签
    Dim ControlName As UIAutomationClient.IUIAutomationCondition
    Dim ControlType As UIAutomationClient.IUIAutomationCondition
    Set ControlName = ouAutomation.CreatePropertyCondition(UIA_NamePropertyId, "Sayfa7")
    Set ControlType = ouAutomation.CreatePropertyCondition(UIA_ControlTypePropertyId, UIA_TabItemControlTypeId)
    
    Dim testElement As IUIAutomationCondition
    Set testElement = ouAutomation.CreateAndCondition(ControlName, ControlType)
    Set MyElement = AppObj.FindFirst(TreeScope_Descendants, testElement)
    
    If MyElement Is Nothing Then Exit Sub
    ' 输出始终为false
    Debug.Print MyElement.CurrentIsOffscreen
    Debug.Print MyElement.GetCurrentPropertyValue(UIA_IsOffscreenPropertyId)
End Sub

可行解决方案 #

由于UIA的IsOffScreen属性在Excel工作表标签上失效，可通过对比标签的边界矩形与Tab区域的可见范围来判断是否可见：

1. 通过UIA定位到Excel的TabControl元素（即底部标签栏的容器），获取其边界矩形
2. 获取目标工作表标签的边界矩形
3. 计算两个矩形的交集，若交集宽度大于0，说明标签有部分在可见区域内，判定为可见

修改后的示例代码：

Sub CheckSheetTabVisibility()
    Dim ouAutomation As New CUIAutomation
    Dim excelWindow As IUIAutomationElement
    Set excelWindow = WalkEnabledElements("Excel")
    If excelWindow Is Nothing Then Exit Sub
    
    ' 1. 获取TabControl容器（底部标签栏）
    Dim tabControlCondition As IUIAutomationCondition
    Set tabControlCondition = ouAutomation.CreatePropertyCondition(UIA_ControlTypePropertyId, UIA_TabControlTypeId)
    Dim tabControlElement As IUIAutomationElement
    Set tabControlElement = excelWindow.FindFirst(TreeScope_Descendants, tabControlCondition)
    If tabControlElement Is Nothing Then Exit Sub
    
    ' 获取TabControl的边界矩形
    Dim tabControlRect As UIAutomationClient.tagRECT
    tabControlRect = tabControlElement.CurrentBoundingRectangle
    
    ' 2. 获取目标工作表标签
    Dim targetTabName As String
    targetTabName = "Sayfa7" ' 替换为你的工作表名
    Dim tabNameCondition As IUIAutomationCondition
    Set tabNameCondition = ouAutomation.CreatePropertyCondition(UIA_NamePropertyId, targetTabName)
    Dim tabItemCondition As IUIAutomationCondition
    Set tabItemCondition = ouAutomation.CreatePropertyCondition(UIA_ControlTypePropertyId, UIA_TabItemControlTypeId)
    Dim targetTabCondition As IUIAutomationCondition
    Set targetTabCondition = ouAutomation.CreateAndCondition(tabNameCondition, tabItemCondition)
    
    Dim targetTabElement As IUIAutomationElement
    Set targetTabElement = excelWindow.FindFirst(TreeScope_Descendants, targetTabCondition)
    If targetTabElement Is Nothing Then
        Debug.Print "未找到目标工作表标签"
        Exit Sub
    End If
    
    ' 获取目标标签的边界矩形
    Dim targetTabRect As UIAutomationClient.tagRECT
    targetTabRect = targetTabElement.CurrentBoundingRectangle
    
    ' 3. 计算两个矩形的交集，判断是否可见
    Dim intersectLeft As Long, intersectTop As Long
    Dim intersectRight As Long, intersectBottom As Long
    
    intersectLeft = IIf(targetTabRect.Left > tabControlRect.Left, targetTabRect.Left, tabControlRect.Left)
    intersectTop = IIf(targetTabRect.Top > tabControlRect.Top, targetTabRect.Top, tabControlRect.Top)
    intersectRight = IIf(targetTabRect.Right < tabControlRect.Right, targetTabRect.Right, tabControlRect.Right)
    intersectBottom = IIf(targetTabRect.Bottom < tabControlRect.Bottom, targetTabRect.Bottom, tabControlRect.Bottom)
    
    ' 交集宽度大于0则说明标签可见（至少部分可见）
    If intersectRight > intersectLeft Then
        Debug.Print targetTabName & " 标签可见"
    Else
        Debug.Print targetTabName & " 标签不可见（被遮挡）"
    End If
End Sub

' 原代码中的WalkEnabledElements函数（假设已实现）
Function WalkEnabledElements(windowTitle As String) As IUIAutomationElement
    ' 此处为原函数实现，用于定位Excel窗口元素
End Function

说明 #

• 该方法通过矩形交集计算判断可见性，避免了IsOffScreen属性失效的问题
• 若需要判断标签完全可见，可额外判断标签的左右边界是否完全在TabControl的边界内

内容的提问来源于stack exchange，提问作者Berk Kılınç