Useful Keywords to Know in Excel Visual Basic

1.        The Application Keyword

Use “Application” to refer to Excel, e.g.:

Application.ActivePrinter Name of the Active Printer.
Application.Name            Name of the program running (“Microsoft Excel”).
Application.Path            The path from which the program is running.

2.        ActiveControl

Instead of the name of a specific control (e.g. txtEdit), you can use the keyword ActiveControl.  This will let your program refer to whichever control the user selected last.

3.        MousePointer

The MousePointer keyword lets you change the pointer to an hourglass.  You could do this while a long operation is happening.  The user can still click on enabled controls with the modified pointer, but most people won’t.  When the operation finishes, remember to change the pointer back to zero (the default).

UserForm.MousePointer or Control.MousePointer = 11 (hourglass) or 0 (default).

4.        DoEvents

You can use the statement DoEvents at any stage in your code.  This allows pending transactions, such as a screen redraw, a button click, or an Alt+Tab to another application, to happen while your code is running.  It is good practice to do this during long-running code. That way the user sees something happening and does not think the program has hung.  It also makes it easier for them to swap to another application and do something else while waiting.

5.        ScreenUpdating

In contrast to DoEvents, you can speed up a busy macro by turning off screen updating.  If there is a lot of screen activity that the user does not need to see, at the start of the macro, use the line:

  Application.ScreenUpdating = False

When you want to show an updated screen again at a suitable point, use

  Application.ScreenUpdating = True
  DoEvents

That will make sure that the user sees the refreshed screen.

Newer versions of Excel seem to be less inclined to update the screen all the time.  You might want to use the above lines at key points so that the user can see some activity.

6.        Calculate

Another way to speed up a macro is to defer recalculation. By default, Excel recalculates all affected cells whenever something changes.  You set this in File > Options > Formulas (tab) > Calculation Options > Automatic.  This is wise.  If Manual calculation is on, you cannot trust that the figures in the spreadsheet are correct!

But, in some cases, you might want to make many changes that affect many cells, and only want the results at the end. In this situation, you could save a lot of unnecessary, time-consuming recalculation. Use VBA code to set Calculation to Manual. At key points, if necessary, you could force recalculation.  Recalculate either the entire workbook or single worksheets, as follows:

Application.Calculation = xlManual        Turn Off Automatic Calculation
Application.Calculate                   Recalculate the whole workbook (press F9).
Worksheets(1).Calculate               Recalculate the formulas in a specific worksheet.
Application.Calculation = xlAutomatic    Turn Automatic Calculations On again

Finally, it is important that you set Calculation back to Automatic with the last line above.