Even if you recorded your Excel macros perfectly and they have been very helpful with your work, you may still have to modify them. Imagine having more data to add to the reports. Imagine your company expanding. There are a million ways that would want you to edit the wonderful macros you had created. And of course expanding and excelling is a good thing, so bear with it!
The good part is that you do not have to create the entire record again. You can modify the existing one. The bad part is, in order to make the modifications and additions; you need Visual Basic Applications which is another program. Luckily, the program is no rocket science. You just need to understand some basic stuff and can get it installed. Once you do that, life will be much, much easier.
Here is how you edit a macro created already in your computer.
- First go to the “start” menu and find Microsoft Excel. Open and through “file” open a new spreadsheet.
- On the toolbar at the top of the new spreadsheet, find “window”. Click here and go to “unhide”. This would bring your personal macro workbook forward. You need to unhide the workbook to make the intended changes to the data.
- To open the workbook you will have to click on “personal. XLS”. Click OK.
- To access the created records, go to “tools” and select “macro”; go to “macros” and the records in the personal workbook will be listed. From here you can choose the one you want to modify and it would open when you chose to edit it.
- Here is when the Visual Basic program comes in.
The language for visual basic program is not very different from the English syntax, as we have nouns and adjectives. In Visual Basic Application, the nouns are termed as “objects” and their qualities which would be the adjectives in common English or any other language are called the “properties” of that object. So, if you need that an object should have a desired property. You type the name of the object, followed by the property with a period between the two: object.property. Likewise, when you want one of the objects to follow a “method”, type in the name of the object, period, and then the method. It is simple but you need to understand these basics so that it does not hinder the editing rather than aiding it. Also note that when the Visual Basic Application is run, the names that you entered when you first created the macro will have additional codes assigned by the program. This is what you have to do. Copy the code, no matter whether or not you understand the code itself, and paste it five times in the column. To save the changes, go to file and click on Save Personal.XLS. Now go to “file” again and click “Close”. Now you can have the files and the Visual Basic Application closed down and the changed data will be saved in the same storage directory/ personal macro workbook.
This is all there is to editing the macro record. Once you are done with the above series of steps, you have successfully edited the macro. Now you should go back and double check if the method is working or not. Launch Microsoft excel again, and go to “file” and “new”. From here, if you remember the shortcut key you had specified earlier, press ctrl key followed by the shortcut code which is usually a letter or a symbol. The macro record will open and you can see if whether or not it is the edited version. The new names will also show in addition to the old ones.
In case you do not remember the shortcut key, go to “tools” and select “macro”, from here, click “macros” and from the list chose the one you want to double check. Click “run” and the corrected or edited version will be presented in case the editing was successful.
Skills like these require you to master them through sufficient practice. Once you do that, editing would not be difficult at all. So move ahead with the ease your computer has provided you!
- How to Use the MS Excel Keyboard Shortcuts
- How to Locate Duplicate Records Quickly By Using Conditional Formatting in MS Excel
- How to Use PasteSpecial – Procedures for Microsoft Excel 2003 and Microsoft Excel 2007
- How to Sort using an AutoFilter – Understanding and Learning the Feature
- How to Base a Chart Off of Auto Filtered Records
- How to Use IF IsError Together?
- How to Use Custom Views
- How to Create Pivt Table
- How to Manipulate PivotTable after It has Been Created
- How to Update the Data in a Pivot Table
- How to Create Dynamic Named Ranges
- How to Use an Array
- How to Embed 8 If Statements into 1
- How to Edit a Recorded Macro?
- How to Record a Macro