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

When you add a chart when recording a macro, the code generated follows the menu and command that users manually call when adding a chart which means there is a discipline to run the macro in exactly the same way or face the potential of landing up with different results or even errors.

Typical code for a recorded macro:

The other issue with the above example code is typically the reference to the source data (which is an absolute string reference Sheet2'!$A$2:$D$12). The user may want more flexibility in controlling where this reference is by using and passing variables.

Note: Previous versions of Excel records macros using the object and method Charts.Add but it still gives the inflexibility in terms of control.

The above code generates a standard size chart within a worksheet and there is no room for setting properties until users edit the properties of an existing chart which just adds more code and becomes inefficient.

 Create Chart Objects

By creating your own written procedure and introduce ChartObjects keyword with supporting methods and properties, you have more control and can be flexible in passing arguments thus reducing extra lines of code.

An example:

The above example allows the chart object to be positioned and sized (measured in pixels) accordingly using the Add method and its arguments.

Defining object variables for longer based procedures makes the code more clinical and efficient to write even though we must first declare a new object (as ChartObject).

The above example is the same as the previous code snippet but using the object variable ChrtObj as declared and set.

The other useful method is SetSourceData as you can add as many series as required (one at a time) enabling what ranges you want to set and not let Excel make as assumption.

 Adding Series

When recording a macro adding a series each line of code is created for a name, y-axis values and x-axis values if required using SeriesCollection and NewSeries keywords.

An example of recorded macro which adds three series (names and values) and an y-axis to an existing empty chart on a worksheet:

Using the With...End With statement will refine the code and make it easier to understand. Also, introducing your own objects for a series just gives you better control should you wish to assign variables and arrays to it.

The same as the above example but a better practice:

Remember, you can pass variables better into the above example code (not illustrated).

To delete a series use ActiveChart.SeriesCollection(1).Delete where the index (brackets with a 1) represents the first series for the active chart.

Using arrays and collections, you have better control especially when wanting to handle multiple charts in one go.

Data used for the above chart:

 Chart object looks something like:

Next Topic: Pivot Table objects