Saturday, January 31, 2009

VBA Macro Excel Tutorial: Objects and Collections

An object is a special type of variable that contains both data and codes. Some object can contains other object. For example, the Application object (or, Excel itself) contain others object, that is Workbooks. And a Workbook contain other object, such as Worksheets.

A collection is group of object of the same class. Workbooks is a collection of all Workbook object and Worksheets is a collection of all Worksheet object. To reference a single object from collections, you can put the object's name or the index number, like this:

Workbooks("Book1") or Workbooks(1) if Book1 is the first workbook in the collection.

VBA Macro Excel Tutorial: Object Properties and Methods

Object Properties
Property is a piece of information that is associated with an object (for example, the color of you hair). Where "hair" is the object and "color" is the property. Some objects are read-only, meaning that you can read the value but you can't change it. Others are read-write, where you can read and change the properties.

For example, the following codes will set value of cell B3 to "VBA Macro Excel".

ActiveWorkbook.ActiveSheet.Range("B3").Value = "VBA Macro Excel"

And this code bellow will display the value of cell B3

MsgBox ActiveWorkbook.ActiveSheet.Range("B3").Value

Object Methods

A method is an action that the object can perform. For example, methods for a car would include "speed up", "speed down", and "stop".

If you execute this code, cell B3 on active sheet will be empty.

Sub clearCellB3()
End Sub

VBA Macro Excel Tutorial: Recording Macro Excel

Macro recorder

Macro recorder is Excel built-in tool to convert you excel actions into VBA code. Macro recorder is a powerful and useful tool, but it has some limitations:

  1. The macro recorder can’t record complex actions.
  2. Macro recorder can’t generate code that performs looping, conditional statement, and so on.

Recording Macro

In this example we will record a macro that will merge selected cells. To record the macro follow these steps:

  1. Open a new blank workbook.
  2. On any worksheet, select cells from A3 to C3.

  3. Click Tools –> Macro -> Record New Macro...

  4. In the Record Macro dialog box, type “SubMergeCells” in Macro name textbox to set the macro name. Then click Ok.

  5. In the formatting toolbars, click Merge and Center button. This action will merge cells from A3 to C3.

  6. To stop recording macro, click the Stop button.

Run the recorded macro

To execute the recorded macro, follow these steps:

  1. On any worksheet, select cells from D4 to G4.
  2. Click Tools -> Macro -> Macros... or press Alt+F8.
  3. In the Macro dailog box, select “SubMergeCells”. Click the Run botton to run the macro.
  4. Cells D4 to G4 now have been merged.

Friday, January 30, 2009

VBA Macro Excel Tutorial: Creating Your First VBA Macro Excel

In this section, I will show you how to create your first VBA Macro Excel. In this example, we will create a "I Love Excel!" message box. To create the macro, please follow instructions bellow:

1. First step, open your visual basic editor by clicking Tools -> Macro -> Visual Basic Editor

2. Click Insert menu, and then select Module to open module window.

3. In the module window type following code:

Sub showHello()
   MsgBox "
I Love Excel!"
End Sub

4. To run your first macro, click Run -> Run Sub/UserForm or by pressing [F5] key.

5. "I Love Excel!" message box will appear in your screen.