VBA MODULE 4


Macros


Macro is a program or set of instructions which we create in VBA language to automate the task in Excel. All the programs which we write in VBA are Macros.
There are two ways of creating a Macro 
First by Recording a Macro 
Second by writing the code in the VBA editor window, this can be opened by pressing ALT + F11.

RECORDING A MACRO


By using Record Macro option provided in the Developer Tab we can capture or record our all activity which we perform in Excel.

Let’s consider a simple example


1. Go to Developer Tab and click on Record Macro button
2. A Record Macro window will pop up on the screen, so give a name to your macro and press OK.
3. Choose any cell in the sheet, write your name, make your name bold and change the color of the cell.
4. Now go to Developer tab and click on the Stop Recording button and your first macro is recorded.

To check your recorded macro, go to a new sheet in the same workbook and press ALT + F8 to get the list of all recorded macros. Select your macro, click on run and you will see your activities getting performed automatically. This is the power of VBA which automates your daily work by few clicks in a hassle free manner.
You can also check the code created by VBA itself while you were recording your macro. To check the code press ALT + F11, VBA editor window will open. At left side you will see the Project pane and Modules folder at the bottom of it. Expand the folder, click on Module 1 and the code will be visible in the Code Window at the right side.
Now this code which you see is little complicated and have more lines but it is good to understand the concept of VBA macros. We can make this code very short and simple by writing our own macro manually in this VBA editor window.

WRITING YOUR OWN MACRO


We can write our own macros to simplify and customize our code as per the understanding and knowledge.

See the simple example of a macro


This macro will simply bring a pop up window displaying “Hi” on the screen
1.Select a new sheet click on Insert button inside the Developer Tab
2.Select the command button from ActiveX control section and expand the button using your mouse.
3.Right click on the button, click property and change caption to give the name to the button of your choice. You can also change the name field in the property tab to give any name to your macro.
4.To make this button working using code, double click on the button and the code window will open.
5.Now the sub-procedure outline will be displayed, you can complete your code by simply adding one line in between. See below

Private Sub CommandButton1_Click()
MsgBox "Hi"
End Sub


Go back to your sheet and click on that button which you created to run your first macro.

© Snagoff. All right reserved.