CODEWITHSUNDEEP
vbaexcel
whytheq
whytheq

Reputation: 35557

Applying Select Case

Current code is as follows:

For Each s In myReport.Sheets
    If s.Name = "Data" Then
        s.Activate
        Excel.ActiveWindow.Zoom = 80
    End If
    If Left(s.Name, 7) = "Charts_" Then
        s.Activate
        Excel.ActiveWindow.Zoom = 77
    End If
    If s.Name = "definitions" Then
        s.Activate
        Excel.ActiveWindow.Zoom = 75
    End If
    If s.Name = "Summary" Then
        s.Activate
        Excel.ActiveWindow.Zoom = 71
    End If
Next

Could this be made more efficient using a Select Case? I'm unsure how to apply VBA version of select case in this context.

Upvotes: 1

Views: 330

Answers (2)

Peter Albert
Peter Albert

Reputation: 17475

Here's my attempt using Select Case - though I'd prefer KazJaw's If/ElseIf, as it is even more flexible.

Dim z as Integer

For Each s in myReport.Sheets
    Select Case UCase(s.Name)
       Case "DATA": z = 80
       Case "DEFINITIONS": z = 75
       Case "SUMMARY": z = 71
       Case Else:
           If Left(s.Name, 7) = "Charts_" Then 
               z = 77
           Else
               z = 0
           End If
    End Select
    If z Then
        s.Activate
        ActiveWindow.Zoom = z
    End If
Next s

Alternatively, you can create a very flexible Select Case statement with the following trick:

For Each s In ThisWorkbook.Sheets
    Select Case True
       Case s.Name = "Data":             z = 80
       Case Left(s.Name, 7) = "Charts_": z = 77
       Case s.Name = "Defintions":       z = 75
       Case s.Name = "Summary":          z = 71           
       Case Else:                        z = 0
    End Select
    If z Then
        s.Activate
        ActiveWindow.Zoom = z
    End If
Next s

As you can see from the two examples, the first Select Case allows you to execute a central code for each comparison (e.g. the UCase, which is a good practise anyway), while the second gives you full flexibility - but is in the end nothing else then an If/ElseIf/... statement!

Upvotes: 4

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

This one would be more efficient:

For Each s In myReport.Sheets
    If s.Name = "Data" Then
        s.Activate
        Excel.ActiveWindow.Zoom = 80

    ElseIf Left(s.Name, 7) = "Charts_" Then
        s.Activate
        Excel.ActiveWindow.Zoom = 77

    ElseIf s.Name = "definitions" Then
        s.Activate
        Excel.ActiveWindow.Zoom = 75

    ElseIf s.Name = "Summary" Then
        s.Activate
        Excel.ActiveWindow.Zoom = 71
    End If
Next

In your code If statements are always checked four times. In my code they will be checked as long as the condition is not meet. Possibly only once, sometimes four time, sometimes in between. Select Case will not be good in your situation.

Upvotes: 2

Related Questions