How To Create A New Macro In Excel 2007
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. Even if you have practice in carrying out these activities and you are able to complete them relatively fast, those "5 minutes" that you spend almost every day inserting your company's name and details in all the Excel worksheets you send to clients/colleagues start adding up over time.
In most (not all) cases, investing much time on these common but repetitive operations doesn't yield proportional results. In fact, most of them are great examples of the 80/20 principle in action. 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.
More precisely, in this Excel tutorial I show you how to set-up a macro that does the following:
- Type "This is the best Excel tutorial" into the active cell.
- Auto fit the column width of the active cell, so that the typed text fits in a single column.
- Color the active cell red.
- Change the font color of the active cell to blue.
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 clicking the button below.
The 7 steps that I explain below are enough to set you on your way to producing basic Excel macros.
However, if you are interested in fully unleashing the power of macros and are interested in learning how to program Excel macros using VBA, the second part of this Excel Macro Tutorial for Beginners sets you on your way to learn more advanced topics by:
- Introducing you to Visual Basic for Applications (or VBA) and the Visual Basic Editor (or VBE).
- Explaining how you can see the actual programming instructions behind a macro, and how you can use this to start learning how to write Excel macro code.
- Give you some tips that you can start using now to improve and accelerate the process of learning about macros and VBA programming.
You can use the following table of contents to skip to any section. However, I suggest that you don't actually skip any sections 😉 .
Are you ready to create your first Excel macro?
Then let's begin with the preparations…
Before You Begin Creating Macros: Show The Developer Tab
Before you create your first Excel macro, you must ensure that you have the appropriate tools.
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.
1. Step #1.
Open the Excel Options dialog using any of the following methods:
- Method #1.
Step #1: Using the mouse, right-click on the Ribbon.Step #2: Excel displays a context menu.
Step #3: Click on "Customize the Ribbon…".
The following image illustrates these 3 steps:
- Method #2.
Step #1: Click on the File Ribbon Tab.
Step #2: On the navigation bar located on the left side of the screen, click on "Options".
The following image shows you how to do this:
- Method #3.
Use keyboard shortcuts such as "Alt + T + O" or "Alt + F + T".
2. Step #2.
Once you are in the Excel Options dialog, ensure that you are on the Customize Ribbon tab by clicking on this tab on the navigation bar located on the left side.
3. Step #3.
Take a look at the Customize the Ribbon list box, which is the list box located on the right side of the Excel Options dialog, and find "Developer".
This is the Developer tab which, by default, is the third tab from the bottom of the list (just above "Add-Ins" and "Background Removal").
The box to the left of "Developer" is, by default, empty. 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.
4. Step #4.
If the box to the left of "Developer" is empty, click on it to add a checkmark.
If box already has a checkmark, you don't need to do anything (you should already have the Developer tab in the Ribbon).
5. Step #5.
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.
How To Enable The Developer Tab In Images
The image below takes you, step-by-step, through the process described above:
Tools For Creating Excel Macros
Excel allows you to create macros by using either of the following tools:
- The Macro Recorder, which allows you to record the actions you carry out in an Excel Workbook.
- The Visual Basic Editor, which requires you to write the instructions you want Excel to follow in the programming language Visual Basic for Applications.
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. As Mr. 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.
However, for the moment, I explain below how you can record an Excel macro using the recorder:
The 7 Easy Steps To Creating Your First Macro
OK…
By now you have added the Developer tab to the Ribbon and you are aware that there are two different tools you can use to produce a macro, including the recorder.
You're ready to make your first Excel macro. To do it, simply follow the 7 easy steps which I explain below.
1. Step #1.
Click on the Developer tab.
2. Step #2.
Ensure that relative reference recording is turned on by checking "Use Relative References".
If relative reference recording is not turned on, as in the case of the screenshot below, click on "Use Relative References".
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.
When relative recording is turned off (which it is by default), absolute/exact cell addresses are recorded. When relative reference recording is turned on, any actions recorded by Excel are relative to the active cell. In other words, the absolute recording is, as explained by Bill Jelen in Excel 2013 in Depth, "extremely literal".
For example, let's assume that you are recording an Excel macro that:
- Types "This is the best Excel tutorial" into the active cell.
- Copies the text that you have just typed and paste it in the cell immediately below.
If, at the time of recording the active cell is A3 and you fail to turn on relative recording, the macro records that it must:
- Type "This is the best Excel tutorial" in the active cell.
- Copy the text and paste it in cell A4, which is the cell immediately below the active cell at the moment of beginning the recording of the macro.
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.
3. Step #3.
Click on "Record Macro" on the Developer tab or on the Record Macro button that appears on the left side of the Status Bar.
4. Step #4.
The Record Macro dialog appears. This dialog allows you to:
- Assign a name to the macro.
Excel assigns a default name to macros: "Macro1", "Macro2", "Macro3" and so on. However, as explained by John Walkenbach in Excel VBA Programming for Dummies, it is generally more helpful to use a descriptive name.
The main rules for macro names are that they must begin with a letter or an underscore (_) (not a number), can't have spaces or special characters except for underscore (which is allowed), and should not conflict with previously existing names. I cover the topic of macro naming in detail here (for Sub procedures) and here (for Function procedures).
For example, "Best Excel tutorial" is not an acceptable name, but "Best_Excel_Tutorial" works:
- Assign a keyboard shortcut to the macro.
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.
The keyboard shortcut to be assigned is of the form "Ctrl + 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. Since Excel has several built-in keyboard shortcuts of the form "Ctrl + Letter", the risk of disabling built-in keyboard shortcuts is not that small.
Take, for example, the keyboard shortcut "Ctrl + B", which is the built-in keyboard shortcut for the Bold command.
If, however, you assign the keyboard shortcut "Ctrl + B" to a particular macro, the built-in keyboard shortcut for the Bold command is disabled. As a consequence, if you press "Ctrl + B", the macro would be executed but the font of the selected text would not be made bold.
One way to address this problem, which generally works, is to assign keyboard shortcuts of the form "Ctrl + Shift + Letter". 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.
This means that, for example, instead of choosing "Ctrl + B" as a keyboard shortcut, we could assign "Ctrl + Shift + B":
- Decide where you want to store the macro.
You can store the macro in the workbook you are working on ("This Workbook"), a new Excel file ("New Workbook") or a personal macro workbook ("Personal Macro Workbook").
The default selection is to store the macro in the workbook you are working on. In this case, you are only able to use that macro when that particular workbook is open.
If you choose "New Workbook", Excel opens a new file. You are able to record and save the macro in that new workbook but, just as in the case of selecting "This Workbook", the macro only works in the file where it was created.The more advanced storage option is "Personal Macro Workbook". 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.
- Create a macro description.
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.
5. Step #5.
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.
6. Step #6.
Perform the actions you want the macro to record and store.
7. Step #7.
Click on "Stop Recording" on the Developer tab or on the Stop Recording Macro button that appears on the left side of the Status Bar.
That's it… it really takes only these 7 easy steps to record your first macro.
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. Therefore, in this section, we set-up a macro that does the following three things:
- Type "This is the best Excel tutorial" into the active cell.
- Auto-fit the column width of the active cell.
- Color the active cell red.
- Change the font color of the active cell to blue.
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. More precisely: the name assigned to the macro is "Best_Excel_Tutorial", the keyboard shortcut is "Ctrl + Shift + B", and the Excel macro was saved in the Excel workbook I was working on.
Are you done?
If you are done…
Congratulations! You Have Created Your First Excel Macro!
Amazing!
You can now go ahead and run your new macro by using the keyboard shortcut that was assigned (in this case "Ctrl + Shift + B"). As you become a more advanced macro user, you'll see that there are several other ways to execute a macro such as the Best_Excel_Tutorial macro above.
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.
For example:
- In the Excel 2013 Bible, John Walkenbach lists some examples of tasks that can't be recorded such as displaying "custom dialog boxes, or process data in a series of workbooks, and even create special-purpose add-ins."
- In Excel 2013 VBA and Macros, Bill Jelen and Tracy Syrstad tell us that is "important to recognize that the Macro Recorder will never correctly record the intent of the AutoSum button."
If I had the space, I could go continue to create a really long list of examples of how programming with VBA is a superior way to create macros than 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 the actual instructions (or code) behind that you have produced when recording the macro. In order to do this, you need to activate the Visual Basic Editor.
Let's open the VBE by clicking on "Visual Basic" in the Developer tab or using the keyboard shortcut "Alt + F11".
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.
The first time I saw this window several years ago, the following where my first two questions:
- What am I looking at?
- Perhaps more importantly, where is the code of my macro?
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, Microsoft has replaced the menus and toolbars of some programs with the Ribbon.
2. 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 by John Walkenbach in Excel VBA Programming for Dummies, most people (including Walkenbach himself) "just leave them as they are".
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.
3. 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.
As you can see in the image below, the Visual Basic Editor allows you to expand or collapse the different sections of the list by clicking on the "+" or "-" (as the case may be) that is displayed on the left side of the appropriate branch.
When "VBAProject" is expanded, it shows the different folders that are currently loaded. 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.
In order to show the Project Window, use the "Ctrl + R" keyboard shortcut, click on the Project Explorer icon in the toolbar, or go to the View menu and click on "Project Explorer":
4. 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.
If your Visual Basic Editor is not showing the Properties Window at the moment, use the "F4" keyboard shortcut, click on the Properties Window icon in the toolbar, or expand the View menu and click on "Properties Window":
5. 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.
6. 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. There are two main reasons for this:
- This window is, by default, hidden.
- As explained by John Walkenbach in Excel VBA Programming for Dummies, this window is not that useful for beginners and, therefore, it may be more appropriate to keep it hidden or, if currently showed, hiding it.
To unhide the Immediate Window, use the "Ctrl + G" keyboard shortcut, or access the View menu and click on "Immediate Window".
Now that you know what you're looking at when working with the Visual Basic Editor, let's go ahead and learn how you can see the actual code of the macro you have created…
Where Is Your VBA Macro Code
The section of the VBE that you usually use for navigation purposes is the Project Window. Let's go back to it and take a closer look at the screenshot above:
In the screenshot above, "VBAProject" is expanded and shows two folders: Microsoft Excel Objects and Modules. You can see the elements inside the first folder (Microsoft Excel Objects) but not inside the second (Modules).
To expand the Modules folder and see its components, click on "+":
The Project Explorer looks roughly as follows:
The items that appear in the Microsoft Excel Objects folder may look familiar. However, you may wonder…
What is a module?
A module is, in the words of John Walkenbach in the Excel 2013 Bible, "a container for VBA code".
In other words, a module is where the VBA code is actually stored. If you have followed along the example in this Excel Macro Tutorial for Beginners, your macro code is in a module, more precisely in Module1:
In order to get the Visual Basic Editor to display the VBA code, double click on "Module1" or right-click on "Module1" and select "View Code":
And the VBE displays the macro code in the Programming Window. If you have followed the example in this guide for beginners and created the Best_Excel_Tutorial macro, your code looks roughly as follows:
Does this make any sense to you?
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.
You may also be wondering… why does something as simple as writing some text, auto-fitting a column, coloring a cell and changing the font color require so much programming?
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. For example, you may recognize or partially understand the following lines from the Best_Excel_Tutorial macro created in this guide for beginners:
- ActiveCell.Select.
The active cell is the cell that is currently selected in a worksheet. I assume that, even if you are not familiar with Excel or Visual Basic for Applications, you know what the word "select"means.
That is right, as you probably imagine, this piece of code selects the current active cell.
- Selection.Columns.Autofit.
This code line begins, once again, with a selection. However, it then makes reference to columns and to auto-fitting.
If you remember, the second thing that the Best_Excel_Tutorial macro was supposed to do is to auto-fit the column width of the active cell so that the text that was typed ("This is the best Excel tutorial") fits in a single cell.
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.
The purpose of this part of the guide isn't to make you an expert in Visual Basic for Applications, but to give you a basic idea of how VBA works and, more importantly, show you what are the instructions that Excel carries out in order to write "This is the best Excel tutorial", auto-fit the column, color the active cell red and change the font color to blue.
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. According to John Walkenbach, in the Excel 2013 Bible, this "is just a by-product of the method that Excel uses to translate actions into code."
In other words, at the moment you don't need to worry about the lines of code that appear to be useless. I may explain, in future tutorials, how you can remove them.
The Programming Window that contains the code of the macro that you have created has the following parts:
Item #1: Sub Best_Excel_Tutorial ()
Sub stands for Sub procedure. 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 used to carry out calculations and return a value.
So… what does this line do?
It simply tells Excel that you are writing a new Sub procedure.
Sub procedures must always begin with:
- The word "Sub".
- The name of the procedure, in this case Best_Excel_Tutorial.
- Parentheses.
Additionally, Sub procedures must always end with the words "End Sub", as you can see in the last line of code shown in the screenshot above (signaled by the number 8).
Item #2: Lines Of VBA Code In Green Font That Begin With '
I'm referring to the following lines:
' ' Best_Excel_Tutorial Macro ' Types "This is the best Excel tutorial". Auto-fits column. Cell color red. Font color blue. ' ' Keyboard Shortcut: Ctrl+Shift+B '
These are, simply, comments. Comments have the following main characteristics:
- They begin (or are indicated by) an apostrophe (').
- Visual Basic for Applications, basically, ignores the text that comes after the apostrophe all the way until the end of the line. Therefore, when executing a macro, Excel simply ignores the comments.
- As a consequence of the previous point, the main purpose of a comment is to display information about that particular macro and help you understand it. Comments may explain things such as the purpose of a procedure or what are the most recent modifications that were made to the procedure.
Item #3: ActiveCell.Select
As explained above, this line tells Excel to select the current active cell. More precisely:
- "ActiveCell" makes reference to the current active cell in the active window.
- "Select" activates an object on the current active Excel worksheet, in this case the current active cell to which ActiveCell made reference to.
Item #4: ActiveCell.FormulaR1C1 = "This is the best Excel tutorial"
This statement instructs Excel to write "This is the best Excel tutorial" in the active cell.
Let's check each of the individual parts of the line:
- You already know what is the purpose of ActiveCell is.
- "FormulaR1C1" tells Excel to set the formula for the object, in this case the current active cell to which ActiveCell refers to. 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 this tutorial. However, remember that at the beginning of this guide, I explain:
- Why you should turn on relative reference recording; and
- How to do this.
- "This is the best Excel tutorial" states what is the formula (in this case the text) that must be put in the object, in this case the active cell.
Item #5: Selection.Columns.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.
The following is the purpose of the different parts of this statement:
- "Selection" simply represents the current selection, in this case the active cell.
- "Columns" selects the columns in the selection, in this case the column where the active cell is located.
- "AutoFit" is kind of self-explanatory; it sets the width of the selected columns (as in this case) or the height of the selected rows to whatever size "achieves the best fit".
Item #6: With…End With Statement 1
I refer to the following group of lines, which is known as a With…End With statement:
With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 255 .TintAndShade = 0 .PatternTintAndShade = 0 End With
The Excel macro you created has already carried two out of the four actions it is supposed to perform:
- It has typed "This is the best Excel tutorial" in the active cell.
- It has auto-fitted the column width so that the text it has typed fits properly.
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 With…End With statements exist.
The main purpose of a With…End With statement is to simplify the syntax by executing several instructions which all refer to the same object without having to refer to that object each time. In the case of the example used in this guide for beginners, this object is the active cell.
As you can see in the screenshot below, the basic macro that you have recorded has two With…End With statements:
With…End With statements have the following structure:
- At the beginning, they must state "With objectExpression". I explore, in other tutorials, what "objectExpression" means. For the moment, is enough to know that in the case of the example included in this guide, "objectExpression" is "Selection.Interior" (for the first With…End With statement) and "Selection.Font" (for the second With…End With statement), as I explain below.
- They can have one or more lines of code, which are the instructions that are executed on the object to which reference has been made.
- At the end, they must say "End With".
In the case of the first With…End With statement, each of these objects looks as follows:
Now that you have a basic understanding of what a With…End With statement does, let's take a look at the first one line-by line:
1. Line #1: With Selection.Interior.
This line tells Excel that it should always refer to the interior of the active cell when executing the statements that are part of the With…End With statement.
How does it achieve this?
- "With" is the beginning of the With…End With statement and informs Excel that the subsequent lines of code refer (work with) the object mentioned in this row.
- "Selection.Interior" is the "objectExpression" which I mentioned above when explaining the structure of With…End With statements. "Selection" represents the current selection, which in this example is the active cell, whereas "Interior" denotes the interior of an object, in this case the inside of the active cell.
2. Line #2: .Pattern = xlSolid.
This is the first line of the With…End With statement that makes reference to the interior of the active cell.
It tells Excel to set the inner pattern of the active cell to a solid color. It does this as follows:
- "Pattern" sets the interior pattern.
- "xlSolid" specifies that the pattern should be a solid color.
3. Line #3: .PatternColorIndex = xlAutomatic.
This line specifies the automatic pattern for the inside of the active cell as follows:
- "PatternColorIndex" sets the color of the interior pattern.
- "xlAutomatic" specifies that the color should be an automatic color.
4. Line #4: .Color = 25.
This is the statement that actually announces to Excel what is the color it should use to fill the interior of the active cell.
"Color" assigns the cell color, whereas the number (in this case 255) specifies the color which, in the Best_Excel_Tutorial macro, is red.
5. Line #5: .TintAndShade = 0.
This line orders Excel to not lighten nor darken the color that was chosen for the active cell filling.
"TintAndShade" sets the lightening or darkening of a color. When TintAndShade is set equal to 0 (as in this case), the property is fixed to neutral and, therefore, there is no lightening or darkening of the color selected for the active cell.
6. Line #6: .PatternTintAndShade = 0.
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.
"PatternTintAndShade" sets the tint and shade pattern for the inside of an object, in this case the selected cell.
7. Line #7: End With.
This line signals to Excel the end of the With…End With statement.
Therefore, the subsequent lines of code make reference to a different object than that to which this With…End With statement did.
In the case of the example used in this Excel Macro Tutorial for Beginners, the end of the first With…End With statement indicates that the subsequent statements do not make reference to the interior of the active cell.
Item #7: With…End With Statement 2
You have learned above what is a With…End With statement and what is its general structure.
Therefore, I go straight to a line-by-line explanation of the second With…End With statement of the macro which, as you probably expect, carries out the last of the instructions that you gave when creating it: changing the font color of the active cell to blue.
You will probably be happy to read that this second With…End With statement is shorter than the first.
Let's start the line-by-line explanation…
1. Line #1: With Selection.Font.
As I explain above, this is the opening of the With…End With statement, where the "With" tells Excel that the following statements work with the object that appears here.
In this case, this object is Selection.Font.
So what is "Selection.Font."?
"Selection" is the current selection, which in the Best_Excel_Tutorial macro is the active cell, whereas "Font" is (not surprisingly) the font.
In other words, Selection.Font means the font of the text in the active cell. Therefore, "With Selection.Font" is basically informing Excel that all the lines of code that are part of the With…End With statement make reference to the font of the active cell.
2. Line #2: .Color = -4165632.
This line of code, as you may expect considering the very similar line in the first With…End With statement above, tells Excel what color should be used for the font in the active cell.
"Color" assigns the color, whereas the number (in this case -4165632) is the actual color code which, in this case, is blue.
3. Line #3: .TintAndShade = 0.
This statement is exactly the same as one of the lines in the With…End With statement above. It instructs Excel to not lighten nor darken the color of the font.
Since "TintAndShade" defines the lightening or darkening of a color, when it is equal to 0 (as it is here), Excel neither lightens nor darkens the font color of the active cell.
4. Line #4: End With.
This is the end of the With…End With statement.
Therefore, any lines of code below this one don't make reference to the font of the active cell.
Item #8: End Sub
End statements 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 clicking the button below.
- Change parts of the VBA code to try new things.
For example, change "ActiveCell.FormulaR1C1 = "This is the best Excel tutorial"" for "ActiveCell.FormulaR1C1 = "I love Microsoft Excel"".
You can also change the numbers that specify the cell filing and font color. For example, change ".Color = 255" for ".Color = 10" and ".Color = –4165632" for ".Color = 200".
Go back to the main Excel window and run the macro again (by, for example, using the keyboard shortcut "Ctrl + Shift + B" that you assigned) and check out what happens.The results changed substantially, right? Isn't it interesting how much difference a couple of small items in the VBA code can make?
- Delete certain statements from the code to see how they affect the macro.
For example, what do you think would happen if you delete "Selection.Columns.AutoFit"?
Try it out.Return again to Excel and run the macro one more time with this deletion.
What happened? Was it what you expected?
- One of the best ways to learn Excel macro code is to repeat the exercise contained in this guide, so I encourage you to do it.
How?
1. Record Excel macros different from the example that appears in this Excel Macro Tutorial for Beginners. Try new things and see what happens.
2. 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.
- Read and study. You can, for example, go through the Archives of Power Spreadsheets in order to find all the Excel tutorials I've written regarding VBA and macros.
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.
If you continue to study and practice Visual Basic for Applications, including the topics that I cover in other Excel VBA tutorials within Power Spreadsheets, you will soon be able to, among others:
- Understand much better what you have done while creating your first macro and how the Best_Excel_Tutorial macro works.
- Write and use much more complex and sophisticated Excel macros.
Books Referenced in this Excel Macro Tutorial for Beginners
- Books in The Power Spreadsheets Library.
- Darlington, Keith (2004), VBA for Excel Made Simple. Burlington, MA: Made Simple Books.
- Harvey, Greg (2013). Excel 2013 All-in-One for Dummies. Hoboken, NJ: John Wiley & Sons Inc.
- Jelen, Bill (2013). Excel 2013 in Depth. United States of America: Que Publishing.
- Jelen, Bill and Syrstad, Tracy (2013). Excel 2013 VBA and Macros. United States of America: Pearson Education, Inc.
- Walkenbach, John (2013). Excel 2013 Bible. Indianapolis, IN: John Wiley & Sons Inc.
- Walkenbach, John (2013). Excel VBA Programming for Dummies. Hoboken, NJ: John Wiley & Sons Inc.
How To Create A New Macro In Excel 2007
Source: https://powerspreadsheets.com/excel-macro-tutorial-for-beginners/
Posted by: schroederfrossuche.blogspot.com
0 Response to "How To Create A New Macro In Excel 2007"
Post a Comment