VBA帮助缩短代码，引入例程

Question

我有一段通用代码用于隐藏我正在创建的excel文档的行，我将在这个电子表格中大量使用这些代码，并且已经到了出现错误"过程太大"的地步。有问题的代码是：

Private Sub Worksheet_Change(ByVal Target As Range)
''''''''General Selection statement'''''''
If Not Application.Intersect(Range("F7"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "-": Rows("8:20").EntireRow.Hidden = True
Case Is = "No": Rows("8:20").EntireRow.Hidden = False
Case Is = "Yes": Rows("8:20").EntireRow.Hidden = True
End Select
End If
If Not Application.Intersect(Range("B8"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "Other": Rows("9:10").EntireRow.Hidden = False
Case Is <> "Other": Rows("9:10").EntireRow.Hidden = True
End Select
End If
If Not Application.Intersect(Range("C11"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "-": Rows("12:19").EntireRow.Hidden = True
Case Is = "Yes": Rows("12:19").EntireRow.Hidden = False
Case Is = "No": Rows("12:19").EntireRow.Hidden = True
End Select
End If
End Sub

是否可以创建一个子例程，然后在需要时调用它？选定的单元格会改变，但范围会保持不变，我希望这有意义吗？我是VBA的新手，如果有任何帮助，我将不胜感激。

Answer 1

如果您需要重复代码，类似的代码应该放入循环或子例程中，您可以使用所需的参数调用这些子例程，因此使用不同的参数重复使用相同的子例程。

注意，Range(Target.Address)在测试上与仅写入Target相同。

例如，将以下内容放入一个模块中：

Option Explicit
Public Enum ToggleMode
tmYesNo
tmOther
End Enum
Public Sub ToggleRows(ByVal Mode As ToggleMode, ByVal CheckRange As Range, ByVal Target As Range, ByVal RowsAddress As String)
If Not Application.Intersect(CheckRange, Target) Is Nothing Then
Dim ToggleRange As Range
Set ToggleRange = Target.Parent.Rows(RowsAddress).EntireRow
If Mode = tmYesNo Then
Select Case Target.Value
Case "-", "Yes": ToggleRange.Hidden = True
Case "No":       ToggleRange.Hidden = False
End Select
ElseIf Mode = tmOther Then
Select Case Target.Value
Case Is = "Other":  ToggleRange.Hidden = False
Case Is <> "Other": ToggleRange.Hidden = True
End Select
End If
End If
End Sub

工作表中的以下代码：

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.CountLarge > 1 Then Exit Sub  'exit if target is a range and not a single cell.
ToggleRows tmYesNo, Me.Range("F7"), Target, "8:20"
ToggleRows tmOther, Me.Range("B8"), Target, "9:10"
ToggleRows tmYesNo, Me.Range("C11"), Target, "12:1"
End Sub