VBA Lesson 13: VBA for Excel for the Application

This is an excerpt from Pierre Leclerc (www.excel-vba.com)

Application is a VBA object, IT IS EXCEL. For example: Application.Quit will close Excel all together.

Exercise 1a

Step 1: Open a new workbook in Excel and use the ALT/F11 keys to go to the visual basic editor (VBE).

Step 2: Copy the following macro in the code window of any sheet. As you can read, you are asking Excel to close itself.

Sub testLesson13a1()

    Application.Quit

End Sub

Step 3: As you have learned in lesson 7, go to Excel and run the macro from the menu bar (Excel before 2007) or the ribbon (Excel since 2007).

Step 4: You will be asked if you want to save the workbook. Answer "No" and Excel will close itself.

Exercise 1b

If you do not want to be bothered by the alert to save your workbook you will add a line of code to the small macro: ActiveWorkbook.Saved = True

Step 1: Open a new workbook in Excel and use the ALT/F11 keys to go to the visual basic editor (VBE).

Step 2: Copy the following macro in the code window of any sheet. As you can read, you are asking Excel to close itself but saying first that the workbook has already been saved.

Sub testLesson13a1()

    ActiveWorkbook.Saved = True
    Application.Quit

End Sub

Step 3: Run the macro from Excel as you did with the previous one.

Excel will just close itself without asking you anything.

There are many other words that you can use in combination with Application. Among them, two important words are:

ScreenUpdating (Application.ScreenUpdating)
When you do not want to see your screen follow the actions of your VBA procedure (macro), you start and end your code with the following sentences:
Application.ScreenUpdating = False
Then at the end:
Application.ScreenUpdating = True

Exercise

Step 1: Open a new workbook in Excel and use the ALT/F11 keys to go to the visual basic editor (VBE).

Step 2: Copy the following macro in the code window of any sheet. As you can read: starting in cell A1 a value of "99" will be entered in the selected cell then the cursor will move one cell down to enter "99", repeat the process until the row number of the selected cell is 3000 and come back to cell A1.

Sub testLesson13b1()

    Range("A1").Select

    Do Until Selection.Row = 3000
        Selection.Value = 99
        Selection.Offset(1, 0).Select
    Loop

    Range("A1").Select

End Sub

Step 3: Run the macro from Excel as you did with the previous one.

Step 4: Remove all the "99" from the cells

Step 5: Copy the following macro in the code window of a  new workbook and run it. Two lines of code have been added to the previous macro to prevent all the steps of the action to be seen on the screen.

Sub testLesson13b2()

    Application.ScreenUpdating = False

    Range("A1").Select

    Do Until Selection.Row = 3000
        Selection.Value = 99
        Selection.Offset(1, 0).Select
    Loop

    Range("A1").Select

    Application.ScreenUpdating = True

End Sub

Step 6: Run the macro from Excel as you did with the previous one. You will see a blank sheet, no movement whatsoever and then a sheet where cells A1 to A3000 are equal to "99".

Sometimes you or the users might want to see the action. Some other times you or the user do not want to see the action. It is up to you to use the sentence or not.

You can even use the pair of sentences (as below) anywhere within a long macro to refresh the screen at significant points in the process. With the pair of sentences you call for a refreshment with Application.ScreenUpdating = True  and then interrupt the refreshment process until the next refreshment with Application.ScreenUpdating = False. Before the end of the macro you will use a final Application.ScreenUpdating = True.

The pair of refreshing sentences:
Application.ScreenUpdating = True
Application.ScreenUpdating = False

Step 7: Close the workbook without saving anything

 Go to the next lesson
Lesson 14 : VBA for Excel for Workbooks