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.
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!
False either ( If z Then )
Nothing or something similar. Instead, it's simply 0 (as is Long, Double, etc.)Dim z as Integer is just a declaration, or in VBA is it more? Or "is it more" for the primitive types whereas if we said Dim z as object then we could say z is uninitialsed ?Nothing, as the pointer is not yet pointing to any meaningful address. This only takes place when you use Set or dim it with the Newkeyword...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.
