Auto Zoom in VBA

We Excel users can often be pretty technologically minded. We like multiple monitors, or big wide screens.

Sometimes we create a superb workbook and we decide to share it with someone else. The problem is, though, that sometimes their monitor does not display the page as well as we would like it to.

There is a neat trick to automatically set the zoom to the appropriate size based on the screen size.
------------------------------------

The first step is to create our workbook. Make it whatever size you think looks nice on your screen. If you think it looks best full screen, make it full screen.

Next, you need to find out what the window size is for your workbook. In the immediate window, type the following:

?application.height

?application.width

Make note of the numbers that result from it. For our example, let's say we got the results, 600 height 400 width.

Next we create a new VBA module and place the following code in it. After the code has been inserted, you must change the variables "orig_Height" and "orig_Width" with the numbers we got in the previous step. In our case this is 600 and 400 respectively.

Public Sub autoZoom()
    'Checks the size of the application and scales it
    'according to the size of the original workbook
    
    'Resume next on error because
    'this subroutine will be run
    'at various times and we don't
    'want it to interrupt the use
    'of our workbook
    On Error Resume Next
    
    Dim orig_Height
    Dim orig_Width
    Dim cur_Height
    Dim cur_Width
    Dim diff_height As Single
    Dim diff_width As Single
    
    'change this to the proper height
    'and width at 100% Zoom
    orig_Height = 600
    orig_Width = 400
    
    'These are set based on the current information
    cur_Height = Application.Windows.Item(ThisWorkbook.Name).Height
    cur_Width = Application.Windows.Item(ThisWorkbook.Name).Width
    
    'this checks to make sure we are using the
    'current workbook. This can be changed if
    'you want, but it's helpful so that we don't
    'scale the wrong workbook
    If ActiveWorkbook.Name = ThisWorkbook.Name Then
        'this is where we automatically change the zoom
        'We have to base it on either height or width
        'So we run a quick calculation to see which one
        'to use
        diff_height = Abs(orig_Height - cur_Height)
        diff_width = Abs(orig_Width - cur_Width)
        
        'if the height difference is less than the width
        'difference, then we use the height to scale the
        'window. Otherwise, we use the width
        If diff_height < diff_width Then
            'set the zoom
            ActiveWindow.Zoom = (cur_Height / orig_Height) * 100
        Else
            'set the zoom
            ActiveWindow.Zoom = (cur_Width / orig_Width) * 100
        End If
    End If
End Sub

Now you need to navigate to the workbook module and create a new event workbook_open (or whatever other event you wish to link this to) Then you simply type autoZoom wherever you want the code to automatically set the zoom.

Viola!
--------------------------------
This code works by creating a ratio of your current window size in comparison to the window size that looks good on the original monitor. It multiplies this ratio by 100 to set the zoom to the proper number.