Sunday, August 15, 2010

Visual Basic's Basics #1

The target audience for this blog is not computer experts, but normal 21st century office works that use computers for their day to day needs, and are looking to increase their productivity through automation. I’m not an expert myself, so what I write may not be the most accurate or efficient way to do things. I just want it to work, and that’s you want too. So, on to the lesson.


The best way to learn about VBA is to record Macros and then look at the automatically generated code. Even if you don't know how to code, you will start to understand how everything works and suddenly you will be confident enough to try to change some lines.

Unfortunately, Outlook doesn't have a native Macro recording feature, so start with Excel.

Note: I'm going to assume you are using Office 2007 or Office 2010, because, really, you shouldn't be using 2003 anymore.

Step 1:

If you don't have it yet, add the Developer tab to your Ribbon. Go to Excel Options.



Step 2:

Go to the developer tab and select “Record Macro”. Enter something in Cell B2 and select “Stop Recording”



Step 3:

To look at the code, select “Macros” and then “Edit”. This will show up:

Sub Macro1()
'
' Macro1 Macro
'

'
Range("B2").Select
ActiveCell.FormulaR1C1 = "something"
Range("B3").Select
End Sub

As you can see, it is quite easy to adapt this code. Instead of "something", you wanted enter "some other thing"? No need to record the macro all over again, just edit the text!

Some explanations:

-“Sub” is used to define and name the macro. “End Sub” is used to, you guessed it, end the macro.

-Text after ‘ is a comment. That means you can write whatever you want, except code. You should use comments to remind yourself (and others that may look at the code in the future) what it is supposed to do. For instance, in my previous post, I wrote:
'Selects incomplete tasks from your default tasks folder

You’ll find out pretty soon that writing clear comments is a good practice. Otherwise you will feel lost when you look at old code, even if it is your own.

-Things in VBA are “Objects”. A menu is an object, a cell is an object, a sheet is an object, a workbook is an object. In this case, Range(“B2”) and ActiveCell are objects. There are many types of objects, and each type is associated to a set of “Methods” and “Properties”.

-A Method is an action that can be performed on an object. In this case, the Select Method selects the object Range("B2").

-Properties describe objects. You are able to change most properties, while some are read only. In this case, FormulaR1C1 is a property, which we change to "something".

Both Methods and Properties are used with a specific object, separated by a period (object.method or object.property).

There some others concepts in VBA programming, like Functions and Variables, which we will talk about in the future, but these are the most important for you to start to understand Visual Basic.

No comments:

Post a Comment