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.
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
What we're going to see here will lay the foundation for the other recipes in this book, so pay close attention.
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:
- Open MS Excel and select Blank workbook from the opening screen.
- Open the
01_VBA_Code.xlsmsample file. Click on [Enable Content] on the SECURITY WARNING ribbon.
- If the Developer ribbon is not visible, activate the Backstage View by clicking on File, which will display the following screen:
- From the category list on the left, select the last option, Options. The Excel Options dialog box appears:
- From the categories on the left, select Customize Ribbon.
- To the far right, under the Main Tabs heading, look for the Developer option. Select the checkbox.
- 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:
01_VBA_Code.xlsmopen, click on the Developer tab:
- In the Code group (the first group on the left of the ribbon), select the Macros icon. The Macro dialog box opens:
- 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:
- In the code window (the large area on the right), a short VBA subroutine is displayed.
How it works…
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.
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.
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.
How to do it…
We will now proceed with the steps to record a macro:
- Make sure that
02_RecordMacro.xlsxis 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.
- 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.
- Press the Tab key, or click in the textbox under the Shortcut key heading to the right of Ctrl+:
- While holding down the Shift key, press F on the keyboard.
The shortcut key for this macro is now set to Ctrl + Shift + F.
- Under Store macro in, make sure that This Workbook is selected. Other options will be discussed in later recipes.
- Under Description, enter a short description of what the macro will do. Click on OK to start recording:
- 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:
- 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:
Now, change the format of the selected cells to bold.
- Next, select the range C3 to F8:
Once done, center the cell content and change the format to currency (US$).
- 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…
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.
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.
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.
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:
- Make sure that
02_RecordMacro.xlsxis still open, but this time on Sheet2. To test how effective the Relative Reference setting is, select any cell other than cell A1.
- Let's use the Ctrl + Shift + F keyboard shortcut and see what happens:
The content is now formatted in exactly the same way as in Sheet 1. Isn't that much faster than formatting each area manually?
- Activate Sheet3, because there is yet another way to run a macro. Select any cell on the sheet.
- This time, select the Developer tab and click on the Macros icon in the Code group:
- 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.
- 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.
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.
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:
- The standard way to activate the VBA Editor is to navigate to Developer | Code | Macros, and then select Edit on the Macro dialog box.
- 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:
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:
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.
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:
- In the VBA Editor, double-click on Module1 to open the code window.
- In the code window, select the second line of the executable code. Delete the line of code, as well as the empty line:
- Switch back to Excel, clear all formatting on the data and run the macro. The result is the same as with the first test.
- Next, select all the lines after
.HorizontalAlignment = xlCenter. Press Del:
- 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.
- Finally, change the cell reference in the last line from A1 to G2:
- 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.
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.
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.
02_RecordMacro.xlsx still open, close the VBA Editor.
How to do it…
Now, proceed with the following steps:
- In Excel, click on the Save button. The following message box appears:
- Since we want to save the file together with the newly recorded macro, click No.
- Select the folder where you want to save the file in the Save As dialog box.
- 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):
- Click on Save. In the title bar, the file extension has changed to
How it works…
- The standard
.xlsxfile extension will not allow a macro to be saved with the file.
- Only files with the
.xlsmfile extension will allow you to save a file with an embedded macro.
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.
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:
- Activate the backstage view by clicking on File:
- From the category list on the left, select the last one, Options. The Excel Options dialog box appears:
- Select the last category on the left, Trust Center, and then click on the Trust Center Settings button on the right:
- Select the Macro Settings category on the left. The details will be displayed on the right.
- Click the second radio button from the top, Disable all macros with notification.
- Click on OK to close the Trust Center dialog box.
- In the Excel Options dialog box, click on OK.
How it works…
- 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.
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
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.
Open Excel, with a new sheet active.
How to do it…
- 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:
The tabs are collapsed as shown:
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.
- To add a new tab to the ribbon, right-click on any existing tab. Choose Customize the Ribbon:
- 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:
There are two columns in the Excel Options dialog box. We will be working on the right side, under the Customize the Ribbon heading.
- Make sure that all the main tabs are collapsed. If any of these are expanded, click on - to collapse them.
- Select the Help tab without expanding it, because we want the new tab to appear to the right of the Help tab:
- At the bottom of that column, click on New Tab:
- A new entry will appear on the list – New Tab (Custom) – and under that is New Group (Custom):
- Select the New Tab (Custom) listing, then click on Rename at the bottom:
- In the Rename dialog box, enter an appropriate name for the tab. Click OK:
- Select the New Group (Custom) listing:
- Click on Rename at the bottom, then enter a name for the new group. Click OK:
- In the left column, select Macros from the commands list:
A list of the available macros in this Excel file will appear:
- Select the macro you want to add to the group, then click Add:
- 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:
- 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.