CODEWITHSUNDEEP
vbaexcel
arbitel
arbitel

Reputation: 321

Optimize Redundant Excel VBA Code

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

Upvotes: 1

Views: 206

Answers (4)

Roland
Roland

Reputation: 976

You can also do this:

Sheets14.Visible = IIf(propcount > 0, True, xlVeryHidden)
...

Btw, reducing the lines of code for this task will not optimize performance IMHO. The problem might be that you are running this code on the event On Calculate

Upvotes: 1

MatthewD
MatthewD

Reputation: 6761

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

Upvotes: 1

Bond
Bond

Reputation: 16311

You may want to define the sheet names in an array. You could even store the configuration in a text or ini file and read it at the start of your function.

For example:

Dim a(15) As String
...
a(3) = "/14/15/16/"       ' Just store the sheet numbers that need to be visible
a(4) = "/14/15/16/17/"    ' Delimit them with any character you like
...

This gives you a lot of flexibility. You don't have to rely on your sheet names being sequential because, as you found with Sheet28, it's not always easy to keep things in order.

Then, just fetch the proper string based on your propcount value:

Dim strSheetList As String
strSheetList = a(Range("propcount"))

Finally, iterate all your sheets and check each one to see if it needs to be shown:

Dim sh As Worksheet, strSheetNum As String

For Each sh In Worksheets
    strSheetNum = Mid$(sh.CodeName, 6) 
    If InStr(strSheetList, "/" & strSheetNum & "/") Then
        sh.Visible = True
    Else
        sh.Visible = xlVeryHidden
    End If
Next

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166306

E.g.

Sub SetVis(propCount As Long)
    Dim arr, i As Long

    arr = Array(Sheet14, Sheet15, Sheet16, Sheet17, Sheet18, Sheet19, _
                Sheet20, Sheet21, Sheet22, Sheet23, Sheet24, Sheet25, _
                Sheet26, Sheet27, Sheet29)

    'assuming lbound=0 (ie, not using Option Base 1)
    For i = LBound(arr) To UBound(arr)
        arr(i).Visible = IIf(i < propCount, True, xlVeryHidden)
    Next i

End Sub

Call as:

Private Sub Worksheet_Calculate()
    SetVis clng(Me.Range("propcount").Value
End Sub

Should add some checks on the cell value etc.

Upvotes: 2

Related Questions