优化冗余Excel VBA代码(Optimize Redundant Excel VBA Code)

优化冗余Excel VBA代码(Optimize Redundant Excel VBA Code)

用于学习目的的快速问题。 我下面的代码工作正常，除非我确定这是我的工作簿开始放慢的几个原因之一。

基本上，在定义的范围单元“propcount”上设置一个返回数字的公式。 我想根据这个数字显示特定的表格，但下面的代码非常多余。 有人可以帮我解决吗？ 另请注意工作表＃不一定按顺序排列。 目前，它影响了14到29页，但它跳过28; 在未来它可能会变得越来越没有组织。

有任何想法吗？

Private Sub Worksheet_Calculate() If Range("propcount") = "0" Then Sheet14.Visible = xlVeryHidden Sheet15.Visible = xlVeryHidden Sheet16.Visible = xlVeryHidden Sheet17.Visible = xlVeryHidden Sheet18.Visible = xlVeryHidden Sheet19.Visible = xlVeryHidden Sheet20.Visible = xlVeryHidden Sheet21.Visible = xlVeryHidden Sheet22.Visible = xlVeryHidden Sheet23.Visible = xlVeryHidden Sheet24.Visible = xlVeryHidden Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "1" Then Sheet14.Visible = True Sheet15.Visible = xlVeryHidden Sheet16.Visible = xlVeryHidden Sheet17.Visible = xlVeryHidden Sheet18.Visible = xlVeryHidden Sheet19.Visible = xlVeryHidden Sheet20.Visible = xlVeryHidden Sheet21.Visible = xlVeryHidden Sheet22.Visible = xlVeryHidden Sheet23.Visible = xlVeryHidden Sheet24.Visible = xlVeryHidden Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "2" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = xlVeryHidden Sheet17.Visible = xlVeryHidden Sheet18.Visible = xlVeryHidden Sheet19.Visible = xlVeryHidden Sheet20.Visible = xlVeryHidden Sheet21.Visible = xlVeryHidden Sheet22.Visible = xlVeryHidden Sheet23.Visible = xlVeryHidden Sheet24.Visible = xlVeryHidden Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "3" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = xlVeryHidden Sheet18.Visible = xlVeryHidden Sheet19.Visible = xlVeryHidden Sheet20.Visible = xlVeryHidden Sheet21.Visible = xlVeryHidden Sheet22.Visible = xlVeryHidden Sheet23.Visible = xlVeryHidden Sheet24.Visible = xlVeryHidden Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "4" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = xlVeryHidden Sheet19.Visible = xlVeryHidden Sheet20.Visible = xlVeryHidden Sheet21.Visible = xlVeryHidden Sheet22.Visible = xlVeryHidden Sheet23.Visible = xlVeryHidden Sheet24.Visible = xlVeryHidden Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "5" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = True Sheet19.Visible = xlVeryHidden Sheet20.Visible = xlVeryHidden Sheet21.Visible = xlVeryHidden Sheet22.Visible = xlVeryHidden Sheet23.Visible = xlVeryHidden Sheet24.Visible = xlVeryHidden Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "6" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = True Sheet19.Visible = True Sheet20.Visible = xlVeryHidden Sheet21.Visible = xlVeryHidden Sheet22.Visible = xlVeryHidden Sheet23.Visible = xlVeryHidden Sheet24.Visible = xlVeryHidden Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "7" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = True Sheet19.Visible = True Sheet20.Visible = True Sheet21.Visible = xlVeryHidden Sheet22.Visible = xlVeryHidden Sheet23.Visible = xlVeryHidden Sheet24.Visible = xlVeryHidden Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "8" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = True Sheet19.Visible = True Sheet20.Visible = True Sheet21.Visible = True Sheet22.Visible = xlVeryHidden Sheet23.Visible = xlVeryHidden Sheet24.Visible = xlVeryHidden Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "9" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = True Sheet19.Visible = True Sheet20.Visible = True Sheet21.Visible = True Sheet22.Visible = True Sheet23.Visible = xlVeryHidden Sheet24.Visible = xlVeryHidden Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "10" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = True Sheet19.Visible = True Sheet20.Visible = True Sheet21.Visible = True Sheet22.Visible = True Sheet23.Visible = True Sheet24.Visible = xlVeryHidden Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "11" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = True Sheet19.Visible = True Sheet20.Visible = True Sheet21.Visible = True Sheet22.Visible = True Sheet23.Visible = True Sheet24.Visible = True Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "12" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = True Sheet19.Visible = True Sheet20.Visible = True Sheet21.Visible = True Sheet22.Visible = True Sheet23.Visible = True Sheet24.Visible = True Sheet25.Visible = True Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "13" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = True Sheet19.Visible = True Sheet20.Visible = True Sheet21.Visible = True Sheet22.Visible = True Sheet23.Visible = True Sheet24.Visible = True Sheet25.Visible = True Sheet26.Visible = True Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "14" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = True Sheet19.Visible = True Sheet20.Visible = True Sheet21.Visible = True Sheet22.Visible = True Sheet23.Visible = True Sheet24.Visible = True Sheet25.Visible = True Sheet26.Visible = True Sheet27.Visible = True Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "15" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = True Sheet19.Visible = True Sheet20.Visible = True Sheet21.Visible = True Sheet22.Visible = True Sheet23.Visible = True Sheet24.Visible = True Sheet25.Visible = True Sheet26.Visible = True Sheet27.Visible = True Sheet29.Visible = True End If End Sub

quick question for learning purposes. The code I have below works fine, except I'm pretty sure it's one of a couple reasons my Workbook is starting to slow down.

Basically, a formula is set up on the defined range cell "propcount" that returns a number. I want to display specific sheets based on this number, but my code below is extremely redundant. Can someone help me fix? Also please note the Sheet #s are not necessarily in order. Currently, it affects Sheets 14 through 29 but it skips 28; in the future it could become increasingly less organized.

Any ideas?

Private Sub Worksheet_Calculate() If Range("propcount") = "0" Then Sheet14.Visible = xlVeryHidden Sheet15.Visible = xlVeryHidden Sheet16.Visible = xlVeryHidden Sheet17.Visible = xlVeryHidden Sheet18.Visible = xlVeryHidden Sheet19.Visible = xlVeryHidden Sheet20.Visible = xlVeryHidden Sheet21.Visible = xlVeryHidden Sheet22.Visible = xlVeryHidden Sheet23.Visible = xlVeryHidden Sheet24.Visible = xlVeryHidden Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "1" Then Sheet14.Visible = True Sheet15.Visible = xlVeryHidden Sheet16.Visible = xlVeryHidden Sheet17.Visible = xlVeryHidden Sheet18.Visible = xlVeryHidden Sheet19.Visible = xlVeryHidden Sheet20.Visible = xlVeryHidden Sheet21.Visible = xlVeryHidden Sheet22.Visible = xlVeryHidden Sheet23.Visible = xlVeryHidden Sheet24.Visible = xlVeryHidden Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "2" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = xlVeryHidden Sheet17.Visible = xlVeryHidden Sheet18.Visible = xlVeryHidden Sheet19.Visible = xlVeryHidden Sheet20.Visible = xlVeryHidden Sheet21.Visible = xlVeryHidden Sheet22.Visible = xlVeryHidden Sheet23.Visible = xlVeryHidden Sheet24.Visible = xlVeryHidden Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "3" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = xlVeryHidden Sheet18.Visible = xlVeryHidden Sheet19.Visible = xlVeryHidden Sheet20.Visible = xlVeryHidden Sheet21.Visible = xlVeryHidden Sheet22.Visible = xlVeryHidden Sheet23.Visible = xlVeryHidden Sheet24.Visible = xlVeryHidden Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "4" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = xlVeryHidden Sheet19.Visible = xlVeryHidden Sheet20.Visible = xlVeryHidden Sheet21.Visible = xlVeryHidden Sheet22.Visible = xlVeryHidden Sheet23.Visible = xlVeryHidden Sheet24.Visible = xlVeryHidden Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "5" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = True Sheet19.Visible = xlVeryHidden Sheet20.Visible = xlVeryHidden Sheet21.Visible = xlVeryHidden Sheet22.Visible = xlVeryHidden Sheet23.Visible = xlVeryHidden Sheet24.Visible = xlVeryHidden Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "6" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = True Sheet19.Visible = True Sheet20.Visible = xlVeryHidden Sheet21.Visible = xlVeryHidden Sheet22.Visible = xlVeryHidden Sheet23.Visible = xlVeryHidden Sheet24.Visible = xlVeryHidden Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "7" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = True Sheet19.Visible = True Sheet20.Visible = True Sheet21.Visible = xlVeryHidden Sheet22.Visible = xlVeryHidden Sheet23.Visible = xlVeryHidden Sheet24.Visible = xlVeryHidden Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "8" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = True Sheet19.Visible = True Sheet20.Visible = True Sheet21.Visible = True Sheet22.Visible = xlVeryHidden Sheet23.Visible = xlVeryHidden Sheet24.Visible = xlVeryHidden Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "9" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = True Sheet19.Visible = True Sheet20.Visible = True Sheet21.Visible = True Sheet22.Visible = True Sheet23.Visible = xlVeryHidden Sheet24.Visible = xlVeryHidden Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "10" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = True Sheet19.Visible = True Sheet20.Visible = True Sheet21.Visible = True Sheet22.Visible = True Sheet23.Visible = True Sheet24.Visible = xlVeryHidden Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "11" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = True Sheet19.Visible = True Sheet20.Visible = True Sheet21.Visible = True Sheet22.Visible = True Sheet23.Visible = True Sheet24.Visible = True Sheet25.Visible = xlVeryHidden Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "12" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = True Sheet19.Visible = True Sheet20.Visible = True Sheet21.Visible = True Sheet22.Visible = True Sheet23.Visible = True Sheet24.Visible = True Sheet25.Visible = True Sheet26.Visible = xlVeryHidden Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "13" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = True Sheet19.Visible = True Sheet20.Visible = True Sheet21.Visible = True Sheet22.Visible = True Sheet23.Visible = True Sheet24.Visible = True Sheet25.Visible = True Sheet26.Visible = True Sheet27.Visible = xlVeryHidden Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "14" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = True Sheet19.Visible = True Sheet20.Visible = True Sheet21.Visible = True Sheet22.Visible = True Sheet23.Visible = True Sheet24.Visible = True Sheet25.Visible = True Sheet26.Visible = True Sheet27.Visible = True Sheet29.Visible = xlVeryHidden ElseIf Range("propcount") = "15" Then Sheet14.Visible = True Sheet15.Visible = True Sheet16.Visible = True Sheet17.Visible = True Sheet18.Visible = True Sheet19.Visible = True Sheet20.Visible = True Sheet21.Visible = True Sheet22.Visible = True Sheet23.Visible = True Sheet24.Visible = True Sheet25.Visible = True Sheet26.Visible = True Sheet27.Visible = True Sheet29.Visible = True End If End Sub

最满意答案

循环显示设置它们。 从第一张纸到第一张纸，从而使它们可见。 像这样的东西。

Dim ws As Excel.Worksheet Dim iIndex as Integer if Range("propcount") > 0 then For iIndex = 14 To 14 + Range("propcount") Set ws = Worksheets(iIndex) ws.Visible = true Next iIndex End if

或许你想要隐藏它们。 在这种情况下，你可以从人数到最后一张。

Dim ws As Excel.Worksheet Dim iIndex as Integer if Range("propcount") > 0 then For iIndex = 14 + Range("propcount") To ActiveWorkbook.Worksheets.count Set ws = Worksheets(iIndex) ws.Visible = xlVeryHidden Next iIndex End if

如果纸张不总是处于可靠状态，您可能希望同时使用显示和隐藏。

Dim ws As Excel.Worksheet Dim iIndex as Integer if Range("propcount") > 0 then For iIndex = 14 To 14 + Range("propcount") Set ws = Worksheets(iIndex) ws.Visible = true Next iIndex For iIndex = 14 + Range("propcount") To ActiveWorkbook.Worksheets.count Set ws = Worksheets(iIndex) ws.Visible = xlVeryHidden Next iIndex End if

Loop through the sheets setting them. Loop from the first sheet to the propcount making them visible. Something like this.

Dim ws As Excel.Worksheet Dim iIndex as Integer if Range("propcount") > 0 then For iIndex = 14 To 14 + Range("propcount") Set ws = Worksheets(iIndex) ws.Visible = true Next iIndex End if

Or maybe you want to hide them. In that case you could go from the propcount to the last sheet.

Dim ws As Excel.Worksheet Dim iIndex as Integer if Range("propcount") > 0 then For iIndex = 14 + Range("propcount") To ActiveWorkbook.Worksheets.count Set ws = Worksheets(iIndex) ws.Visible = xlVeryHidden Next iIndex End if

If the sheets aren't always in a reliable state you may want to use both showing and hiding.

Dim ws As Excel.Worksheet Dim iIndex as Integer if Range("propcount") > 0 then For iIndex = 14 To 14 + Range("propcount") Set ws = Worksheets(iIndex) ws.Visible = true Next iIndex For iIndex = 14 + Range("propcount") To ActiveWorkbook.Worksheets.count Set ws = Worksheets(iIndex) ws.Visible = xlVeryHidden Next iIndex End if