Excel 2010 : Macro for hidden column groups

Question

VBA 不是我的专长，但我们开始吧:

一旦隐藏或显示一组列，我想触发一个宏。我该如何存档？

我之前的研究结果

我能找到的唯一好的提示是this在 MSDN 上讨论。在这里，一个解决方案是使用以下方式起草的:

从 xlsx 文件的根目录创建文件 customUI\customUI.xml与内容

<customUI  xmlns="http://schemas.microsoft.com/office/2006/01/customui" >
    <commands >
        <command 
            idMso="ColumnsHide"
            onAction="ColumnHide_onAction"/>
        <command 
            idMso="ColumnsUnhide"
            onAction="ColumnUnhide_onAction"/>
    </commands >
</customUI >

并添加

<Relationship Id="edTAB" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customUI/customUI.xml" />

到 _rels\_rels.xml . (所有这一切使用 Visual Studio 可能要容易得多，但我无法访问微软世界中如此复杂的工具......)现在，可以通过以下方式使用宏:

Public Sub ColumnHide_onAction(control As IRibbonControl, ByRef cancelDefault)
'
' Code for onAction callback. Ribbon control command
'
    MsgBox "Ribbon Column Hide"
    cancelDefault = False

End Sub
Public Sub ColumnUnhide_onAction(control As IRibbonControl, ByRef cancelDefault)
'
' Code for onAction callback. Ribbon control command
'
    MsgBox "Ribbon Column Unhide"
    cancelDefault = False
End Sub

这种方法完美地捕获了列的隐藏和取消隐藏，但不能隐藏和取消隐藏组。所以，接近，但不完全在那里。

下载可能的 idMso来自 here 的值，我收到了 GroupViewShowHide的通知控制。使用方法与 ColumnsHide 相同或 ColumnsUnhide但是，不会归档所需的结果。

有任何想法吗？

Answer 1

对于隐藏列组，我注意到您没有使用 .Hidden您的代码示例中的属性。它可能非常有用，尤其是当您在数组中定义一组列时。例如:For byti = 0 To UBound(cols())

If Hide Then

ActiveSheet.columns(cols(byti)).EntireColumn.Hidden = True...等等。

要检查一组列是否已经隐藏，您还可以使用数组。通过将列分配给数组来对列进行分组，然后将工作表的当前状态(隐藏哪些列，隐藏哪些列)与该数组进行比较。

下面的代码是一个开始的建议，你可以适应你的项目。它不需要您在问题中提到的 customUI.xml 文件。

关键部分是用于检查列是否隐藏的 MyColumnCheck 变体，以及 .Match方法。这是 Match 的 VBA 等效项电子表格功能。

处理这段代码教会了我很多关于如何在数组中搜索，以及使用 Match 的起起落落。与其他方法相比 - 例如 Find只是循环一个数组!这已经在几篇文章中讨论过了，见 this one举个例子。我选择做Match而不是 For...Next循环，虽然很容易包含 For..Next检查隐藏列是否在您分配的组中的循环。

如果您想知道 IfError陈述:Application.IfError(Application.Match(MyColumnCheck.Column, MyColumnArray, 0),......这是因为使用 Match如前所述 here 在 VBA 代码中通常有些棘手.此外，正如@Lori_m 所写 here , “使用不带 .WorksheetFunction 的应用程序会返回一个变体，该变体允许在参数和结果中使用数组。”

此外，我选择在检查组数组的值时将它们更改为 -1，因此当该过程完成时，一个简单的数学运算将显示数组引用的所有列是否都被隐藏。对于此检查，负数更好，因为我假设您将引用只有正数的实际列。

所以，总而言之，.Match可以有效地用于检查工作表上的隐藏列是否与数组定义的一组列匹配。

'the column group you're looking for, dim as a dynamic array of column numbers
Dim MyColumnArray(1) As Long
'MyColumnArray(0) is column 2 or "B", MyColumnArray(1) is column 3 or "C", etc
MyColumnArray(0) = 2
MyColumnArray(1) = 3
Dim MyColumnCheck As Variant 'column check

For Each MyColumnCheck In Worksheets("Sheet1").columns
  'if the column is hidden and exists in MyColumnArray array...
  If columns(MyColumnCheck.Column).EntireColumn.Hidden = True And _
  Application.IfError(Application.Match(MyColumnCheck.Column, MyColumnArray, 0), 0) > 0 _
  Then
    MyColumnArray(Application.Match(MyColumnCheck.Column, MyColumnArray, 0) - 1) = -1
    '... set that element of the MyColumnArray array to -1.
  End If
Next

If WorksheetFunction.Sum(MyColumnArray) = 0 - (UBound(MyColumnArray) + 1) Then
  Debug.Print "group MyColumnArray is hidden"
  'execute code here for when the group is hidden
Else
  Debug.Print "group MyColumnArray is visible"
  'execute code here for when the group is visible
End If