Excel VBA & Macros
VBA stands for Visual Basic for Applications. It is the programming language of Excel. VBA is also available in other Microsoft products like MS Outlook, MS PowerPoint, MS Project, MS Access as well.
What is the Usage of VBA in Excel?
Although, it is possible to develop complete application using VBA, below are the key usage of VBA:
- Automating Repeated Tasks
- Defining UDF (User Defined Functions/Custom Functions)
What is a Macro?
A macro is an executable set of instructions using VBA. VBA is the language in which macros coding is done.
One of the best utility available in Microsoft Excel is Macro Recorder. Using macro recorder you can quickly record and use macros as per your requirements. Macro recorder also provide an easy way to understand VBA language. By recording multiple macros and reviewing the VBA code you can enhance you understanding of VBA code easily.
Recording a Macro
The Developer Tab in the ribbon should be visible in order to work with Macros. If it is not visible then click File Tab next click Options afterwards click Customize Ribbon and finally select the check box against Developer on the right hand side in excel 2010/2013. In case of excel 2007 Click the Microsoft Office Button , and then click Excel Options, click Popular, and finally select the Show Developer tab in the Ribbon check box.
There is a separate file type in excel 2007/2010 to work with VBA. It is Macro-Enabled Workbook. Make sure that your file type is right.
If you explore the Developer Tab. The below options pertains to the VBA.
The Macro Security must be enabled to work with Macros. If you click at Macro Security above the below dialogue box will appear. Select the appropriate option.
In case you click at VBA the Visual Basic Development Environment will open. This is where your VBA code goes.
- Open new macro-enabled workbook and go to cell B1.
- Next click on Developer Tab and afterwards click on Record Macro in Code group. Record Macro dialogue box will appear. You can change the name of Macro here, but for the time being keep it Macro1.
- When you click on Record Macro, the button changed to Stop Recording as per below:
- Move your cursor to cell A1 and Type ABC Company and In A2 For the Period.
- Next move to cell A3 and stop the recording.
- Delete the values in cell A1 and A3 and run macro by clicking Macros in code group. The Macro dialogue box will open with Macro1 select. Click on Run. It will put the values ABC Company and For the Period in Cell A1 and A2.
Whenever, you run above macro, it always copy the values in Cell A1 and A2. It is absolute reference recording.
In order to view the VBA code, click on Visual Basic icon and click on folder Modules < Module1 if it is not already selected.
Sometimes when you click on Visual Basic icon, the project explorer containing the Modules is not visible. If same is the case with you, click on View and select Project Explorer to see the Module1.
Relative Reference Recording
If you want your macro to run at the place of cursor instead of same cells. You have to on the Use Relative References option. If Use Relative References option is grey, it means it is on or selected.
Now do the recording following the same steps as per above example with Use Relative References On. Run the Macro and explore the VBA code to see the differences.