‘Zoom to selection’ with VBA to improve presentation of Excel financial models

It is not always easy to ensure that a financial model presents well on all screen types and resolution settings. I notice frequently while working on spreadsheets on four different platforms

• Office desktop (3 x 19 inch screens)
• Laptop 13.3 inch laptop
• Blackberry (2 inch screen)
• Home desktop (24 inch widescreen)

There is not one simple way of making sure that your financial model looks great on all platforms but one trick that often helps is the ‘zoom to selection’ functionality. This Excel functionality gives the user the possibility to zoom in on a selected range to fill the screen which is quite useful for disclaimers and cover sheets of financial models. Clearly not even this trick can improve the presentation on the blackberry, but I am sure you can live with that.

Manual ‘Zoom to Selection’ in Excel

To understand the functionality, select a range in Excel (say A1:N44) and then click ‘Selection’ in the Zoom-menu.

Zoom to selection in Excel - dropdown

Excel will then set the zoom-level to whatever is necessary to make the specified range fill the screen.

Automated ‘Zoom to Selection’ using VBA in Excel

The manual Zoom to Selection has limited value as it will only set the zoom level to be right just for your computer and since your clients and colleagues may have different settings they would have to redo it.

By using VBA code this process can be automated and it is quite simple.

Sub ZoomToSelectionDemo()

    'Select the range you want to zoom to
    Range("A1:N44").Select

    'Zoom to the selected range
    ActiveWindow.Zoom = True

    'Select range A1
    Range("A1").Select

End Sub

VBA code for zoom to selection

To see a working example of this, feel free to download one of our Excel Tutorials and have a look at the VBA code in the Disclaimer section.

COMMENTS