Excel Macros

In this module, we will learn about VBA, projects, modules, objects, properties, and methods. We will also learn about the modules folder and the VBA comments.

Once you have recorded a macro, you may need to edit it. For example, suppose that you created a macro that applies the bold text effect to all text in row 1 of a worksheet.

If you wanted to also apply the italic text effect to this same row, you could modify the existing macro to complete this task rather than create a new macro that would have to be run separately.

Over the course of this module, you will learn how to edit existing macros. You will also learn about the various components that make up a macro.

In the end, there will be also an activity on how to edit a macro.

To edit an existing macro, open the Macro dialog box by clicking View → Macros. With the Macro dialog box now open, click to select the macro that you would like to edit and then click the Edit button. The Visual Basic Editor will open with the contents of the module that you need to edit displayed in the Code window.

For the example in this lecture, we will change the line of code containing, the Selection.Font.Bold = True line to Selection.Font.Bold = False. In other words, the macro will now remove the bold text effect (False), instead of applying it (True):

Besides the Code window, you will also see in the VBA Editor the next components:

• Projects in VBA are sets of relationships that exist between all of the parts of an open Excel workbook. You can examine all of these parts using the Project Explorer component of the Visual Basic Editor.

• An object in VBA is essentially a self-contained component (such as a worksheet) that contains properties and methods that describe what the object is and how it behaves. They can best be thought of as the building blocks that can be put together to create a greater application (or macro in this case).

• The Modules folder is where modules are saved in the current project. You can find the Modules folder listed inside the Project Explorer.

• Modules in VBA are the part of a project that contains the code for a macro or function. When opened, these files will display the code in the Code window where you can edit it as you see fit. Remember, modules are used to store code for both macros that were created by recording and by being manually coded.

• Properties are used to describe an object. For example, if you were to describe a worksheet object, it would include properties like a name, type, and more. Here are sample properties for a workbook.

• Methods describe how an object behaves and what it can do. A worksheet object, for example, would include copy and paste methods. However, typically methods are added as functions that work with the properties of an object to achieve a goal.

• VBA Comments can be thought of as notes that are added to the code. These notes can contain whatever you like, such as reminders and notices to other users who may modify this code. Comments appear as green text inside the VBA workspace and are preceded by an apostrophe (‘). When an apostrophe has been placed at the start of a line, anything on that line will become a comment and not recognized by VBA as code, so it will be ignored. This means that if you would like to disable a line of code, but not remove it, you can comment it out. Just place an apostrophe at the beginning of the line of that code (Note that when you create a comment, it will not change to green until you move your cursor off of that line).

In this lecture, we will work on the example that we saw in Module 1 in which we recorded a macro to format a date range as a table. We already mentioned in Module 1, that if we were to use this macro on a different date range or on a table with a different name, we are going to run into the problems.

The main issue in the recorded macro was that we were referencing a table with a specific name, which was Table 1. This means that if we would like to use the same macro with the table named Table 5, the macro execution will result in an error. In this lecture, we will see how we could correct the code directly in the VBA editor.

In this lecture, we will see the difference between absolute and relative references when recording a macro.

Absolute recording mode:

• Excel records actual cell references

• For example, if we would select the cell B8 when recording a macro, each time when executing this macro, the cell B8 would be selected

Relative recording mode:

• Excel records relative references to cells, as an offset from the active cell, would be recorded

• For example, if our currently active cell would be A1 and we would select then the cell A2 when recording a macro an offset would be recorded. Later on, if we would execute the same macro while standing in the cell D8, the macro execution would result in selecting cell D9.

Edit previously recorded macro

You need to modify the macro that you created previously so that when it runs, it formats the header row to have bold formatting and font size of 14px instead of 12px.

You also need to set the column width to a fixed standard width of 17px.

In this lecture, you will find the video showing the solution on how to complete Assignment 2.

In this module, we will be working with different objects, that will be binned to a specific macro.

We will see how we can write simple macro codes that can make the work with Excel much easier and how those codes can be bind to various objects.

Additionally, we‘ll see also how we can create forms for user input, as well as how we can store the user input into a specific cell.

In this lecture, we will see how we can insert a shape in Excel and then connect it to a specific macro, so that every time when we click on this shape, the same macro will be executed.

We will use an invoice template, to which we will insert a shape and name it Print to PDF. To this shape, we will assign an existing macro that prints the active sheet to pdf. When done, every time when user will click on this shape, a new pdf file will be generated.

In this lecture, we will continue with the previous invoice template example, to which we will add an image. Then we will assign a macro to this image that will increment the invoice number and clear the contents of the invoice template. We will show to write such macro code directly in the VBA Editor.

In this lecture, we are going to create a user form, by which we will use a dialog box for adding the service from the drop-down menu, then adding purchase order number, contact person, and contact number with help of text boxes in the dialog box.

In order to create a user form, we will insert a User Form in the VBA. Here we will add the desired fields, their codes and when done we are going to assign the code for displaying user form to a specific object.

Create a Course application form

In this activity, you will need to create a Course application form and save your file as a PDF file. To begin, please open the attached Activity which contains already the course registration template. You will need to add to this template appropriate buttons, shapes, or icons as well as macro codes

1. The first task is to create a PDF button, add macro code that will print the excel file to pdf, and assign that macro to the pdf button.

2. The second task is to create a shape or icon that will display on click user form as we can see here on the right side.

3. User form should have the next functionality:

• The course should be selected from the dropdown menu

• Type should be selected by radio buttons

• The first and last name should be entered into the text box

• With a click on the Submit button, all entered content should be stored in marked cells on the Excel sheet

In this lecture, you will find the video showing the solution on how to complete Assignment 3.

In this module, we will learn how to use debugging tools in VBA Editor. After you have created or edited a macro, you must ensure that it works as intended. Even the best programmers make simple errors, and the key to solving these errors is debugging.

Debugging process:

• The debugging process is a sequence of steps that you can use to find and fix errors in your code.

• The first step is to ensure that the macro code in the workbook is open Visual Basic Editor and that the workbook in which the macro is used is also open.

• Next, it's usually a good idea to back up your code and workbook in case they get lost during the debugging process.

• The third step is to actually execute the macro in question.

• Next, you need to examine the results of the macro and determine what the problem is. Using the debugging tools in the Visual Basic Editor, you can then run the macro code line by line to find exactly the line where the error occurs.

• Finally, you make the suggested corrections and then test the macro again to make sure it works correctly.

You can find the Debug toolbar in VBA Editor under tab View -> Toolbar -> Debug.

The commands on Debug toolbar:

• The first command on Debug toolbar allows you to enter and exit Design Mode.

• The Start, Stop, and Reset buttons are used to control the execution of a macro. With these controls, you can execute the macro line-by-line to determine exactly where the error exists.

• The Toggle Breakpoint command is used to insert breaks into your code. These breaks are used to prevent the code from being executed from the point in which they are inserted and onwards. This allows you to test only parts of a macro at a time.

• The Step tools allow you to select different lines of code and “step in” or “step out” of them.

• The Windows buttons allow you to toggle the Locals, Immediate and Watch windows on or off.

• The Quick Watch will show the value of an expression.

• The final command shows current active procedure calls (7).

As we can see, one of the key steps to debug a macro is to run it using the VBA Editor.

To run a macro in the VB Editor, select the module in which the macro code is Stored. Next, click the Run Macro button on the Standard toolbar. (You can also press F5 to run this command.) Or, click the Run Macro button on the Debug toolbar:

Use the debugging tools in the VBA Editor to find and fix the errors

A macro that you have been working on is not operating correctly. You need to use the debugging tools in the Visual Basic Editor to find and fix the errors.

In this lecture, you will find the video showing the solution on how to complete Assignment 4.

in this module, we will talk about customizing the Quick Access toolbar, hotkeys, and Ribbon.

Once a macro has been created, you can create shortcuts to it so that you can run it more quickly and easily than if you were to use the Macro dialog box each time.

Excel allows you to assign macros to commands on the Quick Access toolbar, as well as assign hotkeys to them so that they can be executed with just a keyboard shortcut. You can even add a custom tab on the Ribbon, containing the shortcuts to your macros.

During this module, you will learn how to add buttons to the Quick Access Toolbar to run a macro, assign keyboard shortcuts to a macro and create a custom Tab on the Ribbon.

In this lecture, we will see how we can add recorded macros to a Quick Access toolbar.

In the final lecture of this module, we will check how we can customize our Excel Ribbon so that it would contain the shortcuts to our recorded macros.

In the presented example, we will have 6 macros that we are constantly using. Now we don’t want to memorize each hotkey or to navigate to the Developer tab and run those macros from the Macro dialog box. It would be nice if we could have all macros structured by groups and displayed on the ribbon. This is something we can do very easily in Excel and we will see how.

Customizing the Quick Access Toolbar and Hotkeys

Imagine that you have completed a macro and you are going to be using it frequently, so you would like to add it to the Quick Access toolbar.

There is another macro that you would also like to bind to a keyboard shortcut for even quicker access.

Your tasks:

1. Add Macro with name Format_Titles to Quick Access Toolbar

2. Add a shortcut key Ctrl + Shift + C to the macro with the name Clear_Title_Formatting

In this lecture, you will find the video showing the solution on how to complete the Assignment 5.

Now in this final module, we will use gained knowledge to finish the last activities of this course. Do try to finish these activities on your own and compare them to the solution provided at the end of the activity.

When done, you will be given also a Quiz to test gained knowledge and at the end, I will give additional lesson labs, so that you will be able to practice some more on your own.

Assignment Instructions

In this activity, you will need to record, edit and debug a macro.

Additionally, you will need to write VBA code for the macro that will generate a print preview and assign that macro to a shape.

For each macro, you will need to create either a shortcut key or add the macro to Quick Access Toolbar or to the Ribbon.

Detailed instructions for this activity:

1. In the first step, you need to Use Macro Recorder to create a new macro labeled „TitleFormatting“ that will apply the italic effect to cell A1, as well as increase its font size to 30 points.

2. Then after you have finished recording the macro, you need to open it in the VBA Editor and add code to the format title also to Bold formatting (Selection.Font.Bold = True).

3. In this workbook, you will find another macro, with the name „FillWeek“ which should fill down the values in range A5:A14. Maybe you will need to use a debug toolbar to find the error and fix it. (Hint: Check if.FlashFill property is the right one to be used in this case).

4. Then for the fourth task, write the code which will generate the Print Preview of the worksheet. You can use the PrintPreview method that doesn‘t have any arguments. Then assign that Macro to the shape.

5. In the last task for this activity, Add macro named TitleFormatting to the Quick Access Toolbar, to the macro named PrintPreview add a shortcut key and you also need to add the macro named FillWeek, to the Ribbon.

In this lecture, you will find the video showing the solution on how to complete Task 1.

In this Activity, we will be searching for the way out of Labyrinth. You will need to record 5 macros and assign them to specific shapes.

1. Insert 5 shapes, which will be used as controls:

• One for moving to the left,

• The second for moving to the right,

• The third for moving up and

• The fourth for moving down.

• For the cheating macro, you will also use a macro recorder to set the active position in the cell where the finish line is. But be sure to record this one without relative references usage activated, because, with the execution of this macro, the same cell – the one representing the finish – should always be selected.

2. For the last task, you will find already an existing code in the Module 1 folder. This code is used to display a dialog box with a warning when a user hits the wall of the labyrinth and to return the user back to the start.

In order for this code to work, you will need to define in Name Manager the range with name Path, which should refer to all cells and ranges on which the user is allowed to move. (Hint: Name Manager can be located under the Formulas tab)

In this lecture, you will find the video showing the solution on how to complete Task 2.

In this Activity, you will find three buttons on the Annual Overview sheet. You will see that those buttons don’t have any macros associated with them. Each button should be associated with a macro that should perform a specific action.

1. For the first button, named „Show on the chart“ you need to record a macro that will generate a column chart at the fixed position, starting in cell D4, as visible on the picture below.

2. For the second button, named „Average Inventory“, write a macro that will display the average inventory amount in the dialog box. Meaning that every time when the user will click on this button, the average amount of the inventory should be displayed in a dialog box.

3. For the button, named „Print Jan-Dec“ on pdf, write a macro that will print all sheets January to December to a pdf file. They should be printed into a single pdf file.

In this lecture, you will find the video showing the solution on how to complete Task 3.