/ / Editing Macros Editing and Deleting Macros in Microsoft Excel Editing a Macro If you need to make simple changes to a macro, such as inserting text or deleting a command, such as a specific format applied to a cell, you can edit the macro. You edit a macro in the Visual Basic Editor, shown in Figure 2-6. The elements of the Visual Basic Editor are described in the table below. The Project Explorer, Properties window, and Code window all appear when you open the Visual Basic Editor. Since you won’t need the Properties window while performing simple editing, you can close the Properties window, and then expand the Project Explorer to view more of its window. Each open workbook in Excel has a project associated with it in the Project Explorer. Navigating the Project Explorer is similar to navigating Windows Explorer, in that they both have hierarchical structures.
The code for a macro is stored in a module, which is simply a holding place for the code, just as a worksheet is a holding place for data in cells. Double-clicking a module in Project Explorer displays the module’s code in the Code window.
A Macro is a piece of programming code that runs in Excel environment and helps automate routine tasks. Learn step by step example of recording Macros. How to Write Macros in Excel: Step by Step Tutorial.
Editing Visual Basic code is similar to editing text in a word processing program. For more details of in Chicago and Los Angeles call us on 888.815.0604. Our classes are hands-on and instructor-led. Are also available. Element Description Project Explorer Contains projects that store the Visual Basic code for each open workbook. Each project can contain folders for objects (such as the worksheets in the workbook), forms, references, and modules.
You select a module to view its code, copy modules to other open workbooks, and delete modules. Code window Displays the Visual Basic code for the selected module in a project. Properties window Displays specific characteristics of an object, such as the name of the object, or the standard width of the columns. Standard (macro) toolbar Displays the basic tools needed to use the Visual Basic Editor. Object box Contains a drop-down list from which to select the desired object whose code you want to view in the Code window. If General appears in the Object box, all the code for the macros associated with the selected module appears in the Code window.
Procedure box Contains a drop-down list from which to select a macro to display the macro’s code in the Code window. Steps to Edit a Macro To display the Visual Basic Editor:. In the Code group on the Developer tab, click the Visual Basic button. To edit a macro:. Display the Visual Basic Editor. From the Tools menu, choose Macros. In the Macros dialog box, from the Macros In drop-down list, select the project containing the macro you want to edit.
In the Macro Name list box, select the desired macro. Choose Edit. In the Code window, make the desired edits. Close the Macros dialog box. To close the Visual Basic Editor:. From the File menu, choose Close and Return to Microsoft Excel.
Deleting a Macro If you no longer need a macro, you can delete it. Deleting unwanted macros makes it easier to view macros in the Code window in the Visual Basic Editor as well as view macros in the Macro dialog box. You can delete a macro in an open workbook using the Macro dialog box or the Visual Basic Editor. If you want to delete a macro in the Personal Macro Workbook using the Macro dialog box, you must first unhide the Personal Macro Workbook. A benefit of using the Visual Basic Editor is that you can delete any macro in any open workbook or the Personal Macro Workbook, without unhiding it.
Steps to Delete a Macro Macro dialog box method:. Locate the Code group in the Developer tab on the Ribbon. In the Code group on the Developer tab, click the Macros button. In the Macro dialog box, in the Macro Name list box, select the macro you want to delete.
Choose Delete. In the message box that appears, choose Yes. Visual Basic Editor method:. Locate the Code group in the Developer tab on the Ribbon.
In the Code group on the Developer tab, click the Visual Basic button. From the Tools menu, choose Macros.
In the Macros dialog box, from the Macros In drop-down list, select the project containing the macro you want to delete. In the Macro Name list box, select the desired macro. Choose Delete.
From the File menu, choose Close and Return to Microsoft Excel. Onsite Excel training Through our network of local trainers we deliver onsite group Microsoft Excel training right across the country.
Excel courses.
You want to automate a repetitive task in Excel, so that you can do the task again with a single click. The best way to do that?
Record a macro. The macro recorder is the easiest way to create a new macro in Excel. A quick note: Macros aren't available in Office on a Windows RT PC. To see what version of Office 2013 you are running, click FILE and then click Account. If you are on a Windows RT PC, you'll see Microsoft Office 2013 RT here. Now before we get started, I want to make sure the DEVELOPER tab is available on the ribbon. That's where all of the macro commands are.
I click the FILE tab Options Customize Ribbon, and over here, I select Developer, and then click OK. Now, I see the DEVELOPER tab and here in the Code group are the Record Macro button and some other buttons I'll go over later. So, let's get started.
I'll record an auto-fill operation where I build a series of days based on typing Sunday in a cell. After I type Sunday, I right-click the cell and drag it to the right. As I move over here, the Tool Tips show what will go in each cell, all the way through Saturday. When I get over here, I release the right mouse button and click Fill Series. That's what we want to record. So, I'll clear these cells and select A1.
I'll repeat these steps, but this time, I'll turn on the macro recorder. I click the Record Macro button. Let's name this macro, 'FillDays'. Macro names can't contain spaces. I click OK to start the recorder. See how the Record Macro button turned to Stop Recording. When I'm done, I'll click that.
We are done with our auto-fill, and since that's all we wanted to record, I'll click Stop Recording. Now I'll clear these filled cells to clean up the worksheet, and select A1 again so we can test our macro. I'll click the Macros button to bring up the Macro dialog box, and click Run. Perfect, so far. I'll clear these cells again, and this time I'll select a cell in a different column and row C3, just to test the macro a little more. Watch what happens.
The macro filled-in A1 through G1. In other words, it failed. So, why that happened? It happened because, by default, the macro recorder was using absolute references.
Before I started recording, I should have turned on Use Relative References. By using relative references, the macro will build a series by selecting the active cell and 6 cells to its right, instead of building a series using A1 through G1.
Well, I know I need to record a new macro. So, I'll clear these cells, and turn on Use Relative References. Just to change things up a bit, let's start at cell B5. So, I click Record Macro, give it a new name, 'FillDaysRelative', and click OK to start recording. I'll type Sunday, do my auto-fill, and Stop Recording.
Now, I'll clear these cells and I'll switch to cell C7 and run my macro from there. This time, I choose 'FillDaysRelative' and Run, and now it works perfectly. So, you can see that the relative references setting can be very important.
In the next video, we'll rewrite the 'FillDaysRelative' macro manually in an Excel tool called the Visual Basic Editor, so that it'll do something a little bit different.