VBA HOME PAGE

Menu

Recording macros

Looking at the code

Ways of running macros

Where macros are stored

Reasons to write macros

Writing macros

Procedure types

Visual Basic editor (VBE)

Rules & conventions

Excel objects

Range/Selection objects

Object hierarchy

Object browser

Chart objects

Pivot Table objects

Formulas

Visual Basic Functions

Creating Add-Ins

Variables & constants

Object variables

Arrays

Collections

Message Box

VBA Input Box

Excel Input Box

Making decisions (If)

Making decisions (Case)

Looping (Do...Loop)

Looping (For...Loop)

With...End With blocks

User defined functions

Event handling

Error handling

Debugging

Creating User Forms

DAO/ADO Objects

Input/Output Files

Other links

Example code snippets

Userform input example

VBA code is stored in a module which is part of the Excel workbook but is viewed via the Visual Basic Editor (VBE) interface.

1. Click the Macro icon from the Developer tab.

2. Select the macro you wish to view.

3. Click on the Edit button.

Now look at the differences between the absolute and the relative macros.

 Absolute Macro

Range(“A1”).Select

In plain English, this means, “click on the cell A1”.

ActiveCell.FormulaR1C1 = “ABC Ltd”

In plain English, this means “enter the text ABC Ltd into the active cell”.

 Relative Macro

Activecell.Select

In plain English, this means, “click on the active cell”.

ActiveCell.FormulaR1C1 = “ABC Ltd”

In plain English, this means “enter the text ABC Ltd into the active cell”.

ActiveCell.Offset(1,0).Range(“A1”).Select

In plain English, this means, “select the cell one row down, but stay in the same column”.

 OffSet (Keyword)

Offset is a command used in relative macros, which allows you to select a particular cell in relation to the active cell.

	Offset(-1,0) Move one row up, but stay in the same column.
	Offset(0,1) Stay in the same row, but move one column to the right.
	Offset(1,0) Move one row down, but stay in the same column.
	Offset(0,-1) Stay in the same row, but move one column to the left.

 Unnecessary Code

As previously mentioned, recording macros does create a lot of unnecessary code. 

In this example, the Range("A1") that appears in rows 3, 5, 7, 9 and 11 of the relative macro is unnecessary and can be removed.

Next Topic: Ways of running a macro

Tip: Alt + F11 function keys switches between Excel and VBE window.

VBA Keywords: ActiveCell, Range, Selection, OffSet.

Note: For further details and other members, see Range/Selection objects.