Excel VBATutorial                      Tutorial on Excel macros

 

Excel VBA Macros

Excel Macros Table of Contents

Excel Visual Basic Editor

Excel Macros Vocabulary

Excel Userforms

Excel Consulting

Excel Tutorial on Macros

Email to excel-vba.com

Here is a sample of what you will find in lesson 13 of the downloadable Tutorial on Excel macros

Lesson 13 on Excel macros (VBA):

VBA Code for the Application

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 is a word that you can use with Application that will neutralise all the alerts that Excel can send your way. Discover this word and many others that you can use in combination with Application in the downloadable course on Excel macros.


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


We hope you have enjoyed this introduction to lesson 13
for more on this topic and a complete course on Excel macros download the
Tutorial on Excel Macros


Next Lesson: VBA Code for Workbooks

 

Excel Tutorial on Macros

Excel VBA Macros

Excel Macros Table of Contents

Excel Visual Basic Editor

Excel Macros Vocabulary

Excel Userforms

Excel Consulting