如何确定工作表单元格在 VBA 中是否可见/显示？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11943200/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to determine if a worksheet Cell is Visible/Displayed in VBA?
提问by Marwan ?????
I need to find if a cell is visible on the screen.
我需要找到一个单元格是否在屏幕上可见。
By visible, I don't mean hidden. I am specifically trying to find if a cell is currently displayed in the active sheet, or if it is not displayed, ie: it has been scrolled off of the visible active sheet.
可见，我的意思不是隐藏。我特别想找出一个单元格当前是否显示在活动工作表中，或者它是否未显示，即：它已从可见的活动工作表中滚动出来。
I have looked online, and can only find the following code which doesn't seem to work for me:
我在网上查看，只能找到以下似乎对我不起作用的代码：
Private Sub CommandButton1_Click()
With Worksheets(1).Cells(10, 10)
'MsgBox "Value: " & .Value & ", Top: " & .Top & ", Left: " & .Left
Dim visibleCells As Range
Set visibleCells = Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible)
If Intersect(Worksheets(1).Cells(10, 10), visibleCells) Is Nothing Then
MsgBox "This cell is not visible."
End If
End With
End Sub
Thanks in advance for your help,
在此先感谢您的帮助，
Marwan
马万
回答by Doug Glancy
Here's a function that does what you want:
这是一个可以完成您想要的功能的函数：
Function CellIsInVisibleRange(cell As Range)
CellIsInVisibleRange = Not Intersect(ActiveWindow.VisibleRange, cell) Is Nothing
End Function
At least I think it does. I hadn't been aware of the VisibleRange property until now.
至少我认为是的。直到现在我才知道 VisibleRange 属性。
Call it like:
像这样称呼它：
If CellIsInVisibleRange(ActiveSheet.Range("A35")) Then
MsgBox "Cell is visible"
Else
MsgBox "Cell isn't visible"
End If
回答by ChrisB
The function from @DougGlancy will work in most instances but it fails if the Range has a row height or column width set to zero. This function adds logic to deal with that plus some error handling.
@DougGlancy 的函数在大多数情况下都可以工作，但如果 Range 的行高或列宽设置为零，它就会失败。此函数添加了处理该问题的逻辑以及一些错误处理。
Function Range_IsVisibleInWindow(ByVal target As Excel.Range) As Boolean
' Returns TRUE if any cell in TARGET (Range) is visible in the Excel window.
'
' Visible means (1) not hidden, (2) does not have row height or column width of
' zero, (3) the view is scrolled so that the Range can be seen by the user at
' that moment.
'
' A partially visible cell will also return TRUE.
If target Is Nothing Then
' Parameter is invalid. Raise error.
Err.Raise 3672, _
"Range_IsVisibleInWindow()", _
"Invalid parameter in procedure 'Range_IsVisible'."
Else
' Parameter is valid. Check if the Range is visible.
Dim visibleWinLarge As Excel.Range
Dim visibleWinActual As Excel.Range
On Error Resume Next
Set visibleWinLarge = Excel.ActiveWindow.VisibleRange ' active window range -INCLUDING- areas with zero column width/height
Set visibleWinActual = visibleWinLarge.SpecialCells(xlCellTypeVisible) ' active window range -EXCLUDING- areas with zero column width/height
Range_IsVisibleInWindow = Not Intersect(target, visibleWinActual) Is Nothing ' returns TRUE if at least one cell in TARGET is currently visible on screen
On Error GoTo 0
End If
End Function
