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

The Object Browser enables you to see a list of all the different objects with their methods, properties, events and constants.
 

In the VBE editor:

1.            Insert menu and select a Module.

2.            Click on the View menu and select Object Browser (shortcut key: F2).

3.            Make sure it’s set to '<All Libraries>'.

Notice Add([Before], [After], [Count], [Type]) is one of the examples previously seen.

The main two panes contain on the left Classes (also known as Objects) and on the right Members (also known as Identifiers).

By selecting a class, you display its members which are of three key types; Properties, Methods and Events.

 Libraries

Libraries are the application divisions of a collection of classes (objects). Therefore, you will have a class for Excel, VBA and other applications you wish to have a reference to. The Excel (the host), VBA and VBAProject are mandatory and can not disabled. All other library files can be switched on or off as required.

In order to code to another applications (for example, Microsoft Word) you will need to load its library first.

To switch between libraries or show all libraries, choose the ‘Project/Library’ drop down box:

The default libraries available:

1. Excel – A collection of classes available in Excel i.e. workbook, worksheet, range, chart, etc…
2. Office – A collection of classes generic to all office applications i.e. command bar, command icon, help assistance, etc…
3. stdole – A collection of standard OLE classes which allow other OLE applications to share information (Not covered in this manual).
4. VBA – A collection of classes which allow generic functions to be used i.e. MsgBox, InputBox, conversion functions, string functions, etc…
5. VBAProject – A collection of classes local to the active workbook project, which includes sheets, workbook and any user, defined classes.

Other libraries are also available but require to be enabled before they can be used which include Word, Outlook, DAO, ADODB and many others.

By enabling the additional libraries, developers can start to code and communicate with other applications and processes, which start to reveal the potential power of Visual Basic (VBA).

To enable a library, from the Visual Basic Editor, choose Tools menu and select References…

Scroll down to find the required library or choose the Browse… button to locate the required library.

Excluding the top two libraries, a library priority order matters that is why users can re-arrange the order using the Priority buttons. The way the system works is when a procedure is executed, it checks to see which library is required in order to execute the line-by-line code.  In some cases, a method or class can be duplicated between libraries and it is therefore important to be able to call the correct method or class first superseding the lower level references.

 Structure of  a Library

Each Library will typically have a collection of classes. A class or object class is in essence the object i.e. Worksheet.

Each object class will have a collection of members, which could be a collection of properties, methods and events.

When looking at the Object Browser, users will see on the left hand side many classes to the active library. To the right hand pane, all members of the selected class will reveal properties, methods and events.

The above illustration shows the Excel library, Worksheet class and the Visible property highlighted (Excel.Worksheet.Visible).

Right mouse click the selected item to choose the Help command and go straight to the offline help documentation.

Browsing the right hand and pane of the Object Browser, users will see three different icons to help identify the member type:

 Browser Searching

The search tool allows users to locate if a keyword firstly exists and secondly where it could possibly reside

At the top half of the browser window, type the desired keyword and then click the search button:

The above example looked for the keyword visible across all libraries.

After locating the correct item (selecting the item), users can use the copy button function and then paste into a code window.

Next Topic: Chart objects