VBA Automation for Excel 2019 Cookbook

By Mike Van Niekerk
  • Instant online access to over 8,000+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Chapter 1: Getting Started with VBA

About this book

Visual Basic for Applications (VBA) is a programming language developed by Microsoft to automate tasks in MS Office applications. This book will help you to focus on the essential aspects of your role by automating mundane tasks in Excel and other Office applications. With comprehensive coverage of VBA delivered in the form of practice problems and bite-sized recipes, this book will help you to hit the ground running.

Unlike most books that assume prior programming experience, this book starts with the fundamentals and gradually progresses to solving bigger problems.

You’ll start by becoming familiar with VBA so that you can start recording macros right away. With this foundation in place, you’ll advance to using the full capabilities of the language as you apply loops, functions, and custom dialog boxes to design your own automation programs. You'll also get to grips with embedded macros and other advanced tools to enhance productivity and explore topics relating to app performance and security. Throughout this VBA book, you’ll cover multiple practice projects in Excel, Word, and PowerPoint while exploring tips and best practices to hone your skills.

By the end of this book, you’ll have developed the skills you need to use VBA to create your own programs that control MS Office applications.

Publication date:
September 2020
Publisher
Packt
Pages
362
ISBN
9781789610031

 

Chapter 1: Getting Started with VBA

The recipes in this chapter will help you familiarize yourself with VBA in general, introduce you to the VBA working environment, and also help you edit and save your first working VBA subroutine. In the process, you will learn how to set up the development environment and how to edit your VBA coding.

In this chapter, we will cover the following recipes:

  • Investigating VBA code
  • Recording a macro
  • Testing the macro
  • Using the VBA Editor
  • Editing the code by changing cell references
  • Saving the file with an embedded macro
  • Using the Trust Center for macro security
  • Creating a customized ribbon

By the end of this chapter, you will be able to identify the need and use for a macro, record a macro, edit VBA code, and save the macro in an Excel Workbook.

 

Technical requirements

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 Developer ribbon can be activated, although this is not a prerequisite. In all the applications, on the View ribbon | Macros group, there are icons for viewing and recording macros.

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.

 

Investigating VBA code

In this recipe, we will inspect a short VBA subroutine. Like all programming languages, VBA has a specific syntax, and the best way to understand the principles is to see what the coding looks like.

What we're going to see here will lay the foundation for the other recipes in this book, so pay close attention.

Getting ready

In order to investigate and edit VBA code in Excel, or any of the other Office applications, we first need to activate the Developer toolbar. Here are the steps:

  1. Open MS Excel and select Blank workbook from the opening screen.
  2. Open the 01_VBA_Code.xlsm sample file. Click on [Enable Content] on the SECURITY WARNING ribbon.
  3. If the Developer ribbon is not visible, activate the Backstage View by clicking on File, which will display the following screen:

    Figure 1.1 – The Backstage View

  4. From the category list on the left, select the last option, Options. The Excel Options dialog box appears:
    Figure 1.2 – The Excel Options dialog box

    Figure 1.2 – The Excel Options dialog box

  5. From the categories on the left, select Customize Ribbon.
  6. To the far right, under the Main Tabs heading, look for the Developer option. Select the checkbox.
  7. Click on OK to accept the change. The dialog box will close, and Excel will now display the Developer tab.

How to do it…

With the Developer tab activated, we will now proceed with the steps for this recipe:

  1. With 01_VBA_Code.xlsm open, click on the Developer tab:
    Figure 1.3 – The Developer tab

    Figure 1.3 – The Developer tab

  2. In the Code group (the first group on the left of the ribbon), select the Macros icon. The Macro dialog box opens:
    Figure 1.4 – The Macro dialog box

    Figure 1.4 – The Macro dialog box

  3. The VBA code we want to investigate is contained in the only macro: Area_Bold. Click on the Edit button. The Microsoft Visual Basic for Applications window will open. Maximize the window, if necessary:
    Figure 1.5 – The Microsoft Visual Basic for Applications editor window

    Figure 1.5 – The Microsoft Visual Basic for Applications editor window

  4. In the code window (the large area on the right), a short VBA subroutine is displayed.

How it works…

The subroutine in the VBA Editor looks like this:

Figure 1.6 – The subroutine in the VBA Editor

Figure 1.6 – The subroutine in the VBA Editor

Let's try to understand this subroutine. Any and all subroutines in VBA start with the Sub keyword, followed by the name of the subroutine (macro), and end with two brackets.

A single apostrophe allows you to enter a note. Comments can be added anywhere in a subroutine and will be displayed in green text, as long as it is on its own line or after a line of code (that is, it cannot be before a line of code as it would obviously comment out the code too). Indented lines without apostrophes are VBA instructions. In this case, the first instruction is when the range B1 to E1 is selected.

The selection's font style is then set to Bold. The last instruction moves the focus to cell A1. Finally, the subroutine is concluded with the End Sub keywords.

There's more…

Whether you record a macro or manually type the coding, you will always find this basic syntax structure in VBA.

In future chapters, we will be working with much longer subroutines. Before we get there, though, we are going to record a macro. With your newly acquired knowledge, you will be able to investigate the VBA code for that too.

 

Recording a macro

Macros are indispensable when we have to do repetitive tasks. In this recipe, we are going to create a macro in Excel. Instead of manually typing several lines of VBA code to create a subroutine, or short program, we can simply record a series of actions in Excel and then store it in the same file. The macro recorder will automatically create the VBA code, as we will see in future recipes.

These recorded steps can then be replayed, in order to execute a series of steps in a fraction of a second.

Getting ready

Like functions, macros make use of absolute and relative referencing. In this first macro, we will use absolute referencing because we have two other sheets with exactly the same layout.

Open the 02_RecordMacro.xlsx file and confirm that Sheet1 is active. Click on the Developer tab. Then, in the Code group, make sure that Use Relative References is not active:

Figure 1.7 – The Relative References option

Figure 1.7 – The Relative References option

How to do it…

We will now proceed with the steps to record a macro:

  1. Make sure that 02_RecordMacro.xlsx is still open on Sheet1, and that cell A1 is selected. In the Code group of the Developer tab, click on Record Macro. The Record Macro dialog box appears.
  2. In the first textbox, under the Macro name heading, type Format_Range. That will be the name of the macro we are going to record.
  3. Press the Tab key, or click in the textbox under the Shortcut key heading to the right of Ctrl+:

    Figure 1.8 – The Shortcut key textbox, Ctrl+

    Figure 1.8 – The Shortcut key textbox, Ctrl+

  4. While holding down the Shift key, press F on the keyboard.

    The shortcut key for this macro is now set to Ctrl + Shift + F.

  5. Under Store macro in, make sure that This Workbook is selected. Other options will be discussed in later recipes.
  6. Under Description, enter a short description of what the macro will do. Click on OK to start recording:
    Figure 1.9 – The Record Macro dialog box with all fields completed

    Figure 1.9 – The Record Macro dialog box with all fields completed

  7. Observe the Code group on the Developer ribbon. The Record Macro icon has been replaced with the Stop Recording icon. This means you are now in recording mode, and all your actions on the keyboard and with the mouse will be recorded:
    Figure 1.10 – Stop Recording is displayed when in recording mode

    Figure 1.10 – Stop Recording is displayed when in recording mode

  8. While in recording mode, click on the Home tab. Then, select the range C1 to F2, hold down Ctrl, and select the range A3 to B7:
    Figure 1.11 – The selected ranges

    Figure 1.11 – The selected ranges

    Now, change the format of the selected cells to bold.

  9. Next, select the range C3 to F8:
    Figure 1.12 – The selected range

    Figure 1.12 – The selected range

    Once done, center the cell content and change the format to currency (US$).

  10. To finish this recording, select cell A1, navigate to the Developer tab | the Code group, and click on Stop Recording.

You have just successfully recorded a macro.

How it works…

Now, this was simple enough, wasn't it? Let's have a look at what just happened.

We set the referencing for the macro to absolute because we want to use the same macro on Sheet2 and Sheet3, too. By starting on cell A1 on Sheet1, we can run the macro from cell A1 on Sheet2 and Sheet3 and get exactly the same results.

Macro names must start with a letter, and there should be no spaces in the name. Furthermore, VBA keywords are not allowed. Format, Copy, and Select are three examples of keywords. Special characters are also taboo.

A keyboard shortcut makes it easy to run a macro, but that's all it is – a shortcut. In the next recipe, we will run a macro from the Macro dialog box.

Storing the macro in This Workbook means it will only work in this specific workbook. To make it available for other workbooks, it must be stored in Personal Macro Workbook. We will find out more about that in future recipes.

Many VBA users leave the Description field empty. This will have no effect on the macro, but it is good practice to explain to other users what you had in mind when saving this macro.

Once you are in recording mode, all your actions are recorded, including errors. It is, therefore, a good idea to makes notes of the steps you want to record.

When you click on Stop Recording, the macro recorder is switched off, and you can relax. Each of your actions has been recorded and converted into VBA code in the background. By opening the Microsoft Visual Basic for Applications editor, you will be able to see the recording code.

There's more…

Once you understand how easy it is to record a series of actions in Excel, you will start looking for reasons to automate all your repetitive tasks.

You can, for instance, record a macro to select the entire sheet and clear it of all formatting. That is much faster than doing so manually, especially if there is more than one sheet.

 

Testing the macro

It's all very well to have recorded a macro, but how do we know whether it's working or not? There is only one way, and that is to test it. Sure, you can have a look at the VBA code in the VBA Editor, but unless you are a seasoned coder, it will be virtually impossible to find coding errors. Best then to do a practical test.

Getting ready

Before moving on, make sure that you have recorded the macro as per the instructions in the previous recipe. We will be using that code in this recipe.

Since we've previously used Sheet1 to record the macro, we will have to test it on Sheet2. Testing on Sheet1 will have no effect because the cells have already been formatted. Sheet2 is still in its original form, meaning we will immediately notice any changes.

How to do it…

Let's proceed with the following steps:

  1. Make sure that 02_RecordMacro.xlsx is still open, but this time on Sheet2. To test how effective the Relative Reference setting is, select any cell other than cell A1.
  2. Let's use the Ctrl + Shift + F keyboard shortcut and see what happens:
    Figure 1.13 – The formatted sheet after running the macro

    Figure 1.13 – The formatted sheet after running the macro

    The content is now formatted in exactly the same way as in Sheet 1. Isn't that much faster than formatting each area manually?

  3. Activate Sheet3, because there is yet another way to run a macro. Select any cell on the sheet.
  4. This time, select the Developer tab and click on the Macros icon in the Code group:
    Figure 1.14 – The Macro dialog box

    Figure 1.14 – The Macro dialog box

  5. The only available macro is the one we recorded, as you can see. To the right, there are several buttons. Click the top one, Run.
  6. As expected, Sheet3 is now also formatted in the same way as Sheet1 and Sheet2.

How it works…

When we first recorded the macro, the Record Macro dialog box gave us the option to select a keyboard shortcut. Clearly, our instructions there have worked.

A slightly longer, yet still effective, way is to run a macro from the Macro dialog box. Since there is only one macro, we can only execute that one. However, if there were more, we could have selected any one before clicking Run.

There's more…

It is good practice to always test a macro, instead of assuming that it will work. It will be exceptional for a recorded macro to fail, but when we get to more advanced subroutines – especially when you start typing your code manually – you should never assume that your coding will be flawless.

So, when everything is working the way it should, it may be a good idea to investigate the actual VBA coding.

 

Using the VBA Editor

We've worked with the VBA Editor before, but that was on another file, with a different Sub procedure. The recipe we're going to work with here will show you how to activate the VBA Editor in your own file. Furthermore, we're going to identify various components within the editor.

Getting ready

With 02_RecordMacro.xlsx still open, we're going to activate the VBA Editor. Whether you are on Sheet1, Sheet2, or Sheet3 is of no consequence, because the macro is part of the workbook.

How to do it…

Let's go through the steps for this recipe:

  1. The standard way to activate the VBA Editor is to navigate to Developer | Code | Macros, and then select Edit on the Macro dialog box.
  2. The shortcut key makes life much easier. Simply hold down the Alt key while pressing F11. This time, you bypass the dialog box by going straight to the VBA Editor:
Figure 1.15 – A Sub procedure in the VBA Editor

Figure 1.15 – A Sub procedure in the VBA Editor

The evidence of what we recorded is displayed in the code window.

How it works…

Opening the VBA Editor can be done in one of two ways: with the Alt + F11 keyboard shortcut, or by clicking on Edit in the Macro dialog box.

In the VBA Editor, you will find the following basic components:

Figure 1.16 – Components of the VBA Editor

Figure 1.16 – Components of the VBA Editor

There's more…

Each element, or component, of the VBA Editor has a specific function. Up until now, we've only used the code window. In future recipes, we will be referring to and using the other elements.

 

Editing the code by changing cell references

This recipe shows you how to edit coding in the VBA Editor. It is more effective to make changes in the code, rather than deleting an entire macro and recording it again. We're going to start by deleting some redundant lines of code, before showing you how to edit code in order to bring about a visible change on the spreadsheet when the code is executed.

Getting ready

With 02_RecordMacro.xlsx still open, use the keyboard shortcut, Alt + F11, to activate the VBA Editor.

How to do it…

For this recipe, the steps are as follows:

  1. In the VBA Editor, double-click on Module1 to open the code window.
  2. In the code window, select the second line of the executable code. Delete the line of code, as well as the empty line:
    Figure 1.17 – Selected code to be deleted

    Figure 1.17 – Selected code to be deleted

  3. Switch back to Excel, clear all formatting on the data and run the macro. The result is the same as with the first test.
  4. Next, select all the lines after .HorizontalAlignment = xlCenter. Press Del:
    Figure 1.18 – Selected code to be deleted

    Figure 1.18 – Selected code to be deleted

  5. Once again, switch back to Excel, clear all formatting on the data, and run the macro. The result is still the same as with the first test.
  6. Finally, change the cell reference in the last line from A1 to G2:
    Figure 1.19 – Cell reference to be changed

    Figure 1.19 – Cell reference to be changed

  7. For the last time, switch back to Excel, clear all formatting on the data in the spreadsheet, and run the macro. The formatting is done as before, but the focus is on cell G2 and not on A1 this time.

How it works…

Let's understand what just happened.

The Range("A3").Activate line can be deleted because the C1:F2, A3:B7 range is already selected. It is that selection that will be formatted to bold.

When the C3:F8 range is selected, we only want it to be centered. The other lines are automatically added but are redundant, and can, therefore, be deleted.

Instead of ending on cell A1, we change the focus to cell G2.

There's more…

The macro has been recorded, tested, and edited, but we have not saved the file yet. As simple as it may sound, there is a trick to it. Refer to the next recipe to take care of that.

 

Saving the file with an embedded macro

This recipe will show you how to save a file with an embedded macro. You might think that saving a file cannot be that difficult, and it isn't. The point is a normal Excel file will not allow you to save the macro you've just recorded, at least not with the normal file extension.

Getting ready

With 02_RecordMacro.xlsx still open, close the VBA Editor.

How to do it…

Now, proceed with the following steps:

  1. In Excel, click on the Save button. The following message box appears:
    Figure 1.20 – Message box warning

    Figure 1.20 – Message box warning

  2. Since we want to save the file together with the newly recorded macro, click No.
  3. Select the folder where you want to save the file in the Save As dialog box.
  4. At the bottom, below the File name textbox, to the right of the Save as type textbox, click on the arrow. From the list of options, select the second one from the top, Excel Macro-Enabled Workbook (*.xlsm):
    Figure 1.21 – Choosing the macro-enabled file option from the Save As dialog box

    Figure 1.21 – Choosing the macro-enabled file option from the Save As dialog box

  5. Click on Save. In the title bar, the file extension has changed to .xlsm:
Figure 1.22 – Altered file extension visible in the title bar

Figure 1.22 – Altered file extension visible in the title bar

How it works…

There are a couple of things to notice:

  • The standard .xlsx file extension will not allow a macro to be saved with the file.
  • Only files with the .xlsm file extension will allow you to save a file with an embedded macro.

There's more…

File extensions allow you to distinguish between files with or without macros.

 

Using the Trust Center for macro security

This recipe is all about security, at least as far as macros are concerned. We will show you where to find the Trust Center in Excel, and after that, how to choose between four levels of macro security.

Getting ready

We've successfully created and tested our macro. We've even made some changes to the code by editing it in the VBA Editor. The final step is to have 02_RecordMacro.xlsm open in order to set the macro security.

How to do it…

Proceed with the following steps for this recipe:

  1. Activate the backstage view by clicking on File:
    Figure 1.23 – The backstage view

    Figure 1.23 – The backstage view

  2. From the category list on the left, select the last one, Options. The Excel Options dialog box appears:
    Figure 1.24 – The Excel Options dialog box

    Figure 1.24 – The Excel Options dialog box

  3. Select the last category on the left, Trust Center, and then click on the Trust Center Settings button on the right:
    Figure 1.25 – Macro Settings in the Trust Center dialog box

    Figure 1.25 – Macro Settings in the Trust Center dialog box

  4. Select the Macro Settings category on the left. The details will be displayed on the right.
  5. Click the second radio button from the top, Disable all macros with notification.
  6. Click on OK to close the Trust Center dialog box.
  7. In the Excel Options dialog box, click on OK.

How it works…

Macro Settings has four levels of security in order to prevent viruses from entering your system. These options are as follows:

  • Disable all macros without notification: Choosing this option will enable all macros. It is safe but defeats the purpose of creating macros.
  • Disable all macros with notification: This is the standard setting, selected by most users. A yellow message bar will appear when you open a file with an embedded macro. If you know and trust the person that sent the file, go ahead and disable the macros. If not, do not enable the macros.
  • Disable all macros except digitally signed macros: Only macros that have a digital signature are allowed to run. However, you will still receive a security warning for signatures that haven't been marked as trusted.
  • Enable all macros: This setting is not recommended because potentially dangerous code can run. Unless you are not connected to the internet, or any other computers in a LAN, never choose this option.

There's more…

The keyword here is vigilance. In this day and age, computer security is vital. A good starting point would be to always have an anti-virus application running on your PC. It is better to be safe than sorry.

 

Creating a customized ribbon

This recipe is optional, though very useful.

There are several ways that you can invoke a macro. After activating the Developer ribbon, it is relatively easy to click on the Developer tab, and then to choose the Macros button in the Code group. But what if you don't want to do it that way? Shortcuts may help, but only up to a point because macros have a way of proliferating quickly.

This recipe will show you how to create a new ribbon, group, and icons.

Getting ready

Open Excel, with a new sheet active.

How to do it…

  1. Before starting with customization, there is an important yet simple way to manipulate the ribbon in Excel. This is not really a customization, but it is still important to know how to control the interface. If you double-click any of the tabs on the ribbon, the ribbon will collapse. By double-clicking the tab again, the ribbon will expand, showing the standard, full view:
    Figure 1.26 – The Developer ribbon in the expanded view

    Figure 1.26 – The Developer ribbon in the expanded view

    The tabs are collapsed as shown:

    Figure 1.27 – Collapsed ribbons

    Figure 1.27 – Collapsed ribbons

    The standard tabs normally provide all the functionality you need. However, if you have a series of functions you want to use regularly, or even a series of macros, it will help a lot if you could display the shortcuts to those items on a ribbon.

  2. To add a new tab to the ribbon, right-click on any existing tab. Choose Customize the Ribbon:
    Figure 1.28 – Right-clicking on the Help tab

    Figure 1.28 – Right-clicking on the Help tab

  3. You could also right-click anywhere to the right of an existing ribbon, in the gray area, and choose Customize the Ribbon, or you can click on File | Options | Customize Ribbon.

    The Excel Options dialog box will appear:

    Figure 1.29 – The Excel Options dialog box

    Figure 1.29 – The Excel Options dialog box

    There are two columns in the Excel Options dialog box. We will be working on the right side, under the Customize the Ribbon heading.

  4. Make sure that all the main tabs are collapsed. If any of these are expanded, click on - to collapse them.
  5. Select the Help tab without expanding it, because we want the new tab to appear to the right of the Help tab:
    Figure 1.30 – The Help tab selected

    Figure 1.30 – The Help tab selected

  6. At the bottom of that column, click on New Tab:
    Figure 1.31 – The Help tab selected

    Figure 1.31 – The Help tab selected

  7. A new entry will appear on the list – New Tab (Custom) – and under that is New Group (Custom):
    Figure 1.32 – The New Tab option inserted

    Figure 1.32 – The New Tab option inserted

  8. Select the New Tab (Custom) listing, then click on Rename at the bottom:
    Figure 1.32 – The New Tab option inserted

    Figure 1.33 – Renaming the new tab

  9. In the Rename dialog box, enter an appropriate name for the tab. Click OK:
    Figure 1.34 – Typing a new name

    Figure 1.34 – Typing a new name

  10. Select the New Group (Custom) listing:
    Figure 1.35 – Selecting the new group

    Figure 1.35 – Selecting the new group

  11. Click on Rename at the bottom, then enter a name for the new group. Click OK:
    Figure 1.36 – The Rename dialog box for groups

    Figure 1.36 – The Rename dialog box for groups

  12. In the left column, select Macros from the commands list:
    Figure 1.37 – Selecting Macros

    Figure 1.37 – Selecting Macros

    A list of the available macros in this Excel file will appear:

    Figure 1.38 – List of recorded macros

    Figure 1.38 – List of recorded macros

  13. Select the macro you want to add to the group, then click Add:
    Figure 1.39 – Click Add to add a macro to the group

    Figure 1.39 – Click Add to add a macro to the group

  14. Select the newly inserted icon and click on Rename. Type a more descriptive name for the macro, and choose a fitting icon. Click OK, and OK again:
    Figure 1.40 – Name and icon selection

    Figure 1.40 – Name and icon selection

    You will have a new tab with a new group, and one icon in the group:

    Figure 1.41 – The new ribbon with the new group

    Figure 1.41 – The new ribbon with the new group

  15. Click the new icon to run the macro associated with it. You can insert more macros to the same group by repeating the steps from Step 12 on this list.

This recipe should enable you to create an easy access point for all recorder macros in a workbook.

About the Author

  • Mike Van Niekerk

    Mike has consulted as an IT Lecturer since 1995, mainly presenting Project Management and Microsoft Office end-user application courses. With thirty-nine years of applied experience in many industries, including language courses and writing of manuals, he has acquired an exceptional ability of transferring knowledge. He is a Microsoft Office Specialist and Microsoft Certified Trainer. He is highly experienced in MS Word, Excel, PowerPoint, Access, MS Projects and VBA. He also works with and train on Outlook, Publisher and Visio. He has trained all these applications on all levels, from beginners to advanced. He has written MS Office training manuals for Torque-IT in the early 2000’s, as well as for The Skills Gap, Rosebank Business College and many other clients. Unfortunately, none of it can be shared because of copyright infringements. Furthermore, manuals of this nature are never published electronically.

    Browse publications by this author
Book Title
Access this book, plus 8,000 other titles for FREE
Access now