There are two most common ways to write VBA codes, they are–
- Record the MACRO.
- Write it in VBA Editor from scratch.
Obviously it’s easy to record the Macro and use it but there are certain disadvantages when you record the Macro like size of the script goes huge which makes it bit difficult to customize it if needed, When we have to deal with the dynamic contents like getting the search results from Google, in such case its recommended to write the VBA.
1. Record a macro:
Recording a macro is one of the powerful feature in Excel, It will record every action done by the user and it will automatically generate the code for the user and user gets the option to create a shortcut for the Macro so the next time when user wants to perform the same action, all user has to do is to press the short cut. Recording of Macro is very useful when it comes to perform the repetitive task at multiple times. It just not save the lot of time for the user but also it’s an very effective way to make the actions error free. User also has privilege to customize the recorded code as per the requirements.
In excel 2007:
Step 1 : View -> Select Macros
Step 2: Select Record Macro
- You will see the a dialog box “Record Macro” where you to fill the following details
- Enter “FirstMacro” as name and Shortcut key as Ctrl+w
- User can be very specific for which WorkSheet or WorkBook he wants to create Macro
- User can provide some details about the macro but its optional.
- Hit OK and now macro is recording is every activity done by the user.
Here we have put some values in Excel and Calculating the Avg Age of the students and this entire process is recorded.
For stopping the Macro recording
View-> Macro-> Stop Recording
Now whenever we want to repeat the entire action all we need to is press the Shortcut key ( Ctrl + w) which we had created before we started the recording.
We can also see the recorded code in Visual Basic Editor. Press “AltF11” and VB Editor will be displayed
VBAProject(Book1)->Expand Modules Folder-> Double Click on Module1