facebook Instagram Twitter You Tube

How to Create and Write Excel Macros in VBA

When we deal with Microsoft excel there are some tasks that we performs in our day to day work. For example writing excel formulas, copy and paste data, some column updates or entering text or data at various places in the worksheet etc. may be our routine task. Often we do same task again and again in our daily work in office of home. To prevent these repetition and save time we may utilize macros in Excel. Macro is a tool that makes the job automate, saves time, do the job with minimum or no errors. Sometime experts write the macro to make complex task into easy way for their subordinates or who has no knowledge of VBA. For example making a forms to fill data in worksheets. Once the forms are prepared than anyone can fill the data in worksheets.

In short, Macro refers to the Programming language or small piece of code written in VBA (Microsoft Visual Basic Application). Macros are written to automate the task and save time with maximum efficiency. In addition to writing codes we can also record macros and run by clicking the Macros command on the Developer tab on the ribbon or by assigning a macros to the objects such as buttons, links, menus, shortcut keys etc. In this article, we will discuss how to write macros and how to implement Excel Macro.

Pre requisite for witting Macros in VBA:

To write a macros for excel or MS Word you need to have some basic knowledge of Visual Basic Language. Visual Basic in not so much difficult language as compare to others, one can learn basics within a week or two. If you want to learn then google VB6 tutorials, you will find lot of free tutorials at youtube.

Writing your first Macros for Excel:

Open MS Excel Sheet then right click anyware in blank space of Ribbon bar then click on Customize the Ribbon as shown in Picture below (Fig-1)

Customize the Ribbon Tab

Then it will open another window as shown below. Now you have to click on Developer Check box and click on OK Button. (Fig-2)

Find Developer Tab

Now you will see the Developer Tab in your Menu Bar (Fig-3), Click on Macro. Alternatively you can also press Alt+F8 to open window for creating a macro.

Customize Developer Tab

Now In text box write the macro name, you can write any name as you like, then click on Create button. See the image below (Fig-4)

Customize Developer Tab

After Clicking the Create button you will see the VBA editor as shown below. (Fig-5)

Age Group Macro Code

For practice or to test the macro, write the above code in your VBA editor and save it as macro enabled worksheet and now switch the window to your main worksheet and fill the age of persons in Column A cell 3 to 9 as shown in fig 4. Leave the column B blank and run the code to see the result.

How to run the Macro?

Press Alt+F8 to open the window from your worksheet (See the sequences from image below) (Fig-6).

Run Macro Command

First select AgeGroup then click on Option Button and then create a shortcut key by filling any letter or number in blank box to run your macro ( in this example we filled a letter) then close the dialog by pressing OK button. Now close these dialogs and run your Macro by pressing Alt+a key.

You can run your Macro by creating a Button as shown in Fig-4 (SubmitQuery Button). I have already written an article on this topic you can read from here. I apologize for my bed English. Thanks for reading.

Excel Macro & VBA