vba - VBA 代码中的参数数量错误或无效的属性分配错误

Question

希望有人可以在这里帮助我。

我正在尝试将一些代码放在一起，这些代码将根据两个单元格的值隐藏行。

我的代码如下:

 Sub hideSummaryDetailed()

 Application.ScreenUpdating = False
 Application.EnableEvents = False
 ActiveSheet.DisplayPageBreaks = False


 'Hide all the Rows based on the selection for Summary/Detailed data linked to cell A1
 If Cells(1, 1) = 0 Then
    Rows("23:336").Select Selection.EntireRow.Hidden = True

 ElseIf Cells(1, 1) = 1 And Cells(10, 5) = "All" Then
    Rows("23:43").Select Selection.EntireRow.Hidden = False
    Range("E11").Select
    Rows("44:336").Select Selection.EntireRow.Hidden = True

 ElseIf Cells(1, 1) = 2 And Cells(10, 5) = "All" Then
    Rows("23:126").Select Selection.EntireRow.Hidden = False
    Range("E11").Select
    Rows("127:336").Select Selection.EntireRow.Hidden = True

 ElseIf Cells(1, 1) = 1 And Cells(10, 5) = "Cardiff" Then
    Rows("128:148").Select Selection.EntireRow.Hidden = False
    Range("E11").Select
    Rows("23:127, 149:336").Select Selection.EntireRow.Hidden = True

 ElseIf Cells(1, 1) = 2 And Cells(10, 5) = "Cardiff" Then
    Rows("128:232").Select Selection.EntireRow.Hidden = False
    Range("E11").Select
    Rows("23:127, 233:336").Select Selection.EntireRow.Hidden = True

 ElseIf Cells(1, 1) = 1 And Cells(10, 5) = "Swansea" Then
    Rows("233:253").Select Selection.EntireRow.Hidden = False
    Range("E11").Select
    Rows("23:232, 254:336").Select Selection.EntireRow.Hidden = True

 ElseIf Cells(1, 1) = 2 And Cells(10, 5) = "Swansea" Then
    Rows("233:336").Select Selection.EntireRow.Hidden = False
    Range("E11").Select
    Rows("23:232").Select Selection.EntireRow.Hidden = True

 ElseIf Cells(1, 1) = 1 And Cells(10, 5) = "Both" Then
    Rows("128:148, 233:253").Select Selection.EntireRow.Hidden = False
    Range("E11").Select
    Rows("23:127, 149:232, 254:336").Select Selection.EntireRow.Hidden = True

 ElseIf Cells(1, 1) = 2 And Cells(10, 5) = "Both" Then
    Rows("128:336").Select Selection.EntireRow.Hidden = False
    Range("E11").Select
    Rows("23:127").Select Selection.EntireRow.Hidden = True

 End If

 Application.ScreenUpdating = True
 Application.EnableEvents = True

 Range("E11").Select

 End Sub

但是，当我运行它时，我不断收到一条错误消息，提示错误数量的参数或无效的属性分配，当我单击确定时，它不会突出显示代码的任何特定部分以指向我的方向。

我对 VBA 很陌生，并且已经搜索了几个论坛以获取有关此错误的建议，但我尝试过的任何方法都没有奏效，或者我不理解它。

任何帮助将不胜感激。

谢谢

Answer 1

这不是有效的 VBA 代码:

Rows("23:43").Select Selection.EntireRow.Hidden = False

请记住，VBA 中的一行就是一个命令。上面的行是两个单独的命令。这是有效的 VBA 代码:

Rows("23:43").Select 
Selection.EntireRow.Hidden = False

然而，现在我们处理代码本身的低效率。您正在使用 ActiveSheet隐含地，并且您依赖 Select .为了避免 Select和 Activate我建议从这里开始: How to avoid using Select in Excel VBA .

我们如何重构这段代码？简单的:

' Near the beginning of the module....
Dim Target as Worksheet

' Ideally, explicitly set this to the correct worksheet. 
' For now, this will still use the Activesheet, it will just
' do it more explicitly and reliably since it won't potentially change as the code is running.
Set Target = ActiveSheet


' Later in the code....

' Notice how clean this is. We rely on Target instead of an implicit ActiveSheet, and we
' don't have to rely on the `EntireRow` property of `Selection` since we are explicitly accessing
' the rows.

Target.Rows("23:24").Hidden = False

这不仅可以解决手头的错误，还可以帮助您避免许多常见错误，这些错误会导致以后无数小时的挫败和维护。