Discover Excel Macros
Discovering macros and programming in Excel is quite easy if you follow the right steps and learn the right things. The exercises below are a great start and they are FREE.
Excel macros are powerful tools and they will allow you to do much more with Excel and to do it more rapidly.
You can easily learn to develop your own macros or use one of a few thousands of macros already developed by others and available on the World Wide Web.
Get Started for FREE in 3 easy steps
Step 1: Installing the Visual Basic Editor for Excel: If you are using any older or newer version of Excel (1997-2006 or 2010-2013) you do not need to install anything to work with macros in Excel. Everything is installed by default.
In Excel 2007-2010-2013 you might have to make the "Developer" ribbon visible by right clicking on the "Home" item of the Excel menu bar and then selecting "Customize the ribbon...". Make sure that the "Developer" ribbon is checked.
But if you are using Excel 2007 you need to install the Visual Basic Editor for Excel from your Office CD.
Step 2: Setting the security level of Excel: To be allowed to use macros with your copy of Excel you have to set the level of security. The same applies for the people to whom you will be sending macros.
For users of Excel 1997 to 2006: You will set the security level of Excel at "Medium" so that you can use the macros (VBA procedures) that you develop. From then on each time you try to open an Excel workbook you will be asked to "Enable " the macros. If you receive a Workbook from an unknown source, do not enable the macros. You are then fully protected against the very rare bad macros.
From the menu bar of Excel select "Tools" then "Macro" then "Security" and select "Medium".
For users of Excel 2007 to 2010: From the "Developer" ribbon click on the "Macro Security" button. Check the second level "Disable all Macros with Notification" and you are set. From then on you will be asked to activate macros by an alert that shows above the Excel grid or within a dialog window.
Step 3 Setting up the Visual Basic Editor in Excel (VBE): When you want somebody to do some work for you, you open your Email program and you send him a message in a language that he understands (English, Spanish, French...). When you want Excel to do some work for you, you open the Visual Basic Editor and you write the instructions in a language that Excel understands and that is called VBA (Visual Basic for Application).
IMPORTANT NOTE 1: There are no risks to your computer or to Excel in completing the task below. At any time if you feel uncomfortable, just close Excel without saving the workbook and try again later.
The Visual Basic Editor is a program within Excel that allows you to communicate with Excel. We will open it and start by setting it up so that working within it becomes easy and efficient.
Open Excel and open a new workbook (Book1).
On your keyboard press the "ALT" key (left of the space bar) and hold, strike the "F11"key (the F11 key is at the top of your keyboard).You now see the Visual Basic Editor. Again press "ALT/F11" and you are back into Excel. Use the "ALT/F11" key to go from Excel to the VBA and back.
When you first open the VBE you will see is a window somewhat like the image below.
If there are any open windows within the VBE like in the image below click on the Xs to close them and see a gray rectangle filling up the bottom part of the screen like in the image above.
The Three Windows in the Visual Basic Editor
To be efficient when working with the VBE there should always be 3 windows showing like in the image below; the Project Window (1), the Code Window ( 2), and the Properties Window (3), arranged as in the image below. You can resize the windows by left-clicking where the red stars are, holding and moving sideways or up and down. We will study each of the three windows in lessons 2, 3 and 4 but first we will set them up in the VBE.
In the exercise below we will setup the 3 windows of the VBE.
Remember that you will perform this task only once as each time you will open the VBE it will remain setup.
Step 3-1: Close all the windows that are open in the VBE to end up with this:
Step 3-2: Go to the menu bar "View" and click "Project Explorer". The result will be somewhat like the image below:
If the project window already appears as a column on the left side of the screen there is nothing else that you have to do for now. If the project window appears in the middle of the gray area like above, right-click in the white space in the middle of the project window and check "Dockable". Then click on the top blue bar of the Project window, hold and drag it left until the cursor (white arrow) touches the middle of the left side of the screen. When you let go of the mouse button the end result should be like shown in the image below. Congratulations you have setup the first major window of the VBE.
Step 3-3: Move your cursor on the line separating the project window and the gray rectangle. When it turns to two small parallel lines and arrows click, hold and move the lines sideways. Resize the two windows as you want them.
Step 3-4: Go back to the menu bar "View" and click "Properties Window". The Properties window will appear somewhat like in the image below.
If the Properties window is already located below the Project window there is nothing left to do. If it shows like in the image above, right-click in the white space in the middle of the Properties window and check "Dockable". Then click on the top blue bar of the Properties window and drag it left and down until the cursor (white arrow) touches the center of the bottom of the Project window. When you let go of the mouse button the end result should be as the image below. Congratulations you have setup the second major window of the VBE.
Step 3-5: Move your cursor on the line separating the project window and the properties window. When it turns to two small parallel lines and arrows click, hold and move the lines vertically. Resize the two windows as you want them.
Step 3-6: To add the code window to the setup, you just have to double click on the name of a component in the Project window (Sheet1, Sheet2, Sheet3 or ThisWorkbook) and its code window appears within the gray rectangle. You can maximize any Code window by clicking on its "Maximize" button .
The final result looks like the image below. The words "Option Explicit" might not be present in your Code window. We will address this issue later in the lesson on variables (Lesson 19). You might also have a VBAProject named FUNCRES.XLA or FUNCRES.XLAM in the project window. Forget about this project for now.
Step 3-7: Now go to Excel and close it. Re-open Excel, go to the VBE (ALT/F11) and you will see that the VBE setup persists.
You are now ready to develop macros
Complete the 3 small and simple exercises below
In the following exercises macros are also called VBA procedures.
Open Excel and a new workbook. Use the "ALT/F11" keys to move to the Visual Basic Editor. As you can see, the Project window shows you all the workbooks that are open ("Book1" in the example below) and their components. You can use the + and - signs to show the details.
A new Excel workbook includes three sheets and another component named "ThisWorkbook". As you will discover in the downloadable tutorial on Excel macros "ThisWorkbook" is a component in which you will store the macros that should start automatically when the workbook is opened.
The Properties window shows you the properties of the component that is selected in the Project Window (single click). For example in the new workbook if you single click on "Sheet1" in the Project Window you see the properties of sheet1 in the Properties Window like in the image below.
A worksheet has 12 properties that you can change in this Properties window. You will rarely work with the 12 properties of the sheet. The only properties that you will work with are the 2 "Name" properties and the "Visible" property.
So there are 2 "Name" properties. On the first line is the programmatical name of the sheet (Sheet1). The second "Name" property (9th line) is the name (or caption) that appears on the tab of the sheet in Excel.
Here is an exercise to show how simple it is to change the caption of an Excel sheet.
Step 1: Go to Excel (ALT/F11) and notice the names on the three tabs of "Sheet1" as in the image below.
Step 2: We will change the name (Caption) on the tab of "sheet1" o "Introduction". To do so right-click on the tab of the sheet and the following dialog window appears:
Step 3: Select "Rename". The menu disappears and the name of Sheet1 is highlighted. Enter "Introduction" and this new name will replace "Sheet1" when you click "Enter". The end result is illustrated in the image below.
Step 4: Come back to the Visual Basic Editor (ALT/F11) and notice in the Properties window that the property "Name" (the ninth property, the one without the parentheses) has changed to "Introduction" but the programmatical name of thesheet (line 1) is still Sheet1. You will discover the advantages and disadvantages of changing this property in the downloadable tutorial on Excel macros.
As you have now learned the name of the sheet can be changed from Excel. We will now complete another small exercise to change the name from the VBE Properties window.
Step 5: In the VBE select "Sheet2" in the Project window. On line 9 of the Properties window double-cllck on "Sheet2" and enter the name "Spreadsheet". Click "Enter"
Step 6: Go to Excel and notice that you now have a sheet named "Spreadsheet" .
In the downloadable tutorial on Excel macros you will not be bothered with long presentations of properties and vocabulary that you will never used. For example there is nothing about the 9 rarely used properties but you will learn everything you need about the 2 "name" properties and the "Visible" property that you can use to hide sheets so well that a user can not unhide and see it unless he has the "Password" with which you are protecting your VBA procedures.
The Code Window is where 90% of the VBA work is done; writing VBA sentences, testing your VBA procedures (macros) and modifying them when needed.
To illustrate some of what you can do in the Code window we will start by creating a small macro.
Step 1: In Excel notice that cells A1, A2 and A3 of "Introduction" are empty. Go to the Visual Basic Editor.
For the purpose of this exercise we will develop a small macro within the code window of a sheet. In the downloadable tutorial on Excel macros you will learn how to create modules and organise your macros within them.
Step 3: Click anywhere in the Code window
Step 4: You can either copy/paste the following macro from your browser to the code window of "Sheet1" or key it in.
If you decide to key it in you will start by entering the first line and then when you press enter the VBE will add the final Line "End Sub". Enter the rest of the code within the two lines. Make sure that everything is there including all the quotation marks, periods, parentheses, equal signs and spaces.
Step 5: Click on any line of the macro, go to the menu bar at the top of the VBE screen and click on "Run" then click "Run Sub/Userform".
Step 6: Go to Excel (ALT/F11) and see what has happened to cells A1, A2 and A3 of the sheet ""
Congratulations you have run and tested you first macro. Go to Excel and "Introduction" and see that what the macro was ordering Excel to do has been done. The value of cell "A1" is 34, the value of cell "A2" is 66 and there is a formula in cell A3 that sums cells A1 and A2.
Step 7: Go to Excel and clear the cells A1, A2 and A3 of "Introduction". On the menu bar go to "Tool" and click on "Macros". In the dialog window select "proFirst" and click on run.
You have run the macro from the menu bar of Excel. In lesson 9 on Events you will discover many other ways to start a macro includin adding buttpns on the worksheet..
There are plenty of other operations that you can execute in the code window. For example, you can test a macro line by line (step by step), go back a few lines and make corrections, use breakpoints to test only part of a macro. You will learn everything that you need about these operation in the downloadable tutorial on Excel macros. In section 2 of the tutorial ( lessons 11 to 23) you will learn the VBA vocabulary to write macros.
You are now ready to learn much more
Download the complete tutorial on Excel macros
33 Complete Lessons