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

There are three locations to choose from which affect the scope and availability of a macro:

1. This Workbook

2. New Workbook

3. Personal Workbook

1. This workbook will store macros in the current workbook, which recorded the macro and is said to be a local macro.

That will mean, every time users want to run the macro, they will first have to load the file and then execute the macro.

2. New Workbook will store the macro to an unsaved new file and is generally used for distributing to other users which they would need to load and run manually. Treat this as the same scope for that of This Workbook and is deemed as a local macro too.

3. Personal Workbook is a specially reserved named file which is generated (first time around) automatically to store the recorded macros.

The name given to this file (which is still treated like any other Excel file) is Personal.xls/xlsm/xlsb.

This file is hidden by default as it is not intended to be used as a normal spreadsheet.

The location of the file is important and must reside in the XLSTART folder of where the user's profile or Excel application is installed.

This special path responds to the loading event of Excel and loads any file stored in this folder. Therefore, macros which are stored in the file in the path XLSTART will open too.

Macros that require a global use are stored in this type of file (i.e. User Defined functions).

The full path and file for the default installation of Excel would look something like the following:

Excel 2007 (Windows Vista)

C:\Users\Ben\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB

Excel 2003 (Windows XP)

C:\Documents & Settings\Ben\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLS

Next Topic: Reasons to write macros

Note: The full path to locate folders to the PERSONAL file may be hidden and will require some modification in Windows Explorer to view them.

Also check with your IT administrator if this has been restricted.