在Excel 2010中导致SheetSelectionChange事件的SpecialCells [英] SpecialCells causing SheetSelectionChange event in Excel 2010

问题描述

我有一个测试宏

Sub test()
    Dim rSrcMatrix As Range
    Set rSrcMatrix = Sheets("Code Matrix").Range("Xfer_To_Xfer_Matrix").Range("A1")
    Set rSrcMatrix = rSrcMatrix.Resize(rSrcMatrix.SpecialCells(xlCellTypeLastCell).Row, rSrcMatrix.SpecialCells(xlCellTypeLastCell).Column)     
End Sub

我正在使用此宏来测试我在VS2010中创建的 COM 加载项.我已将外接程序中的SheetSelectionChange事件委托给了某些函数.

I am using this macro to test my COM addin that I have created in VS2010. I have delegated the SheetSelectionChange event in the addin to some function.

现在我注意到，每当我运行此宏时，Excel就会触发SheetSelectionChange事件4次，并且我的插件会多次调用关联的方法.

Now I notice that whenever I run this macro, Excel fires the SheetSelectionChange event 4 times and my addin calls the associated method for that many times.

我有什么想念的东西吗?或者这是Excel中的错误?

Is there anything that I am missing or is this a bug in excel?

推荐答案

我相信，我可能是错的，因为我找不到MSDN文章来证明这一点，但是SpecialCells执行一种选择并触发Worksheet_SelectionChange或Workbook_SheetSelectionChange事件，因此您需要关闭事件.

I believe and I could be wrong because I couldn't find an MSDN article to prove it but SpecialCells performs a type of selection and triggers the Worksheet_SelectionChange or the Workbook_SheetSelectionChange event and hence you need to switch off events.

这是一种简单的测试方法.

Here is a simple way to test it.

将此代码放在工作表代码"区域

Place this code in the Sheet Code Area

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox "Damn! The SpecialCells caused me to pop up!!!"
End Sub

Sub test()
    Debug.Print ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
End Sub

Worksheet_SelectionChange和Workbook_SheetSelectionChange做相同的工作.工作表代码中的Worksheet_SelectionChange用于特定的工作表.如果您希望事件在该工作簿中的所有工作表上触发，则使用Workbook_SheetSelectionChange.

Worksheet_SelectionChange and Workbook_SheetSelectionChange do the same job. Worksheet_SelectionChange is used in the sheet code are for a specific sheet. And Workbook_SheetSelectionChange is used when you want the event to fire across all the sheets in that workbook.

您在评论中的问题:如果我们想将另一个事件与该行代码相关联，该怎么办.在这种情况下，我们无法抑制该事件.

YOUR QUESTION FROM THE COMMENT: What if we wanted to associate another event with that line of code. In that case, we cannot suppress the event.

现在，我们有两种选择.根据您上面的问题，我们不能使用Alternative One.因此，您可以直接跳到Alternative 2

Now, we have two alternatives. Based on your above question we cannot use Alternative One. So you may directly skip to Alternative 2

替代1

关闭事件

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error GoTo Whoa

    Application.EnableEvents = False

    '
    '~~> YOUR CODE
    '

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

替代2

我们将使用.Find.而不是使用SpecialCells查找最后一行或最后一列.

Instead of using SpecialCells to find the last row or the last column, we will use .Find.

Sub test()
    Dim ws As Worksheet
    Dim rSrcMatrix As Range
    Dim Lrow As Long, LCol As Long

    Set ws = ThisWorkbook.Sheets("Code Matrix")

    With ws
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            Lrow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row

            LCol = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByColumns, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Column
        Else
            Lrow = 1
        End If

        Set rSrcMatrix = .Range("Xfer_To_Xfer_Matrix").Range("A1")
        Set rSrcMatrix = rSrcMatrix.Resize(Lrow, LCol)

        Debug.Print rSrcMatrix.Address
    End With
End Sub

这篇关于在Excel 2010中导致SheetSelectionChange事件的SpecialCells的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！