Excel Automation Using Macros

Excel Automation

Excel contains a pretty powerful, but a rarely used functionality. It is the ability to create automated tasks and custom logic using macros. Macros are an ideal way to save time on routine, repetitive tasks.

Eager to learn about Excel macros or how to create macros. I will give you an overview of the entire process of creating a macro.

Excel Macros – What’s that?

A macro is a piece of code written in Visual Basic for Applications (VBA) saved inside a document. VBA is the language used to write code for macros. This code cannot operate outside of Microsoft Office environment.

Macros are very powerful and can do almost anything that you can think of. The following are some of the functions that a macro can help you with:

  • Apply styles and formatting to cells.
  • Manipulate cell data.
  • Get data from external sources like databases, text files, etc
  • Create entirely new documents.

How to create a Macro – An Example

Let us begin with a simple Excel file. A simple file consisting of data for 10 students and 4 subjects. For each of the students the marks are between 1 to 100. Through our macro we will produce a data sheet with the following features

  • With proper formatting and
  • which includes summary totals for each row and a totals row.
excel automation, Excel macros, visual basic for applications

As mentioned above, a macro is a piece of VBA code. But one of the good things in Excel is that you can create or record a macro without actually writing any code. This is what we will be doing for this article. We will record a macro.

Developer Tab

To begin with ensure that the developer tab is visible in the toolbar as follows

If the developer tab is not visible, follow the following steps to activate it.

  1. Click on File > Options(Alt+F+T keys)
  2. You get the following screen
  3. Ensure that the developer tab is selected on the right hand side as shown in the above image. Click on OK.
  4. Now the Developer tab should  be visible as follows

Excel Macro example

To create a macro, go to Developer >  Record Macro.

Give the macro a name FirstMacro (ensure there are no spaces in the macro name) and click OK.

Once you click OK, all your actions will be recorded. In short every action that you do in the Excel is captured in the macro functionality.

There are a couple of places where Excel will show it is in macro record mode. One is by viewing the Developer menu. As you can see in the following image, the Stop Recording button has replaced the Start Recording button.

The other is in the bottom left corner of the status bar. The ‘stop’ icon (indicated by the red square in the image below) indicates that the macro mode is on. And pressing this button will stop the macro recording.

Now that we have started recording our macro, let’s apply some summary calculations. First add the headers in columns F to I as shown in the following image

Next, apply the following formulas

  • =SUM(B2:E2), in cell F2
  • =AVERAGE(B2:E2), in cell G2
  • =MIN(B2:EK2), in cell H2
  • =MAX(B2:E2), in cell I2

Now, highlight all the calculation cells from F2 to I2 and drag down till the last row of our data in row 11. This will copy the formula to each of the rows.

Once this is done, each row should display their respective summaries as shown in the below image

Now, we want to get the summary data for the entire sheet

So, we add the following formulas

  • =SUM(F2:F11), in cell F12.
  • =AVERAGE(B2:E11) in cell G12. Since we cannot do the average of the row averages we take th average for the entire data.
  • =MIN(F2:F11), in cell H12.
  • =MAX(F2:F11) in cell I12.

Now your screen should look like the following image

Next, we need to format the cells. 

  • Select all cells from A1 to I12 and apply general number formatting and click on the comma style icon
  • Next select the column headers(B1 to I1) and the row headers(A2 to A11) and the totals line(F12 to I12)
  • Make them bold and centered and give a background fill colour
  • And finally, apply the same formatting to the totals as shown.

When all is finished, this is what our sheet will look like

Since we are done with our tasks, stop the recording of the macro.

Congratulations. You have just created your first Excel macro.

To use our newly recorded macro, we have to save our Excel Workbook in a macro enabled file format. Click on File Save As. In the save as dialog, select the Save as type as in the following image.

Once you have saved the file as a template, go ahead and close Excel.

Executing an Excel Macro

To see the macro in action, do the following 

  • Clear the formatting from the cells B1 to E1 and
  • Clear the formatting from the cells A2 to A11
  • Clear the data and formatting from the cell E12
  • Clear the data and formatting from the cells F1 to I12

To use the macro, go to the developers Tab and select View Macros

You get the following dialog

From the Dialog, we see the “FirstMacro” macro we recorded above. Select it and click Run.

Once the macro starts running, you will see that the formatting of the cells changes just as we had recorded it. Once the macro is finished, the data  will look as follows

A word about macros

VBA code is actually very powerful. It can manipulate files outside of the scope of the current document. For example, a macro could modify or delete random files in your data folder. As such, it is important to ensure that you only run macros from sources that you can trust. When you open a macro file from untrusted sources you get the following message

How does a Macro work ?

When a macro is recorded, the following happens. Internally, Excel converts every action that the user performs into the corresponding VBA code.

To view the code that makes our macro run, from the Macros dialog click the Edit button.

On clicking the Edit button, we get a window (the following image)that displays the source code. This is the source code that was recorded from our actions when creating the macro.

From this code window you can 

  • edit the code to suit your requirements. 
  • You can create new macros.

The VBA macro that we used in this article will likely fit simple needs of the users. But for more customized actions you would be required to edit the generated code or write new code from scratch.

What we covered in this article was just a glimpse of the power of VBA code. I encourage you to explore VBA programming in detail. To use the power of macros to help you in your day to day activities.

Links to old posts
VLOOKUP training
Gantt Chart template
INDEX MATCH Function

Leave a Comment