Optimize Redundant Excel VBA Code

Question

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

What is the logic/reasoning that determines which sheets should be visible for each prop count? — Greg
– Greg, Commented Jul 24, 2015 at 20:24
Well basically this is creating rooming lists for various trips. If a trip has 5 properties (visits 5 hotels), then 5 sheets will pop up. Each sheet has it's own rules to autopopulate from stored data (i.e. hotel name, hotel rate, etc.). These rooming lists are then sent to hotels after passengers names have been imported — arbitel
– arbitel, Commented Jul 28, 2015 at 14:33

Tim Williams · Accepted Answer · 2015-07-24 20:31:06Z

2

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.

answered Jul 24, 2015 at 20:31

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MatthewD · Accepted Answer · 2015-07-24 20:45:08Z

1

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

edited Jul 24, 2015 at 20:45

answered Jul 24, 2015 at 20:15

MatthewD

6,8215 gold badges24 silver badges44 bronze badges

4 Comments

arbitel Over a year ago

Thanks Matthew, playing around with this answer. I believe it'll be my best bet

MatthewD Over a year ago

Let me know if you have any questions. Hint you may want to use both. make visible from 14 to rangecount and hide 14 + rangecount to end of sheets.

MatthewD Over a year ago

Made one small change. For iIndex = 14 To 14 + Range("propcount")

arbitel Over a year ago

Thanks Matthew! Made a couple small changes, but this route runs real fast

Bond · Accepted Answer · 2015-07-24 20:36:49Z

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

Roland · Accepted Answer · 2015-07-24 20:50:13Z

1

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

edited Jul 24, 2015 at 20:50

answered Jul 24, 2015 at 20:38

Roland

9768 silver badges20 bronze badges

2 Comments

Roland Over a year ago

Ah, I see Tim uses the IIf as well

arbitel Over a year ago

Thanks for your input. You were indeed rate it was the Worksheet_Calculate that was slowing this down. I was able to use a workaround to trigger a various of MatthewDs code above when a button is clicked. The button doesn't have much to do with sorting, but it needs to be clicked every time the users need to change the # of sheets displayed so it works nicely

Collectives™ on Stack Overflow

Optimize Redundant Excel VBA Code

4 Answers 4

Comments

4 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related