Excel Macros Tutorial: How to Record and Create Your Own Excel Macros
※ Download: Macro excel tutorial
To organize your discovery of Excel macros, the is divided in three sections all 3 sections part of the single download : This section is about recording, writing, modifying and testing macros in the Visual Basic Editor. Adopt the same attitude as you have with documents attached to Emails. Even non-programmers can easily add impressive functionality to their spreadsheets with Virtual Basics for Applications VBA.
You do not want your user to go to the Visual Basic Editor to trigger a macro. To be more exact, the toolbar contains items such as on-screen buttons, icons, menus and similar elements. The Properties Window is the section of the VBE that you use to edit the properties of anything you may have selected in the Project Window.
VBA - Excel Macros - We will open it and start by setting it up so that working within it becomes easy and efficient. As you may imagine, this macro does not work very well if, when using it, you are in any cell other than A3.
Do you track what proportion of the time you spend working on Excel goes away in small and relatively unimportant, but repetitive, tasks? If you have and perhaps even if you haven't , you have probably noticed that routine stuff such as formatting or inserting standard text usually take up a significant amount of time. In most not all cases, investing much time on these common but repetitive operations doesn't yield proportional results. They're part of the majority of efforts that has little impact on the output. If you are reading this Excel macro Tutorial for Beginners, however, you're probably already aware of how macros are one of Excel's most powerful features and how they can help you automate repetitive tasks. As a consequence of this, you're probably searching for a basic guide for beginners that explains, in an easy-to-follow way, how to create macros. Macros are an advanced topic and, if you want to become an advanced programmer, you will encounter complex materials. This is why some training resources on this topic are sometimes difficult to follow. However, this does not mean that the process to set-up a macro in Excel is impossible to learn. In fact, in this Excel Macro Tutorial for Beginners, I explain how you can start creating basic macros now in 7 easy steps. In addition to taking you step-by-step through the process of setting up a macro, this guide includes a step-by-step example. Although I truly believe that I am building one of the best online resources for learning how to use Excel. However, if this text really bothers you, please feel free to change the text for whatever you want. This Excel Macro Tutorial for Beginners is accompanied by an Excel workbook containing the data and macros I use including the macro I describe above. You can get immediate free access to this example workbook by subscribing to the Power Spreadsheets Newsletter. The 7 steps that I explain below are enough to set you on your way to producing basic Excel macros. You can use the following table of contents to skip to any section. In Excel, most of the useful commands when working with Excel macros and Visual Basic for Applications are in the Developer tab. The Developer tab is, by default, hidden by Excel. Therefore, unless you or somebody else has added the Developer tab to the Ribbon, you have to make Excel show it in order to have access to the appropriate tools when setting-up a macro. In this section, I explain how you can add the Developer tab to the Ribbon. At the end of the step-by-step explanation, there's an image showing the whole process. Note that you only need to ask Excel to display the Developer tab once. Assuming the set up is not reversed later, Excel continues to display the tab in future opportunities. Step 1: Using the mouse, right-click on the Ribbon. Step 2: Excel displays a context menu. Step 1: Click on the File Ribbon Tab. Once you are in the Excel Options dialog, ensure that you are on the tab by clicking on this tab on the navigation bar located on the left side. In this case, the Developer tab is not be shown in the Ribbon. If this box has a check mark, the Developer tab appears in the Ribbon. If box already has a checkmark, you don't need to do anything you should already have the Developer tab in the Ribbon. Click on the OK button at the lower right corner of the Excel Options dialog. Excel takes you back to the worksheet you were working on and the Developer tab appears in the Ribbon. The second option which requires programming is more complex than the first, particularly if you are a newcomer to the world of macros and you have no programming experience. Since this guide is aimed at beginners, I explain below how to record an Excel macro using the recorder. If your objective is to only record and play macros, this tutorial likely covers most of the knowledge you require to achieve your goal. As explained by John Walkenbach one of the foremost authorities in Microsoft Excel in the Excel 2013 Bible, if your objective is to only recording and playing macros: … you don't need to be concerned with the language itself although a basic understanding of how things work doesn't do any harm. However, if you want to benefit from Excel macros to the maximum and use their power fully, you will eventually need to learn VBA. Excel Bill Jelen another one of the foremost Excel wizards and Tracy Syrstad an Excel and Access consultant say in Excel 2013 VBA and Macros, recording a macro is helpful when you are beginner and have no experience in macro programming but… … as you gain more knowledge and experience, you begin to record lines of code less frequently. Therefore, I cover some of topics related to Visual Basic for Applications more deeply in other tutorials. You're ready to make your first Excel macro. To do it, simply follow the 7 easy steps which I explain below. Click on the Developer tab. If relative reference recording is turned on, as in the case of the screenshot below, you don't need to click anything. I may explain the use of relative and absolute references further in future tutorials. However, for the moment, ensure you have turned on relative reference recording. As you may imagine, this macro does not work very well if, when using it, you are in any cell other than A3. The following image shows how this would look like if you are working in cell H1 and activate the macro with absolute references explained above. The Record Macro dialog appears. However, as explained by John Walkenbach in Excel VBA Programming for Dummies, it is generally more helpful to use a descriptive name. I cover the topic of macro naming in detail for Sub procedures and for Function procedures. This step is optional. You can set up a macro without a keyboard shortcut but selecting a keyboard shortcut allows you to execute the macro by simply pressing the chosen key combination. In this context, key combination means either i a letter by itself or ii a combination of a letter plus the Shift key. When creating keyboard shortcuts for macros, you want to be careful about the exact key combination that you choose. If you choose a keyboard shortcut that has been previously assigned for example a built-in keyboard shortcut , your choice of keyboard shortcut for the Excel macro overrides and disables the pre-existing keyboard shortcut. The risk of overwriting and disabling a previously existing keyboard shortcut is smaller but, in any case, I suggest you continue to be careful about the exact key combination that you choose. The default selection is to store the macro in the workbook you are working on. In this case, you are only able to. In Excel 2013 In Depth, Bill Jelen defines a Personal Macro Workbook as … a special workbook designed to hold general-purpose macros that may apply to any workbook. The main advantage of saving macros in the Personal Macro Workbook is that those macros can later be used in future Excel files because all those macros are available when you use Excel in the same computer where you saved them, regardless of whether you are working on a new or different Excel file from the one you created the macro on. Having a macro description is optional. However, as explained by Greg Harvey in Excel 2013 All-in-One for Dummies: It is a good idea to get in the habit of recording this information every time you build a new macro so that you and your co-workers can always know what to expect from the macro when any of you run it. Harvey also suggests that you include the date in which the macro was saved and who created the macro. Once you have assigned a name, set the location where you want to store the macro and if you wanted assigned a keyboard shortcut and created a macro description, click on the OK button to close the Record Macro dialog. Perform the actions you want the macro to record and store. Example Of How To Create An Excel Macro If you follow the 7 easy steps explained above, you're already able to start creating basic macros. However, I promised that this Excel Macro Tutorial for Beginners would include an example. I have already explained how you can get the Developer tab to show up in Excel. Since you only need to ask Excel one time to display the Developer tab, the image below only shows the actual recording of the macro. For this particular example, I have used the parameters described above when working with the Record Macro dialog. You Have Created Your First Excel Macro! I hope you have found it easy to create your first Excel macro. At the very least, I hope that you realize that the basics of Excel macros are not as complicated as they may seem at first sight. I know that the macro we have recorded above is a very basic example and, in other posts about VBA and macros, I dig deeper in more complicated topics that allow you to set up more complex and powerful macros. However, it is true that the information in the previous sections of this Macro Tutorial for Beginners is enough to set up a relatively wide variety of macros. In the Excel 2013 Bible, John Walkenbach explains that: In most cases, you can record your actions as a macro and then simply replay the macro; you don't need to look at the code that's automatically generated. Therefore, once again, congratulations for creating your first Excel macro! The Next Step In Creating Excel Macros: Enter VBA I have quoted twice how John Walkenbach, one of the most prolific authors on the topic of spreadsheets, implies that casual users of Excel macros do not necessarily need to learn programming. However, this doesn't mean that you shouldn't learn programming. If you are committed to unleashing the power of Excel macros, you will have to learn Visual Basic for Applications. Programming Excel macros using VBA is more powerful than simply recording the macros for several reasons, the main one being that using VBA code allows you to carry tasks that can't be recorded using the Macro Recorder. Beginning To Learn How To Write Excel Macro Code You have already learned how to set up a macro in Excel and, as you saw in the most recent sections, the macro is working. In order to start learning how to program macros, it is useful to take a look at behind that you have produced when recording the macro. In order to do this, you need to activate the Visual Basic Editor. Excel opens the Visual Basic Editor which looks roughly as follows: The VBE window is customizable so it is quite possible that the window that is displayed in your computer looks slightly different from the above screenshot. Since you may have these same questions, let's answer them. What Are You Looking At In The Visual Basic Editor You can divide the VBE in 6 main sections: 1. Item 1: The Menu Bar. The Visual Basic Editor menu bar is, pretty much, like the menu bars that you use in other programs. More precisely, a menu bar contains the drop-down menus where you can find most of the commands that you use to give instructions to, and interact with, the VBE. If you're working with more recent versions of Excel 2007 or later , you may have noticed that Excel itself does not have a menu bar but, rather, a Ribbon. The reason for this is that, from Microsoft Office 2007, has has replaced the menus and toolbars of some programs with the Ribbon. Item 2: The Toolbar. The VBE Toolbar, just like it happens with the menu bar, is similar to the toolbars you may have encountered when using other types of software. To be more exact, the toolbar contains items such as on-screen buttons, icons, menus and similar elements. The toolbar displayed in the screenshot above is the standard and default toolbar of the Visual Basic Editor. As explained above, if you have a newer version of Excel starting with 2007 you won't see neither a toolbar nor a menu bar on the Excel window because Microsoft replaced both of these items with the Ribbon. Item 3: The Project Window or Project Explorer. The Project Window is the part of the VBE where you can find a list of all the Excel workbooks that are open and the add-ins that are loaded. This section is useful for navigation purposes. There may be several folders for different types of items, such as sheets, objects, forms and modules. I explain what all of these are in other VBA and macro tutorials. When a folder is expanded, you are able to see the individual components inside that folder. For example, in the image above, there are 2 folders Microsoft Excel Objects and Modules and the Microsoft Excel Objects folder which is expanded has two items Sheet1 and ThisWorkbook. If you can't see the Project Explorer, it may be hidden. Item 4: The Properties Window. The Properties Window is the section of the VBE that you use to edit the properties of anything you may have selected in the Project Window. It is possible to hide or unhide the Properties Window. Item 5: The Programming or Code Window. The Programming Window is where the VBA code that you record appears. I explain how you can get the Visual Basic Editor to display the code of your macros in the next section. In addition to displaying code, the Code Window is where you can actually write or edit VBA code. Item 6: The Immediate Window. The Immediate Window is useful for purposes of noticing errors, checking or debugging. You may have noticed that, in the first screenshot of the VBE that I included above, there is no Immediate Window. You can see the elements inside the first folder Microsoft Excel Objects but not inside the second Modules. In other words, a module is where the VBA code is actually stored. The good news is that, to a certain extent, it probably does make a little bit of sense. However, you may feel that you're not fully understanding all of the instructions in the Excel macro you created. All of these feelings and questions are normal. Let's take a closer look at the macro code to understand all of this. Learning VBA From Scratch Using An Example Of Basic Excel Macro Code Good news first. As you may have noticed, VBA code is kind of similar to English. In VBA for Excel Made Simple, Keith Darlington an experienced programming teacher explains how structured English which is similar to regular English can be a helpful intermediate step to think the instructions that a macro should follow before actually writing those instructions in Visual Basic for Applications. Therefore, you are probably able to understand some of the words, and perhaps even some of the instructions above. The active cell is the cell that is currently selected in a worksheet. That is right, as you probably imagine, this piece of code selects the current active cell. This code line begins, once again, with a selection. However, it then makes reference to columns and to auto-fitting. Considering the above, you may have correctly thought that the purpose of this piece of VBA code is to auto-fit the column where the active cell is located so that the text the macro types fits in a single column. However, if you are currently learning about Excel macros, you may want to understand what every single line of code means, so let's understand some of these basics of VBA code. Basics Of Excel Macro Code To understand each of the instructions behind the macro that you have recorded, let's check out the entire code line-by-line and item-by-item, which is how Excel executes the macro. Don't worry if you don't understand every single line below now. You'll notice not only this time but generally when recording macros that the VBA code may include some actions that you didn't actually carry out. I may explain, in future tutorials, how you can remove them. This is one of the two types of macros or procedures that you can use in Excel. Sub procedures carry out certain actions or activities in Excel. The other type of procedure is a Function procedure. Function procedures are and return a value. It simply tells Excel that you are writing a new Sub procedure. Therefore, when executing a macro, Excel simply ignores the comments. Comments may explain things such as the purpose of a procedure or what are the most recent modifications that were made to the procedure. Select As explained above, this line tells Excel to select the current active cell. The last portion R1C1 makes reference to the R1C1 notation in which cell references are relative instead of absolute. I explain R1C1 notation more in depth in. However, remember that at the beginning of this guide, I explain why you should turn on relative reference recording and how to do this. AutoFit As I explain above, this particular statement makes Excel auto-fit the column of the active cell so that the text that the macro has typed fits fully within it. As you probably expect, the next activity that Excel carries out is to color the active cell red. You'd expect that coloring the active cell is a simple step. However, it turns out that Excel needs to carry out a lot of steps in order to carry out this action. This is the reason why exist. In the case of the example used in this guide for beginners, this object is the active cell. Line 1: With Selection. How does it achieve this? It tells Excel to set the inner pattern of the active cell to a solid color. This is the statement that actually announces to Excel what is the color it should use to fill the interior of the active cell. This line orders Excel to not lighten nor darken the color that was chosen for the active cell filling. When TintAndShade is set equal to 0 as in this case , is fixed to neutral and, therefore, there is no lightening or darkening of the color selected for the active cell. As you may imagine, this line conveys to Excel that it should set no tint nor shade pattern for the interior of the active cell. Line 7: End With. Line 1: With Selection. In this case, this object is Selection. In other words, Selection. Font means the font of the text in the active cell. It instructs Excel to not lighten nor darken the color of the font. Line 4: End With. Therefore, any lines of code below this one don't make reference to the font of the active cell. Item 8: End Sub terminate the execution of something, in this case a Sub procedure. This means that, once Excel executes this line of code, the macro that you have created stops running. In other words, this is the end of the code of your first Excel macro. A Few Final Tips Regarding How To Learn About Excel Macros If you want to go the extra mile for purposes of speeding up your learning process about Excel macros, I provide some final tips below. You can try most of them in the example Excel workbook that accompanies this Excel Macro Tutorial for Beginners. You can get immediate free access to this example workbook by subscribing to the Power Spreadsheets Newsletter. You can also change the numbers that specify the cell filing and font color. The results changed substantially, right? Isn't it interesting how much difference a couple of small items in the VBA code can make? Return again to Excel and run the macro one more time with this deletion. Was it what you expected? Record Excel macros different from the example that appears in this Excel Macro Tutorial for Beginners. Try new things and see what happens. Open the VBE and go through the VBA code line-by-line to understand what is the purpose of each statement. Perhaps even better, if you have a big enough screen or two monitors , is to follow John Walkenbach's advice in the Excel 2013 Bible and… … set up your screen so that you can see the code that is being generated in the VB Editor windows. You can, for example, go through in order to find all the Excel tutorials I've written regarding VBA and macros. Additionally, you may want to register for our Newsletter by entering your email address below. This ensures that you don't miss out on any future blog posts. Conclusion Once again, congratulations! After going through this Excel Macro Tutorial for Beginners, you have created your first macro and understood the VBA code behind it. As you may have seen, setting up a macro using Excel's recorder is relatively simple and can be done in seven easy steps. If your main purpose is to simply record and play Excel macros, you are ready to go! If your purpose is to become a macro expert, I hope this basic guide has given you a good idea of how to record Excel macros and a basic introduction to VBA programming. Furthermore, I hope that this Excel Macro Tutorial for beginners gives you some confidence about your Excel programming abilities. Put in practice the final tips regarding how to learn about macros that I have provided in the section above. Produce macros, study the VBA code behind them and try different things to see what happens. Books Referenced in this Excel Macro Tutorial for Beginners Use the following links to visit the book's webpage in Amazon. I love reading and sharing success stories from amazing members of the Power Spreadsheets community, like you. If this or any other Tutorial has helped you, please share your success story below. This only takes few seconds and, by doing it, you help the future development of Power Spreadsheets. Any improvements I make to this or the other free Tutorials in Power Spreadsheets based on your feedback will benefit you too. I'd like to highlight your amazing work if there's a suitable opportunity. Therefore, I may publish your success story so other members of the Power Spreadsheets community can learn and be inspired by your success. If there's any data about you or your success story you don't want me to publish, please expressly specify this below.
The macro had Sub Nameofmacro and End sub at the top and bottom line of the code. The final result looks like the image below. Step 6: Now go to Excel and close it. You will see why in later lessons. VBA is Visual Basic for Applications, a programming language that you can use in many Microsoft apps. You do not want your user to go to the Visual Basic Editor to trigger a macro.
