Program by Levels

Tutorial on VBA for Excel (Macros)

bringing you the simplest and most efficient ways
to do things in VBA for Excel

VBA Lesson 2-3: VBA for Excel for the Application
Levels: Intermediate and Advanced)

Application
Application is a VBA object, IT IS EXCEL. In alphabetical order here are the 10 VBA words that you need to learn to work efficiently with the Application:

Application, Calculation, Cursor, CutCopyMode, Dialogs, DisplayAlerts, EnableEvents, GoTo, Quit, ScreenUpdating, Timer

In this lesson you will also discover: ChDir and ChDrive

Basic Vocabulary

Quit
The following line of code closes Excel altogether.
Application.Quit

CutCopyMode
After each Copy/Paste or Copy/PasteSpecial operation, you should empty the clipboard with the following line of code to make sure that the computer memory doesn't overload.
ActiveSheet.Paste
Application.CutCopyMode=False

DisplayAlerts
When you don't want Excel to ask you things like "A file already exists....." or "Do you wan to save this file..." you will use the following line of code at the beginning of your VBA procedure.
Application.DisplayAlerts = False
Then at the end
Application.DisplayAlerts = True

ScreenUpdating
When you don't want to see your screen follow the actions of your VBA procedure, you start and end your code with the following sentences:
Application.ScreenUpdating = False
Application.ScreenUpdating = True

In the middle of a VBA procedure you might want to show a single updated screen to help the user be patient while the procedure is finishing its job. You will write after a significant line of code:
Application.ScreenUpdating = True
and immediately after:
Application.ScreenUpdating = False

Calculation
When you are working with a workbook in which there are a lot of formulas and you want to temporarily deactivate the calculations you will use:
Application.Calculation = xlManual (Don't forget the xl  before the Manual)
MAKE SURE that at the end of the procedure you add this line of code:
Application.Calculation = xlAutomatic
If during the execution of the procedure you want  a single sheet to be calculated you will use:
Activesheet.Calculate

Dialogs
When the workbook that the user has to open may vary from time to time you will not use the Workbooks().Open sentence as shown in lesson 12-4. To show any of the dialog windows in Excel you will use the following code (example):
Application.Dialogs(xlDialogOpen).Show
Notice that when you key in this sentence, as soon as you open the parenthesis VBE offers you the list of the 244 dialog windows of Excel. Among the basic ones are: xlDialogOpen, xlDialogSave, xlDialogSaveAs

See the advanced section below for more on xlDialogSaveAs

When you want the dialog window to point at a specific drive or directory you will use the ChDrive and ChDir methods before calling the dialog window:
ChDrive "D:/"
ChDir "D:/Snagit"
Application.Dialogs(xlDialogOpen).Show

GoTo
In the tutorial on Excel that you have downloaded from www.excel-examples.com you have discovered how to use the hyperlinks in Excel to navigate around the workbook and open any kind of files from it. You don't like the fact that when you use the hyperlinks to go to a certain cell in the workbook the selected cell is not in the top/left corner of the screen. To correct this situation you will need to resort to the GoTo method:
Application.Goto Reference:=Range("V300"), Scroll=True

Cursor
There are 4 cursor that you can choose from (xlDefault, xlWait, xlBeam and xlNorthWestArrow) and to make your selection you will write:
Application.Cursor=
and the Editor will offer you the choice between them.

Advanced Vocabulary and Ideas

Timer
Timer is a stand alone thing that will tell you how many seconds your procedure takes to complete its work. I use it often when I work at improving execution time for a procedure. The code to determine the time elapsed between the beginning and the end of a procedure will be like the following. To test the following VBA procedure enter XX in cell D1000 and run the procedure. Then remove the XX in cell D1000 and enter it in cell D10000. See the time difference going down 1,000 cells and 10,000 cells. The procedure stores the time in a variable named varTimeStart at the beginning of the procedure  varTimeStart = Timer and then enters in cell A1 the difference between the time at the end and the time stored in the variable Range("A1").Value = (Timer - varTimeStart) & " seconds".

Sub proTimer()
Dim varTimeStart As Double

       varTimeStart = Timer
       Range("D1").Select

       Do
       Selection.Offset(1, 0).Select
       Loop Until Selection.Value = "XX"
       Range("A1").Value = (Timer - varTimeStart) & " seconds"

       Range("A1").Select

End Sub

When execution time or calculation time is of the utmost importance I end up using the variable of the VARIANT type that you will discover in lesson 2-11 and in the workbook  "vba-example-variant.xls"

Dialogs
If you want your user to be forced to save his file in a certain format you will use the arg2 argument. In the example below the value "3" is for Text Delimited Format. The Value "1" can be omitted as it stands for Excel format which is the format by default and the value "6" stands for CSV File.
Application.Dialogs(xlDialogSaveAs).Show arg2:=3

EnableEvents
In some workbooks you might have VBA procedures triggered by events related to the sheet (Change, Calculate, Activate...) and you might not want these event to be executed while a certain other event is running. To prevent such executions you will use:
Application.EnableEvents = False
at the beginning of the procedure and:
Application.EnableEvents = True
at the end.

ChDrive and ChDir
If you don't want to hard code the drive and directory you can stor the drive and directory in a cell and use:
ChDrive Sheets("Intro").Range("A32").Value
ChDir Sheets("Intro").Range("A33").Value
or sore the values in a variable and use:
ChDrive varDrive
ChDir varDirectory

This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple