Managing macros in Excel 365

This is a guide on managing macros in Excel 365.

 

In Excel, you can use the Visual Basic Editor to create and manage your macros. In this course, see how to insert procedures and write code using the Visual Basic Editor. Easily insert objects and browse the object library using the VBA Editor, so you can create macros that work with specific workbooks, sheets, or cell ranges. Execute actions under conditions by learning how to insert procedures with conditions using If, Then and Else to return specific values. Rename spreadsheets and create reminder prompts by creating event procedures. And finally, manage and correct your macro errors by learning about the three types of errors that can occur.

 

From Excel 365 Visual Basic Editor, you can get a complete view of your macros and their components. These components include comments, sub procedure, and executable statements. In this video, you'll explore how to identify macro components in Excel. To view the macros in this workbook, switch to the Developer tab and on the left within the Code group, click the Visual Basic button. This opens the Microsoft Visual Basic for Applications window.

 

At the top left, in the Project pane, you'll find a folder labeled Modules. Below this folder, you may have multiple modules listed. Your modules are where your macros are stored. Once you select a module, on the right you'll be able to view your macros in the viewing window. If you have more than one macro stored in a module, you'll be able to quickly switch views by going down to the left of the window and select the Procedure View button. This will now help you focus on an individual module.

 

In order to understand your macro, you'll need to understand its key components. In the code, at the beginning of the macro, I can see that it starts with "Sub", which indicates a sub procedure, followed by the macro's name and then an open and close parentheses. At the bottom of your macro code, you'll find "End Sub" which indicates where the sub procedure ends. If any comments have been added to your macro, you'll be able to easily identify the comments as they will appear in green. As you can see, near the top of my macro code is a line of green text, which is a comment. Comments are useful as it gives information about the macro and its actions.

 

Below any comments, you'll find the executable statements. Executable statements is the code after the comments between Sub and End Sub. To directly enter into these statements, press the F8 key on your keyboard. Once you select F8, a line of code is then highlighted in yellow and you can continue switching between the lines of code by pressing the F8 key again.

 

Excel 365 macros can be edited with Visual Basic. In the VBA Editor, macros are contained within modules. You can add modules, rename modules, and even remove modules. In this video, you'll discover how to work with modules in Excel. I currently have the Visual Basic Editor window open. From here, there are multiple ways to add a module. One way is to go to the top left, and in the menu bar, click on Insert.

 

From the menu, select the option Module. Alternatively, you can go to the far left of the menu bar of tools, click the Insert UserForm dropdown and select the option Module. The final way to add a module is to right-click in the project pane at the top left, hover over the option Insert, and from the side menu select Module. As you can now see, below the Modules folder in the Project pane, three new modules have been added.

 

It's important to note that when using the macro recording tool, macros created in the same session will be stored in the same module. Macros created in different sessions after opening and closing a workbook will be stored in different modules. Once you've added your modules, you can then rename them. To do this, select a module in the Project pane. Then, in the Properties pane at the bottom left, switch to the Categorized tab.

 

A Name field then appears in this pane. You can click inside this field, delete the current text, and then type in a name for your module. Once you've named your module, you can then use this name to start adding code to create a sub procedure. Now, if you'd like to remove a module, you have a few different options. First, you can select the module in the Project pane that you'd like to remove. Go to the top left of the menu bar and click on File, and from the menu select Remove, followed by the module's name.

 

A notification will then appear where you can choose to export the module before removing it by clicking the Yes button, or you can choose to not export it by clicking the No button. I'll select No, and the module has been removed from the Project pane. You can also remove a module by right-clicking on it and from the menu select the Remove option. Again, the same dialog box as seen before will appear. Again I'll click the No button, and in the Project pane, that module has also been removed.

 

Modules in Excel 365 are used to store procedures. In the VBA Editor, you can manually insert procedures or use the Insert tool. These procedures can be public or private. In this video, you'll learn how to insert procedures in Excel. Modules are used to store procedures, and procedures are blocks of statements used to execute actions. You can create different procedures within a module to organize complex code into various groups of tasks.

 

In the Project pane of the VBA Editor window, I've selected Module2. Once you've opened up your module, the most common procedure is a sub routine, or sub procedure, to tell Excel to perform a task. In order to compose this procedure, I'm going to click in the viewing window on the right and type in "Sub". Next, insert a space and then type in the module name, making sure there are no blank spaces within the name. Insert another space and then an open and close parentheses. Once you finished, press the Enter key on your keyboard. Now below, the "End" statement declaration has been automatically added and executable actions can now also be added.

 

Another way to add a procedure is to use the Insert menu. To do this, I'm going to first move the cursor after "End Sub" and press the Enter key a few times. Once I have the space to insert my procedure, go to the top left of the menu bar, click on Insert, and from the menu select Procedure. And Add Procedure window opens. At the top is a Name field where I'll type in a name for my procedure. Below, you need to choose the type of procedure. You have the option Sub which performs actions, the Function procedure which performs actions before returning a value, and lastly, Property procedures. I'll select Function.

 

Then below, you can choose to make your scope public, which gives free access from other modules; or make it private, which only gives access to procedures in the same module. I'll select Private and then click the OK button at the top right. Now you can see when additional procedures are inserted, a line is added between the procedures already in your module. Since I've inserted a procedure that is private, I'll see Private before the procedure, which is a function, and then the name of the procedure. [Video description begins] The line of code is: Private Function AddTotals(). [Video description ends]

 

If you'd like to move one procedure to another module, you can easily do this by using the cut tools. First, select all of the procedure text. Then you can go to the top left of the menu bar and click on the Cut tool. Note that you can also use the keyboard shortcut Ctrl+X to perform the cut function. After you've cut your procedure, navigate to the module where you want to move the procedure to. Then into the viewing window, you can either paste it using the Paste tool found in the menu bar, or use the keyboard shortcut Ctrl+V. As you can see, this is an easy way to move your procedures from one module to another.

 

With Visual Basic in Excel, you can write your own code from scratch. While writing code, you can add properties, methods and comments. You can also edit your code formatting by using the Edit tool. In this video, you'll explore how to write code with Visual Basic in Excel. In the viewing window, I already have the procedure inserted. Within this procedure, I'd like to add a comment.

 

A comment will inform others what your macro does. To insert a comment, you'll need to insert your comment between single quotation marks. After you've done this, press the Enter key. When you do this, you'll see that the comment turns green, informing others that this is a comment and not an executable action. You can also convert existing text to a comment.

 

To do this, I'll first add some text, then I'll need to select that text, go up to the menu bar, right-click on an empty space, and from the menu select Edit. An Edit toolbar then appears on the left of the window. I'll quickly move the Edit toolbar down a bit. In the center of this toolbar, you'll find the Comment Block tool. Select this, and if I click away to remove the text selection, I can see that this text has been added as a comment block.

 

To remove a comment, first select the text, and in the center of the Edit toolbar click the Uncomment Block button. This will remove the comment formatting from the text. I'll quickly remove this text by selecting it and pressing Delete. Next, I'm going to insert code to add additional worksheets to my workbook. To do this, I'm going to need to insert properties and methods. To insert a property application, type in the text "App". And then to access the properties, use the keyboard shortcut Ctrl and space bar.

 

A dropdown menu then appears below the text where I have now access to my properties. From here, I'll double click the property I want to insert, which is Application. To insert another property, I'll first need to insert a period, and then the text "Caption". When I start typing, the properties menu reappears and from here I'll double click on Caption. Next, insert an equal symbol, followed by the text "Excel Office 365" in quotation marks. Then press Enter.

 

Now to insert a new worksheet, I need to insert additional properties and methods. To do this, right-click on the viewing window and select the option List Property/Methods. A list then appears. I can now start typing the name of the property I'd like to add. Once I see it in the list, select it to insert it. Type in a period, then in the list select the Add option for the method. If you want to access quick information about your method, right-click on it and select the option Quick Info.

 

A yellow text box then displays, giving you the information needed to add your additional method. So I'll now insert a space, the text "After", followed by a colon and an equal symbol. Next, I need to insert ActiveWorksheet, so I'll press Ctrl and space bar to reopen the list and select ActiveWorksheet. To then run the macro, press the F5 key. Now, in the Project pane, I can see that a new worksheet has been added.

 

To organize your macro code, you can insert indents. To add indentation, click before your property or method text and then press the Tab key. This will move your code slightly to the right, keeping it more organized. You can also do this by selecting before your property, and in the center of the Edit toolbar, click the Indent button. You can also outdent by selecting the Outdent button to the right, or you can use the keyboard shortcut Shift+Tab to outdent. As you can see, the editing toolbar is quite useful. You can customize this toolbar by right-clicking over it and select the option Customize.

 

A Customize window then opens. At the top, switch to the Commands tab. From the Categories list on the left, select Edit. And on the right, you'll see a list of the commands currently added to the toolbar. To remove or rearrange the tools, select the tool and click on Rearrange Commands below. The Rearrange Commands window then opens, and on the right you can choose to add, delete or move specific tools. To leave this window, click the Close button at the bottom right, and close the Customize window by clicking the X icon at the top right. This has been an overview on writing code

 

When creating macros in Excel, you'll need to know how to declare and assign variables. You'll also need to insert constants to save time when writing code in Visual Basic. In this video, you'll discover how to use variables and constraints in Excel. I have the Visual Basic Editor window currently open, and in the viewing window on the right, I already have my procedure inserted. Here, I'd like to insert variables. Variables are useful as they store values that can be used in a macro. To insert a variable, I'll first press the Tab key to add an indent.

 

Next, I'll type in the text "Dim", which is the most common statement to declare a variable; then type in the name of the variable, which is "Discount", the text "As", and then the type of variable, which is an integer. When I start typing "Integer", a list of options then appears. From here, I'll double click on Integer to select it. I'll then press the Enter key to move to another line in order to add an additional variable. Following the same process, this time I'm going to insert my variable as "Dim Status As String".

 

Now, after I've declared both of my variables, I need to assign a value to them. First, I'm going to move the cursor down a few lines and press the Tab key to add an indent. Then I'll type in the text "Discount", an equal sign and the number 10. So the discount variable now has a value of 10. I'll press the Enter key to move to the next line, and next I'll assign a value for my string variable. So type in "Status", an equal sign, and then within quotation marks the text "Apply discount".

 

You can also store values that will not be changed during the macro execution by inserting a constant. To insert a constant value to show who approves the discount, type in "Const" followed by "Approvedby As String", then insert an equal sign followed by quotation marks and then the string that I'd like to appear. I want to show that Julia Campbell approves the discount, so I'll type in that name and then close the quotation marks. To then run this macro, press the F5 key on your keyboard.

 

Visual Basic in Excel 365 works with an object module program. You can easily insert an object, browse the object library, and create object collections. In this video, you'll learn how to use objects in Excel. Since Visual Basic works with an object module program, you'll notice that your objects are organized by hierarchy. If I'd like to customize the name of the application window, I need to insert an object application followed by Caption and then the name of the application I'd like to appear. Below, I already have my sub procedure added.

 

To manually insert an object, I'll first move to the line below, press the Tab key to add an intent, and then start typing "Application". I can then use the keyboard shortcut Ctrl and spacebar to open the list of properties, and here I'll double click on Application. Insert a period, and the same list of properties automatically opens again. I'll start typing my next item, which is Caption, then select it from the list. I'd like the caption to appear as "Excel for Office 365". To do this, insert an equal sign, then within quotation marks type in "Excel for Office 365".

 

Another way you can insert an object is by using the object browser library. First, I'll move to a new line by pressing Enter. To access the object browser, go to the right side of the menu bar of tools where you'll find the Object Browser button. Alternatively, at the top left, you can click on View and from the menu select the option Object Browser. Note that you can also use the keyboard shortcut F2 to access it.

 

Once clicked, the Object Browser interface opens on the right of the window. To choose a library to view, go to the top left of this interface, click the dropdown, and from the menu select Excel. Once you have your library open, you'll notice it's organized into two different sections: Classes and Members. For example, if I want to insert an object that will add in a worksheet, I'll go to the Classes section on the left and select Application. Once the class has been selected, you then need to choose your member.

 

As the member list on the right is quite long, I can easily search for a specific item by going to the top left and, for example, typing in "Worksheets" into the search bar. When I press Enter, a Search Results section according to your library class and member appears below. Once you've found the class and member you're looking for, select it. Then at the top center, click the Copy to clipboard option. Once you've copied the member, return to your module by selecting it from the Project pane at the top left. To then paste the copied member, I'll use the keyboard shortcut Ctrl+V.

 

Once Worksheets appears in the viewing window, insert a period and the properties list opens. From here, I'll select Add. Insert a space and type in "After" and a colon. Then I'll choose to apply this to the active worksheet by inserting an equal sign followed by "ActiveSheet". To run this macro, press the F5 key. And now, in the Project pane, and new worksheet called 'Sheet2' has been added.

 

You can also use object collections to rename your worksheets. So to do this, I'm going to add a new procedure called Sub Renaming with an open and close parenthesis, then press Enter. Move down a fewlines, then I'll press the Tab key, type in "Dim", then I'll declare my variable so type in "SheetNum As Integer". Then from the properties list, select Integer.

 

To then create my collection of objects, I'm going to use For and Next to declare my procedures. Insert "For SheetNum' and then the integer will be "= 1". Now I'll quickly add the rest of my phrase, which is "To ActiveWorkbook.Worksheet.CountActiveWorkbook", and below I have also added my collection for this particular object. I'll press Enter and then Backspace, then insert "Next" followed by "SheetNum". I can now run this macro by pressing the F5 key, and you'll now see in the Project pane that my worksheets have been automatically renamed and numbered.

 

You can insert operators into your Excel macros to perform calculations. It's also possible to insert comparison and logical operators to perform calculations if certain criteria are met. In this video, you'll explore how to use operators in Excel. As you can see, I have a module open with the macro that I've started. Within this macro I've named it, and below I've declared my variables and the variable value. [Video description begins] The first line of code is: Dim a As Integer, b As Integer, c as Integer. The second and third lines of code are: a = 10, and b = 15. [Video description ends] You can use these items to perform calculations with operators.

 

For example, if I'd like to add the values for a and b, I can easily do this by using the + symbol. So I'll type in "C = A + B". You can also insert comparison and logical operators into your macro. First, I'll press Enter to move down a few lines. I want to set a condition to perform a calculation with certain values to show results within a message box. For example, if the A value is less than or greater than 20, or if the B value is equal to 10, then I want a certain message to appear. To do this, I'm going to have to define my comparison and logical operators.

 

First, type in "If" then "A". Insert a less than symbol followed by an equal sign and the value 20. Then, to also define the B value, type in "Or", the letter B, an equal sign, and the value 10. So if any of these values are true, I want a specific message to appear. To do this, first type in "Then" and press Enter to move to a new line. To insert a message box, type in "MsgBox", and to specify what message I want to appear, insert an open parenthesis, then within quotation marks type in your message. To include the value of the calculation above for C, type in a & and the letter C. Close off that parenthesis.

 

Next, I'm going to add another logical condition. So if these values are not met, I want another message box to appear. So I'll press Enter again, type in "Else", and press the Enter key once more. To insert another message box, type in MsgBox followed by an open parenthesis, the message within quotation marks, and then close the parentheses. I don't need to have an If at the end, so I'll put "End" and "If".

 

Now, once I've inserted my comparison and logical operators along with my arithmetic operator, I can then test it. To run this macro, press the F5 key. The VBA Editor window closes, and in the main Excel interface, a message has appeared stating that the result is 25. As you can see, since the values were true, I have now the result and the values. To return to the VBA Editor window, click the OK button.

 

With Excel, you can insert procedures with conditions using If, Then and Else to return specific values. It also gives you the possibility to work with cases by inserting the CaseValue procedure. In this video, you'll discover how to execute actions under conditions in Excel.

 

In the viewing window, I have already started my macro and I've also declared the variable, as well as the value of that specific variable. [Video description begins] The first line of code is: Dim purchase As Integer. The second line of code is: purchase = 100. [Video description ends] To create a macro to evaluate if a purchase will get a discount or not, I can create a conditional procedure.

 

To create my procedure, I'll be using If, Then and Else. Since I've already defined my value, I'll first type in "If", and this will be the condition for my procedure. If the purchase is greater than 500, then I'm going to give it a discount. So type in "purchase", a < symbol and 500. To give it a discount, type in "Then".

 

Press Enter to move down a line where I'll insert a message box by typing in "MsgBox" and an open parenthesis. So if the condition is met, I want to give it a 20% discount. Within quotation marks, type in the message "20% discount", then close off the parentheses. Since I have already used Then, which is an instruction, I'm also going to use Else to connect the next part of my condition. The next condition will be if the purchase is greater than 100, I want to give a 10% discount. So typing "If purchase > 100 Then".

 

Move to a new line and insert a message box with the text MsgBox, an open parenthesis, within quotation marks type in 10% discount, then close the parentheses. To connect the next part of my condition, I'll type in Else again. And if none of this criteria is met, I want the message "No discount" to appear. Insert a message box, an open parenthesis, "No discount" in quotation marks, and close the parentheses. After I've inserted the conditions, type in "End If" to end the conditions. And I'm also going to end the entire procedure again with "End If".

 

Now, after I've entered these values, if I'd like to test the macro, I can use the keyboard shortcut F5. I'm then navigated to the Excel workbook, where a message displays stating "No discount". Click the OK button to return to the VBA Editor. If, for example, at the top I change the value of purchase from 100 to 600 and then press the F5 key, in the Excel workbook, the message now states that I have a 20% discount. As you can see, this is useful if you want to evaluate purchase totals and get a quick response. I'll click the OK button to return to my macro.

 

Besides using conditions, you can also use cases. I'll go up to the top right of the viewing window, click the dropdown and select my CaseValues macro to display it. I can now see that it differs slightly from the condition procedure. [Video description begins] First line is: Dim purchase As Integer. Second line is: purchase = 1. 3rd line is: Select Case purchase. 4th line is: Case Is >= 500. 5th line is: MsgBox ("20% discount"). 6th line is: Case Is >= 100. 7th line is: MsgBox ("10% discount"). 8th line is: Case Is < 100. 9th line is: MsgBox ("no discount"). 10th line is: End Select. [Video description ends]

 

I have my value, which is my purchase, and my variable declared as an Integer. And I can also see that I have Case purchase, and I can go through and specify if the case is greater than or equal to the values, and even include a message box. So if I run this macro by pressing F5, in the Excel message box, it states that I get no discount for these particular cases. Click OK to return to the macro. Now, if I go up and change my purchase value to, for example 600, then press F5 again, the message box now states that I get a 20% discount. Click OK to return.

 

To compare both of my macros, I'll go to the bottom left corner of the viewing window and click the Full Module View button. I can now see both macros, and I can compare and remove any additional lines I don't need. There are both quite similar, yet a bit different. You can choose whichever format you prefer, but as you can see, the case format is a bit shorter if you're looking to execute actions according to a defined expression.

 

Event procedures in Excel enable you to interact with objects. You can rename spreadsheets and create reminders within the Excel application. In this video, you'll learn how to create events in Excel. Within Visual Basic, you can create events for objects such as applications, workbooks and worksheets. For example, if I'd like to create an event to request a name for a new worksheet, first within the Project pane at the top left, I'll double click on this workbook.

 

Into the window on the right, I'm going to insert an object and a procedure. To do this, go to the top left, click the Object dropdown and select Workbook, and displayed below is now the procedure Open. Next, I want to insert the procedure NewSheet, so I go up to the top right, click the Procedure dropdown and select NewSheet. I'll now quickly remove the procedure Open.

 

Now that I have the procedure and objects, I'm going to need to insert my variables. To do this, insert "Dim SheetName As String". I want an input box to appear when I add a new worksheet. To do this, insert "SheetName = InputBox". Type in an open parenthesis followed by quotation marks, and then the text that will appear in the box. Close the quotation marks and parentheses, and next I want this to be applied to the active worksheet. So I'm going to push "ActiveSheet.Name = SheetName". Next, I want to move the worksheets in order after the sheet count, so I'm going to insert "ActiveSheet.Move After : = Sheets(Sheets.Count)".

 

Besides entering an input box, I'd also like to add a couple of reminders. First, I'd like to remind users before closing the application to save their changes. To do this, first be sure that the object selected at the top left is Workbook. Then go up to the top right, click the Procedure dropdown and select BeforeClose. Next, I'll insert a message box, so type in "MsgBox", an open parenthesis, followed by quotation marks, the text that I'd like to appear in the message box, then close the quotation marks and parentheses. [Video description begins] She adds the message "Don't forget to save your changes". [Video description ends]

 

You can also add an additional reminder after saving. For example, after the document has been saved, I want a message box to appear with the text "Now you can close your document." To do this, again be sure that the object Workbook is selected. Click the Procedure dropdown and select AfterSave. Insert the message box by typing in "MsgBox", an open parenthesis, the message within quotation marks, and close the parentheses. [Video description begins] She adds the message "Now you can close your document". [Video description ends]

 

Now that I've created my events, if I'd like to test those events, I need to return to my workbook. Go to the bottom of the window, and in the taskbar click the Excel icon. With my workbook open, if I go to the bottom left and click the New Sheet button, and input box then appears with the field where I can name the new worksheet I'm creating. So I'll type in the name "Sales" and then click the OK button at the top right. And now at the bottom left, I can see that the new worksheet I created has now been called "Sales'.

 

If now, for example, I close this document by clicking the X icon at the top right, a message box appears with the text "Don't forget to save your changes". Click the OK button to close this, and another Excel notification appears asking if I want to save the changes made. If I click the Cancel button and I go up to the top left-hand corner and click the Save icon, once my documents have been saved, a message box appears with the text "Now you can close your document." Click the OK button to close this.

 

When using macros in Excel, there are three types of errors that can occur: syntax, runtime, and logic errors. It's important to know how to identify and correct macro errors. In this video, you'll explore how to manage errors in Excel. In order to identify syntax errors, I have a module opened up. If you're going to create a macro and you decide not to name it, for example I'm just going to type in "Sub" and press Enter, a syntax error message box then appears with the message that I have a compile error, which means I need to add the name for this particular macro. I'll click the OK button to close this.

 

If I now type in a name and an open and close parenthesis, then press Enter, I can see that it automatically adds "End Sub". Now you might also notice that you have syntax errors when entering variables. For example, I'm going to declare two variables, a and b. First, I'm going to insert "Dim a As". Now, if I don't declare this variable as an integer and just press Enter, another syntax or compile error appears. This means I need to declare the variable. Close this by clicking OK, then I'll define a as an Integer, insert a comma, and I'm also going to declare b as an integer.

 

Below, I'll declare the value for a, so type in "a = 10", and I'm also going to include a message box that will appear with a, so press Enter, insert "MsgBox", an open parenthesis, a piece of text within quotation marks, then close the parentheses. And to include the value for a, insert "& a". To run this macro, press the F5 key, and in the Excel workbook, a message box displays with the text "Today is 10". Click the OK button to return to the macro.

 

Other types of errors are ones that are detected after running a macro. For example, I'm going to change the value of "a" to the text "Date" instead of the number 10. If I press F5, an error message appears indicating that there's a runtime error. To find out what the error is, click the Debug button. Within the macro, the step that is causing the issue is now highlighted in yellow. Since I changed the variable from an integer to text, this is causing a runtime error.

 

To correct this, I'll turn this variable back into an integer, since I declared it as an integer. Now, to declare my next variable b, I'll type in "b", an equal sign, and put in the value "Hi" within quotation marks, and then a message box "MsgBox", and in parentheses the value "b". If I press F5, the message box for the first string will display. Click OK to close this, and an error message then displays with the runtime mismatch error.

 

If I click the Debug button again, the step causing the error is highlighted in yellow. [Video description begins] This is the step: b = "Hi". [Video description ends] Again, since this is not an integer, it's going to be flagged as an error each time, so I'll change the b value to 5. Now, if I press F5, a message box then displays with the value 5, since that's what I declared in the message box. Click OK to close this.

 

Another way to manage your errors is to add "On Error" to a line of code in order to keep track of your errors as you go. You can add this to the end of a string. Type in "On Error GoTo", and this will go to a specific point in your macro. I'll quickly delete the text "GoTo". You have also the option to insert "Resume Next" if you'd like to skip particular errors and come back to them later. So if you know that you've inserted text instead of an integer, and you'd like to skip that and come back to it later, you can insert this particular string and you'll be able to go through the rest of your macros to check for any errors.