Sunday, April 05, 2009

"Mind" -> Visual Basic For Excel

The age old fantasy for everybody is to have a personal butler to help oneself around the house.

In the very popular movie "Batman Begin," Alfred is the rock solid helper that assists the Batman in the times of greatness need. Not only that, but he is also taking care of the menial duties of the house so that Bruce Wayne can spend his time on better things, like saving the city from the clutches of the evil villains that beset it.

So, what does this have to do with Excel?

Chances are if you are in business and you do any work, you will be using Excel for something. It may be doing the very simple to the very complex, but you will be using it for something. Now, if you are using it on a regular basis, chance are that there is something that you do with it on either a weekly or a daily basis. Here is where the butler idea comes in. It is exceptionally easy to set up the ability for that spreadsheet to do your repetitious tasks automatically, like a personal butler for your spreadsheet.

However, you will need to use some called "Visual Basic For Applications" or VBA. I was reading an excellent blog by Jorge Camoes, who is a quant head at a company and spends a lot of time blogging on charts, and very much enjoyed his casual observation:

In an informal survey among friends and colleagues (all of them Excel users), I’ve discovered that 55% doesn’t know what VBA is, 40% knows but doesn’t want to use it, 4% uses recorded macros from time to time and 1% actually edits the recorded macros to add some sort of functionality (well, this happens to be me…).

"Oh, no," you might say. "This sounds complicated. You just told me that 40% of people don't even know what this thing is. Why should I be interested?"

Well, the first step is just you out of the class that has never heard of have heard of Visual Basic For Applications (VBA). VBA is the backend (or the device) that allows you to record macros in Excel. So, congratulations, you are now more informed than 55% of the population.

Let's talk a bit about macros. Macros used to be very, very difficult. Now, they are very, very easy.

Macros are the short way of saying macroinstruction. Since I'm an aging computer geek, I want to take you back to the early days of computing to the old "Terminate and Stay Resident" (or TSR) programs. Borland International stole this idea from a start-up and created "Superkey."

If you think about it, there were no mice on the early computers. (I know that this boggles the mind.) The way that you interacted with computer was the "Command Line Interface" or CLI. The amout of clicks that you can do from the keyboard if you are a very fast typist is 480 per minute. (think 80 words per minute at an average length of 5 letters per word and one space.) While this is extremely fast for a typist, this is very slow for a computer.

If you had a series of keystrokes that happened over and over again, you could simply capture them and replay them. The computer doesn't know if it is a human being or a program replaying the key.

The application providers realized that this really wasn't worth a stand alone program, and they started to integrate these key recorders into the applications that they made. This is when I was introduced to macros, through the DOS environment. However, the macro environment goes back earlier than this.

When I got my second degree is when I discovered Unix. Unix has two very popular editors vi and Emacs. What is an editor? Think of it as a limited word processor made specifically to write scripts or computer language type instructions. Of these two programs, Emacs is of special interest. First off, I prefered it to vi, but even more important for our story: it stands for Editing MACros. See the real computer geeks were on the Unix side, and they were working on things far before it ever came to the DOS (and later Windows) world.

I sold computer in the early days of the personal computer revolution, and when Lotus 1-2-3 2.0 came in, it had the ability to use macros. People went crazy building all types of unique additions to Lotus using the ability to replicate end user keystrokes.

The successor to the general purpose keyboard macros programs can still be found today. The most widely used free software is Autohotkey. The program is not what I call as directly friendly, as you need some ability to program. However, I recently decided that I needed to trim the first 30 seconds of of 100 podcasts. I had a program that would trim one, but it had no ability to do multiple files. So, I set up an autohotkey script to act as if it was me. It opens the files in my trimming program. It trims the file. I copies the file into a "done" folder, and then it opens the next file. You can watch it operated as it does things on my screen. It is almost spooky.

Back on subject, applications started the ability to bring in keystrokes into the programs. In a flash of brilliance (and it revolutionize the app industry) Microsoft determined that it could capture those keystrokes and convert them into a series of programming steps.

Let's use an example. Let's say that you want to save a spreadsheet. You know that the keyboard short cut is "Alt-S" and typing in "temp" and hitting enter.

The old keyboard macro would have captured:

1. Alt key down (Make code in keyboard talk)
2. s key down (Make)
3. Alt Up (Break code in keyboard talk)
4. s Up (Break)
5. t (Up and Down)
6. e (etc)
7. m
8. p
9. Enter

What Microsoft does is change this automatically to a programming language call Visual Basic. Once it records your keystrokes, it say that you did the following command:

ActiveWorkbook.SaveAs Filename:="c:\temp.xls"

Now, programming is always a bit hard to learn, but if you can learn a few rules (about 30-40 hours worth of study), but once you learn the syntax, you can open up a window and say, "I want to take the data from sheet 5 in my spread sheet. Then I'll make a Pivot table out of it. Then I'll make a button to come up to the various parts that I want to see. Then I'll save the document."

The trick part of working with VBA the first time is understanding how to refer to things and all the command to do something. Like most modern programming languages, the language has a lot object orientation in it. This make is simple to program once you understand how it thinks.

We can understand some of this by simply looking at the line of code above. What did we want to do? We wanted to save a worksheet (file), which is obvious and object. In VBA, the currently opened spreadsheet is called ActiveWorkbook. This is always an object. This object can do various actions (think verbs). The object can open. The object can close. In our case, the object will "save as". These actions are called "methods" rather than actions. If you want a object to do a method, you list the object, then you tack on the method with a period. Thus this is why we have

Activeworkbook.SaveAs (or object.method)

We are obvious lacking a file name, and if you put in a space and a "filename:=", it knows to assign the file name whatever you put behind the := in the syntax.

The other tricky thing is that most objects have properties. For example, a Pivottable has pull down menus for setting data that you want to see. In this case, you need to tell the program to find the workbook, then find a particular sheet in the workbook, then find a particular pivottable, then find the attribute or property of one of the items on the pivottable. While this sound very complicated, you basically just type down what we said.

ActiveWorkbook for the workbook
Sheets("MainPage") for the sheets inside of the workbook, and you named the sheet

This is listed out as


In this case, this pull down menu (PivotItems) has several things like "Sales in Feb" and "Sales In March". You can then say "please set this item to always come up "Sales in March".

However, in most cases, you don't type this in. You record yourself doing the action in Excel, then after you are done recording, you look at the commands. Simply selecting the pull down menu with record a line like the above one. While a bit complicated at first, with some training you can quickly see what it is doing. Then you can make changes to the code to modify the actions.

I know that this has been a short glimpse into VBA, but I want to give you a bit of the history and what it is doing as an overview. As stated before, it'll take 30-40 hours to learn enough to start writing a bit of code to automatically do stuff, but once you have this tool, you'll be many more times productive.

No comments: