Excel Macros

How to use Excel macros

An Excel Macro is a Visual Basic for Applications (VBA) code saved inside a excel document. A macro is a stored series of commands and functions that enables you automate tasks and helps you to prevent repetitive tasks.

From common excel actions like sort, filter, copy, paste, format cells, transferring data to complex formulas etc can be performed by Excel macros.

Excel Macros can be used by  created and used by accessing the Developer Tab in the Excel  Window.

However the developer tab is not added by default in your Excel ribbon.

You can use the Developer TAB to do the below

  • Record macros or Write new macros.
  • Run macros that you previously recorded.
  • Use XML commands, ActiveX controls, Form Controls( e.g Check box, Option Box, Command Button etc)
  • Create applications to use with Microsoft Office programs.

To add the Excel Developer tab, follow below steps

  1. Click the Excel File tab and select Options to open the Excel Options dialog box.
  2. Select the Customize Ribbon option.
  3. From the right list box, check/select the Developer tab
  4. Under Customize the Ribbon and under Main Tabs, select the Developer check box.

Below image shows the Developer tab on the Excel ribbon with the Visual Basic command button.

 

 

 

VBA Editor Window

Pressing Alt + F11 or Clicking on Developer – Visual Basic will open up the Visual Basic Editor Window

Every Excel workbook, has at least one code module associated with the workbook. Also each every worksheet also has a code module associated with it. To open their respective code window just click the particular object. Clicking on Sheet1 will open Sheet 1 code window.

We can also add code in modules which will work on multiple sheets, workbooks together.

Module can be inserted by Insert – Module .

Write your desired code or paste the code from some other source to your module.

 

 

Below are some basic examples of macros

1.Extract Active Employee Data & conditionally format extracted records

http://www.hrknowledgecorner.com/excel-macros-extract-active-employee-data-conditionally-format-extracted-records/

2.Excel Macros Series 2: Calculate the total paid days from the attendance roster for a particular month

http://www.hrknowledgecorner.com/excel-macros-series-2-calculate-total-paid-days-attendance-roster-particular-month/