VBA Charts- Basic Operations

The Chart Object in Excel VBA represents the collection of all the charts sheet present in a workbook. A chart can be either an embedded chart or a separate chart sheet. The Chart object excludes all embedded charts and is only represented by a Chart object. A Chart Sheet contains a single chart and covers the entire worksheet.

To create a chart, we need to have excel data. Here, we have taken a dummy based on the survey of student’s habits of various courses.

VBA Charts Basic Operations

Based on this data, we will perform basic chart’s properties and methods that are useful in day to day Excel life.

  1. VBA to Delete All Charts
VBA Charts Basic Operations

Many times, we encounter a situation where too many chart sheets have been created at our excel worksheet. In that case, we have to manually delete every chart sheet. Thus, this job becomes time-consuming and tedious. So instead of practicing the manual effort, we can do the same thing with VBA code and can get all the chart sheets deleted with a single click.

Program:

The above program will throw a runtime error if it runs for the second time as it doesn’t have any charts to be deleted. So we will manage it with error handling. Here in the below program, we have used the basic error handling by using ‘On Error Resume Next’.  If an error has occurred, it will resume the next line else it will happily run the code.

Program:

  • Selecting a Block of Cells

When you pick an only single cell in a list, but Excel uses the entire region of data to create the chart. But sometimes there is some specific requirement where you only want to pick some specific rows.

Program:

Output

VBA Charts Basic Operations
  • Selecting Non-Contiguous Columns

It is slightly more complicated than selecting the block of cells. Now let’s suppose you want to include the course and only the Dancing habit. But the challenge here is they are not contiguous. So, we have to select everything from A4:A16 and then D4:D16 in this particular worksheet.    

Program:

VBA Charts Basic Operations

Output

VBA Charts Basic Operations
  • Selecting Non-Contiguous Rows

It is bit more time consuming to select individual rows, but it is easy to implement. For instance, we only want to fetch the habit record for BTECH, MTECH, and MBA students. We will choose the BTECH range all together i.e. ‘A4:D6’, for the MBA range we will select ‘A11:D11’, and for MTECH, the range would be ‘A13:D13’.

Program:

VBA Charts Basic Operations

Output

VBA Charts Basic Operations
  • Selecting Dynamically the End of the List

In a real-world scenario, dynamic computation and implementation is more preferred. When we deal with a bulk amount of data, we are usually not sure about the total number of rows present in the list. Suppose we have to create a first for first to the column, but we are possibly not aware of the number of rows. So, we can use some standard selection tricks, which we have also covered in the previous Excel tutorial.

Program:

VBA Charts Basic Operations

Output

VBA Charts Basic Operations
  • Union Method

Many times we encounter a situation where we dynamically select the non-contiguous columns. Let us suppose we have to choose everything dynamically from column A and column D but not from Column B or Column C. Hence, it becomes bit complicated and tricky. But this problem can be done easily by using union method. In this method, we will effectively join a range of non-contiguous cells to form a single contiguous range. Through this method, you can specify up to 30 different ranges of cells.

Program:

Output

VBA Charts Basic Operations
  • Editing a chart using Chart Wizard

Once you got a reference to a chart, there is a range of methods and properties one can use to edit the charts in various different ways. If you are well versed with excel, you will recall that when you created a charts, there was step by step wizard dialog box followed in choosing the chart type, the range of data to be used, the titles and the axis labels, etc. Similarly, the chart wizard method in VBA allows us to do exactly the same thing. In the below program we will see how to set up the source data using the chart wizard method.

Program:

Output

VBA Charts Basic Operations
  • Changing the chart type

One of the important things about the chart is choosing the appropriate chart type.  By default, Excel provides the standard clustered column chart. But one can change the chart type as per the data representation. VBA provides a wide range of chart types option to select.

VBA Charts Basic Operations

Program:

Output

VBA Charts Basic Operations
  • Adding a Legend and a Title

The title field represents the Heading for our chart. Firstly, you have to enable the hasTitle property and then in the ChartTitle.Text property specify your Cart’s title.

When we have more than one series of data, the legend will describe the color-coding key, which indicates each series of data values. The user only needs to specify the hasLegend method to TRUE. The VBA will automatically specify the legend fields.

VBA Charts Basic Operations

Program:

Output

VBA Charts Basic Operations
  1. Add2 Method

Add2 method adds another chart to the collection of charts, but it has got different parameters. If its parameter layout is set to true, it will use the new dynamic layout to add the elements of the charts. This method is slightly different than the Add method as in this, the chart title is automatically created, and it also includes the legend (only appear is it has more than one series) in a slightly different position.

Program:

Output

VBA Charts Basic Operations

Pin It on Pinterest

Share This