Friday, January 31, 2014

Excel VBA tutorial 01: record, edit and run macros

A reason for creating this blog, and which I have left aside too much is Excel and VBA tutorials. This will be the 1st VBA Tutorial and is intended for people like my past colleagues who asked me how they could learn VBA to avoid doing the same manual tasks every day.

Purpose

In the world of telecom operators, we care about QoS (Quality of Service). We have a lot of antennas & equipment to supervise of which quality fluctuates day by day. In order to monitor the quality of equipment, metrics are recorded. These metrics are very numerous (hundreds or thousands). Here's some example of what we record in order to monitor quality:

  • call attempts
  • call successes
  • call drops
  • etc.

I'm keeping it rather non-technical since the focus of this article is on VBA, not telecoms.

So, in our network, we have more antennas than we can humanly check every day. And in order to improve the network, or prevent its degradation over time, we want to know which antennas (which we call "cells" as in "cellular network") have a lot of dropped calls. We will therefore create a macro that sorts our data so we can see which cells need urgent care.


Developer mode & keyboard shortcuts

First things first: we need to make sure that "Developer" appears in the ribbon menu. It will make life much easier to access macros.

by default, the Developer tab is absent. But we'll see how to bring it up


In Excel's "big button menu", click "Excel Options".

In the "Popular" category, check the checkbox to show the Developer menu. That's it!



If you prefer using keyboard shortcuts, the following ones will be useful:

  • ALT + F11: brings you to the VBA code window
  • ALT + F8: opens the macro menu, letting you choose which macro you want to run or edit




Recording the macro

For our example, we'll assume that we have files containing very basic data:

  • cellid (identifier of the telecom equipment)
  • drops (number of calls interrupted)
  • calls (number of calls initiated)

By default, our file is sorted by cellid, but we want to see it sorted with the highest number of drops at the top.


Now, think of the way you would sort your data to have the highest number of drops at the top! it would take the following operations:

  • select columns A, B, C
  • go to tab menu "Data"
  • select "Sort"
  • do the sorting: sort by drops, sort on Values, order Largest to Smallest


We will do just that but we will record it in a macro. That means we will

  • start Excel's macro recorder
  • do the manual sorting
  • stop Excel's macro recorder
Start the recorder by clicking the following button in the lower left hand corner of your Excel window!
note: if it does not appear in your Excel window, you can also find it in the tab menu "View", ribbon menu "Macros", submenu "Record macro..."


After you click the "OK" button, your next actions will be recorded in the macro entitled "Macro1" (you can change this name now or later). So just click OK.

You'll notice that the button in the lower left hand corner has changed to this:

Now you can do the complete manipulation and press the square button (stop recording macro) when you're done.


Analyzing the recorded code

Now, we want to see the code that has been produced when recording our moves, and understand what it does. That's how we will understand how Excel works and how we can later do changes to the code to suit our needs.

Open the VBA window by going to the tab menu "Developer" and clicking on "Visual basic"... or you can do the same with ALT+F11.


In the modules, find Module1 and double-click on it. Single click is not enough.

You should get the same content as shown here, except you will have different colors. I have changed my colors in order to have a black background that preserves my eyes after long hours of programming.

So let's see the content of the macro:
Columns("A:C").Select
...that's pretty straightforward. This selects columns A to C.

All the rest is gibberish, but it has the word "sort" in many places, so we can safely think that it's doing the sorting. However, we will notice a few things:

  • it mentions "Sheet1" many times. So if we want to later reuse this macro, we'll have to pay attention to the sheet name.
  • It mentions Key:=Range("B2:B15") as well as Order:=xlDescending. That's obviously the column B (drops) which we are using for sorting data.
  • we see SetRange Range("A1:C15") which is the area containing my data. We see that it is limited to the 15th row. So if someday we have more than 14 rows of data (15 rows minus the header row), the macro might not process data beyond the 15th row... but that can be modified of course.

Change the name of the macro to something meaningful. Maybe it will be like:
Sub SortByDrops()


Checking the result of the macro


Let's go back to the Excel window. Our data is already sorted by drops, so we need to put it in a different order so we can run the macro and check that it does its job.


Sort the data by cellid.

Then open the macro menu (ALT+F8 or from the menu "View", "Macros", "View macros")

We see our macro, so we can simply launch it by pressing the "Run" button.

And voila! You now have your first macro which can sort data.


Further exercise

Now, make a copy of the macro "SortByDrops", which you will name "SortByCellid". With this new macro, we will try and sort data by cellid so that we can easily come back to the initial state of our Excel file.

Modify this new macro by changing
Key:=Range("B2:B15")
into
Key:=Range("A2:A15")

Go back into Excel.

Run this new macro.

Observe the result. It should be "almost" what we were looking for. You can find by yourself what detail is wrong and how to modify this macro to make it perfect.


Conclusion

This is a very basic tutorial. The 2 macros themselves are not very useful but what's very important is that we learned:

  • how to record macros
  • how to check the code and modify it
  • how to run macros 


It might be very crude, but that's truly the starting point for writing VBA macros.

No comments:

Post a Comment

Creative Commons License
Erik Lallemand's blog by Erik Lallemand is licensed under
a Creative Commons Attribution 3.0 Unported License.