This cookbook was written and designed to be used with MS Office 2019 and MS Office 365, installed on either Windows 8, 8.1, or 10.

In the case of MS Word, MS Excel, and MS PowerPoint, the VBA Editor can be activated by using the Alt + F11 keyboard shortcut. By pressing Alt + F11 a second time, you can switch between the Editor and the application you are working in.

Demonstration files can be downloaded from https://github.com/PacktPublishing/VBA-Automation-for-Excel-2019-Cookbook.

Please visit the following link to check out the CiA videos: https://bit.ly/3jQRvVk.

The VBA Editor is the heart and soul of writing your code. In this recipe, we will examine the VBA Editor in detail in order to understand the layout and location of components. There are several components in the Editor, each with a specific function and purpose.

Getting ready

All applications in the MS Office Suite contain the VBA Editor as an integral part of the application. In this recipe, we will be working with Excel. If you do not have Excel installed on your PC or laptop, please install it now.

How to do it…

Let's take a look at the steps for this recipe:

1. To get to the Editor, we first need to open Excel.
2. With the home screen visible, also referred to as the welcome screen, select the first option, Blank workbook:
   

   Figure 2.1 – The welcome screen

   A new Excel spreadsheet named Sheet1 is displayed. Since the file has not been saved, the workbook name, as displayed in the title bar, is Book1 - Excel:

   

   Figure 2.2 – Book1, Sheet1

3. With Sheet1 active, the next step is to open the VBA Editor. This can be done in one of two ways:

   Click on the Developer tab, go to the Code group, and click on the Visual Basic icon.

   Use the keyboard shortcut, Alt + F11.

   Either way, the following screen will appear:

   

   Figure 2.3 – The VBA Editor

4. Since there was no recorded macro, the code window will not be available. Press F7 to open a new code window or, if you want, click View on the menu bar, then select the first option, Code:
   

   Figure 2.4 – The VBA Editor with the code window

5. With the VBA Editor open, we can now identify the different components:

Figure 2.5 – The VBA Editor components

How it works…

Figure 2.5 shows the VBA Editor and its components. Let's look at each component in detail.

The title bar

All applications in the Microsoft environment have a title bar. As the name suggests, it identifies the application you are working in. It also displays the familiar minimize, maximize/restore, and close buttons on the rightmost end:

Figure 2.6 – The title bar

The menu bar

All the latest Microsoft applications make use of ribbons to categorize icons and commands. Menu bars are, therefore, not all that common anymore. However, in the case of the VBA Editor, the menu bar has survived the test of time. It contains all the commands you need to work within the various components of the VBA Editor. Drop-down menus will also show shortcut keys for some of the commands:

Figure 2.7 – The menu bar

The toolbar

The standard toolbar is displayed directly under the menu bar. There are three other toolbars, which can be activated by using the View | Toolbars command, or by right-clicking on the toolbar. All these toolbars can be customized and moved around, although most users leave the toolbars as they are:

Figure 2.8 - The toolbar

The Project window

The Project window displays a tree diagram of the objects currently available in Excel. In this case, only one workbook with one sheet is open in Excel, meaning that only Sheet1 and ThisWorkbook will be displayed under Microsoft Excel Objects. To expand and collapse the objects under Microsoft Excel Objects, you can either double-click on the folder icon or click on the minus sign to the left of the folder icon.

To close the Project window, click on the close button on its title bar, and to open it, press Ctrl + R or View | Project Explorer:

Figure 2.9 – The Project window

The Properties window

The Properties window is displayed directly below the Project window. It does exactly what it says: it displays the properties of objects. When you click on the Sheet1 object in the Project window, the Properties window will display the related properties. The same happens when you select the ThisWorkbook object:

Figure 2.10 – The Properties window

The code window

The code window is the place where the VBA code is stored. Every object in a project has its own code window. To view the code window for ThisWorkbook, double-click on the object. The same applies for the Sheet1 object.

Because we have not saved a macro or typed any code for any of these objects, the code window for both objects will be empty:

Figure 2.11 – The code window

Like all windows, the code window can be restored (and maximized). By restoring both the code windows, you can either tile or cascade them:

Figure 2.12 – Cascading code windows

The Immediate window

When you open the VBA Editor the first time, the Immediate window is normally not visible. Use the View | Immediate Window command, or press Ctrl + G to display the Immediate window. Closing it requires clicking on the close button on the title bar.

If you are new to VBA, the Immediate window will not be of much use to you, since it is used to execute VBA statements directly, as well as for debugging your code. Until you have a bit more experience, feel free to close this window:

Figure 2.13 – The Immediate window

In this recipe, we will be creating a new module, which is yet another object, in the Project window. So far, we've seen only two objects in the Project window: Sheet1 and ThisWorkbook. It is possible to create a code window for each, but Sub procedures must be created and stored in a module in the Project window. This is where coding for all objects can be created and saved.

Getting ready

Make sure that Excel is open and that the VBA Editor is visible.

How to do it…

The steps for this recipe are as follows:

1. With Excel open on a new sheet, select the Developer tab. In the Code group, select the Visual Basic icon to activate the VBA Editor. If you prefer using the shortcut key, simply press Alt + F11.

   In the VBA Editor, observe the Project window. Under Microsoft Excel Objects, only two objects are visible: Sheet1 (Sheet1) and ThisWorkbook.

2. On the menu bar, select Insert. From the drop-down menu, select Module, as shown:

Figure 2.14 – The Insert menu's Module option

Once you've made your selection, the VBA Editor adds a new module – visible in the Project window – and a new code window appears on the right:

Figure 2.15 – Newly inserted module

How it works…

When you open a new spreadsheet in Excel and then activate the VBA Editor, no modules will be visible in the Project window.

However, should you record a macro and then activate the VBA Editor, that macro will be stored in a module.

Finally, if you choose not to record a macro, but to insert the code yourself, you have to create a module yourself.

In this recipe, we will create a basic procedure in the code window of the Editor. We're at the point where everything is coming together now; you know the different components, and you know how to create a module. All that's left is to create code – a simple Sub procedure – in the newly created module.

Getting ready

Make sure that you've created a module in the VBA Editor. The module should be visible as an object in the Project window, and the title bar of the code window should display the name Module1 (Code). If you cannot see the title bar, click on the restore window button to make the window smaller.

How to do it…

Let's go through the steps for this recipe:

1. In the code window, type the following code:

   Sub MyName()
       MsgBox "My name is Yourname" (Replace Yourname _
       with your first name.)
   End Sub

2. Press Alt + F11 to switch to Excel.
3. Click on any cell of the active sheet. Go to Developer | Code, and click on the Macros icon. The Macro dialog box appears:

Figure 2.16 – The Macro dialog box

The only available macro is the one that you've just created; MyName. Click on the Run button.

A message box will appear with the words My name Mike, as shown:

Figure 2.17 – Message box from a basic procedure

A much shorter way to execute the code is to press F5 while in the VBA Editor. You will automatically switch to Excel, where the message box will be displayed.

How it works…

Entering code in the VBA Editor is relatively simple, on the condition that you have a basic understanding of VBA coding. Looking at a few code samples online before going solo is advisable, and will save you many hours of frustration.

As a bonus, the Editor will make adjustments to the text you enter. The first example of this is when you type the Sub statement and the name of your procedure. When you press Enter, the Editor automatically inserts the End Sub statement. As we progress, you will see how spaces are inserted, and how capitalization and text colors are changed.

Once your code is error-free, the code can be run. In this case, the outcome is a message box, displaying the words My name Mike.

Our first Sub procedure was as simple as it gets. It wouldn't take much imagination to understand that coding can become more complex than that. If that's the case, it makes you wonder how long it would take to manually type the coding for super-long Sub procedures.

All depending on the situation, there is more than one option to create coding. In this recipe, we will investigate the different methods to create code.

Getting ready

With Excel open, activate a worksheet in the workbook.

How to do it…

We will now go through the steps of working with each method to create code.

Recording a macro and then opening the VBA Editor to view the code

When using this method, you'll need to perform the following steps:

1. Fill the range A1:A10 with any numerical value. In this case, I filled the range with the value 100. Once done, click on cell A1:
   

   Figure 2.18 – Fill the range A1:A10

2. Navigate to Developer | Code and click on Record Macro. The Record Macro dialog box appears. Give the macro a name and shortcut key, and type a short description, as shown:
   

   Figure 2.19 – The Macro dialog box

3. Click on OK to start recording the macro. The first step is to highlight the range A1:A10. Then, format the range as Accounting by using the drop-down list from Home | Number, as shown:
   

   Figure 2.20 – Formatting the range

4. Once the formatting is done, stop the recording from Developer | Code by clicking on Stop Recording.
5. Now, view the code. Press Alt + F11 to display the VBA Editor. Double-click on Module1 to open the code window:
   

   Figure 2.21 – Code for the recorded macro

6. To test the macro, press Alt + F11 to switch back to Excel. First, clear the formatting on range A1:A10. To clear the formatting, select the range. Then, go to Home | Editing | Clear | Clear formats. Click any cell on the sheet, then run the macro. The range A1:A10 will be formatted as Accounting.

Entering the code manually by typing

In this method, we will be typing the code manually using the following steps:

1. While still in the VBA Editor, click on Insert, then select Module.
2. A new module will appear in the Project window. Double-click on the Module icon to open the corresponding code window.
3. Type the following code:

   Sub MsgBoxOKCancel()
       MsgBox "Do you want to continue?", vbOKCancel
   End Sub

4. To run the code, press F5. The Excel spreadsheet will appear, with a message box on the screen. When you click on OK, you will switch back to the VBA Editor.

Copying and pasting

When you work in MS Word and want to copy text from one document to the other, you need both pages to be open. The same applies for code in VBA.

Open the module with the code you want to copy, as well as the one where you want to paste it. Select the code in the first module – use Ctrl + C, or right-click and Copy – then switch to the new module. Ctrl + V will paste it, or you can right-click and select Paste.

How it works…

Now let's see how each method works.

Recording a macro, and then opening the VBA Editor to view the code

Many Excel users are introduced to the world of VBA coding after recording a macro or two. Looking at the recorded steps and starting to understand how VBA works is quite exciting.

If you have to record a series of steps in Excel, there is no better or faster way to create the code than with a macro.

Entering the code manually by typing

It normally comes as a shock when you realize that not all coding can be done with the macro recorder.

Take, for instance, the message box we created with the sample code. There is no way to create that message box other than manually typing the code.

If you are new to VBA, typing code manually might sound like a challenge. However, with a little bit of experience, you will soon be able to type lines of code like a professional.

Like with any other typing, you can cut, copy, paste, and delete text in the Editor. To make the code easier to read, indent some of the lines with the Tab key. Don't forget that undo and redo can also be used!

If a single line becomes too long to read on one screen, use a space and an underscore (_) to break to a new line.

Copying and pasting

This sounds like the lazy man's escape, but there are many good reasons for copying code. Firstly, if you created a Sub procedure for another project that can be used in your current project, it would be a waste of time to retype it.

You will also find many examples of VBA code on the internet. This is a very good way of learning from people with experience.

There's more…

To make the macro we recorded a bit more useful, do the following:

1. In the VBA Editor, copy the Currency_Format Sub procedure and paste it directly below the last line.
2. Still in the Editor, rename it to Range_Format.
3. Now, delete all the notes in the Range_Format procedure.
4. Finally, delete the line that selects the range A1:A10.

Your code should now look like this:

Figure 2.22 – Altered code

If you switch back to Excel, you can now use the second macro to highlight any range and format it to accounting.

In this recipe, we will change settings in the Editor tab to suit your preferences.

Getting ready

Make sure that the VBA Editor is visible.

How to do it…

In order to proceed with executing this recipe, you need to go through the following steps:

1. With the VBA Editor active, choose Tools | Options from the menu bar. The Options dialog box will appear as shown here:
   

   Figure 2.23 – The Options dialog box

2. In the Options dialog box, click on the Editor tab, if it is not already selected.

   There are two sections: Code Settings and Window Settings. In each of these sections, you will find several boxes, some with tick marks and others empty.

3. Click in any of these tick boxes to select or deselect the option, as described to the right of the tick box:
   

   Figure 2.24 – The Editor tab

4. Click on the Editor Format tab to activate it. With this tab, you can customize the way the Editor looks.

   Here, you will find three sections: Code Colors, Font, and Sample.

   The options under Code Colors can be customized by selecting a different color under Foreground, Background, and Indicator for each of the code elements.

   The font and size can be changed by selecting either Font or Size, while a sample will display the effect of your choices in the Sample section, as shown:

   

   Figure 2.25 – The Editor Format tab

5. Click on the General tab to activate it. This is where the less dedicated settings can be made.

   In this area, you will find four sections: Form Grid Settings, Edit and Continue, Error Trapping, and Compile.

   Once again, changes can be made by either selecting or unselecting tick boxes, as shown:

   

   Figure 2.26 – The General tab

6. Click on the Docking tab to activate it. The only purpose of this tab is to set the behavior of the different windows in the VBA Editor.

   There is only one section with a list of tick boxes, each with a description:

Figure 2.27 – The Docking tab

How it works…

We will now have a quick look at what customizing these tabs entails.

The Editor tab

The default settings are default for good reasons and speak for themselves, in most cases. There are two possible changes I would suggest, which are as follows:

• The Auto Syntax Check: For novice users, this option is indispensable, so keep it as it is. For people with more experience, the pop-up dialog box that appears when it discovers a syntax error in the code can be irritating. You choose what you prefer.
• Default to Full Module View: Many coders prefer to see a maximized code window. However, if you know that you're going to work in multiple modules, it is sometimes nice to have the code window in restore down mode so that you can easily select and move between them.

The Editor Format tab

With this tab, you can customize the way the Editor looks. Once again, these settings are the default because they've been tried and tested.

So, unless you are working for a multi-national company where the project managers have developed their own color settings, I would strongly advise that you stick to the factory settings.

The General tab

In almost every case, the default setting will be best. There is, however, one setting that must be mentioned, and that is Error Trapping. The default setting is Break On Unhandled Errors. If you switch this off, your error handling code will not work. More on this will be covered later in the book.

The Docking tab

As said, these settings determine window behavior in the VBA Editor. When a window is docked, it is fixed in place in the VBA program window, meaning that your environment is fixed and familiar.

If you turn off all docking, windows in the VBA Editor can potentially become a confusing mess of floating screens. In this case, I would strongly advise against undocking windows.