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.

Optimize Your VBA Code

Have you ever created a VBA module only to find that the subroutines and functions therein were incredibly slow?

There are a few things you want to look for in order to speed up your code.  Using a simply google search, you can find information about more advanced things like Storing data in a variant array (don't loop through cells!). However, I'm going to show you a few simpler techniques that I use when coding in VBA.

The first thing I do is use optimizing code. I have written two very simple subroutines which I call at the beginning and end of my code respectively.

First:

 Sub Optimize ()  
   'Run this before the slow code runs  
   Application.ScreenUpdating = False  
   Application.Calculation = xlCalculationManual  
   Application.DisplayAlerts = False  
   Application.AskToUpdateLinks = False  
 End Sub  

and then:

 Sub deOptimize()  
   'Stops Optimization, run before exiting sub or function  
   '(after code runs)  
   Application.ScreenUpdating = True  
   Application.Calculation = xlCalculationAutomatic  
   Application.DisplayAlerts = True  
   Application.AskToUpdateLinks = True  
 End Sub  

The important thing to note here is that you MUST run the deOptimize sub after  you are done. Otherwise, the user will run into some very annoying problems.

Another thing I do is look for points where my code calls for something else to happen. That could be running additional subroutines or functions, or calls to the windows API.

When you run code that is process intensive, Excel has a tendency to hang. A user might think that Excel is unresponsive and choose to end it.  Sometimes while Excel is hanging, it is also trying to complete another task in addition to your code.  (This can happen during long loops, for example.)

The solution is simple, but you must be careful with how often you use it. It is a simple line:

 DoEvents  

This will pause your code for a brief moment while the computer uses its processing power to process other things it is doing.  This will then open up more processing power for your code and can speed up certain processes. When used excessively, however, it can have the opposite effect and slow it down.

There have been big speed problems when it comes to Pivot Tables and Camera objects. If you need to use these things, you may not have a good way of speeding things up. Make sure you aren't calculating your Pivot Tables each time the smallest update occurs. Camera objects have no mercy, and it is better to avoid them and go with better "Dashboard" tools instead.