## Bullet Charts in Excel (updated and simplified)

People have struggled for a long time to find effective means of displaying single points of data. With the recent obsession with dashboard displays of business data, this struggle has come to a head. On one side, there are myriad gauge designs; on the other are more effective charts, notably Stephen Few’s Bullet Graphs. In this tutorial I’ll show how to make bullet graphs in Excel, using a simplified approach that works for horizontal and vertical bullet charts.

## Gauges

Gauge graphs are based on the numerous dials found in automobile dashboards and aircraft cockpits. These dials make sense in a mechanical setting: they are activated by small voltages generated by sensors in the equipment. In the gauge, this small voltage results in rotation of a needle around a pivot. These gauges are reliable and inexpensive, compared to comparable linear electromechanical gauges. These dials also make sense in a cockpit: you need to know what’s happening right now, and in general you do not need to compare the values on several dials.

Some business dashboard designers have missed the point. Instead of treating a dashboard as a place where a lot of information is displayed in a limited field of view, they apply the metaphor of cockpit too literally, as if the manager is driving his desk around the company. Dial gauges in a business dashboard are remarkably ineffective. They take up a lot of space to show very little information, and comparisons among several gauges are difficult to judge with any precision. They also show only a “right now” value, rather than a historical view afforded by line charts. It seems that more design effort has gone into making them gauge-like rather than making them comprehensible.

Yet so-called Business Intelligence dashboards are loaded with gauges. BI graphics packages are full of them, each more glossy and glittery than the next.

## Bullet Graphs

To address many of the problems with gauges, Stephen Few started with a simple bar chart and developed Bullet Graphs. Bullet graphs require less space than gauges to show the same amount of information, and their linear style makes it easy to compare values on adjacent bullet graphs. Stephen describes bullet graphs in Bullet Graphs for Not-to-Exceed Targets and has published a Bullet Graph Design Specification.

## Vertical Single Bullet

A bullet chart requires five values: high, medium, and low background values, a target, and an actual value, as in the table below. Insert a clustered column chart, and make the chart narrow or adjust it later (middle); in most cases you won’t need to go to the extra effort of making a stacked column chart. Excel assumes that one column of data should be plotted as a single series, so click the Switch Row/Column button on the Chart Tools > Design ribbon tab (right). Format High, Medium, and Low as light to dark gray bars. I’ve formatted Actual as a dark blue bar (which will show up slightly better on screen than a black bar), and Target as a red bar, but these formats will have to be recreated since their chart types will be changed.

Right click the Actual series, and choose Change Series Chart Type. Select the Line chart type with markers, format its marker as a square with dark blue border and fill, and format it to use No Line (left chart below). Right click the Target series, and choose Change Series Chart Type. Select the XY chart type with markers and no lines, and choose the dash marker using red fill and a red border with thickness of 1.5 instead of the default 0.75, which will eventually look better in the legend (middle chart below). Since Excel automatically plots Actual on the secondary axis, format the series to appear on the primary axis (below right).

Note that all of the above three steps can be accomplished in Excel 2013 in one trip to the Chart Type dialog, but earlier versions need three different formatting steps.

Format any of the remaining column chart series (High, Medium, or Low) and set the Overlap to 100 and Gap Width of 50 (below left). Add a legend (middle). For this thin chart, Excel may make both the chart and the legend too narrow, so adjust the width of the plot area and the legend so both fit nicely (below right).

Select the Actual series and add error bars. It’s a line chart series, so you only get vertical error bars (first chart below). Format the error bars to show the minus direction only with a percentage of 100, without end caps, using the same dark blue for the line, and make the line thick (such as 7.5 points in the second chart below).

Select the Target series and add error bars. It’s an XY scatter chart series, so you get horizontal and vertical error bars (third chart below). Delete the vertical error bars, and format the horizontal error bars to show both directions, at a value of 0.3, without end caps, with a 1.5-point red line.

Sometimes the default error bars are too short to see, so you need to use the dropdown in the top left of the Chart Tools > Format or Layout tab to select the set of error bars you need to format.

Now comes a little bit of Excel Chart Magic. Error bars do not show up in the legend, so the dark blue and red symbols for Actual and Target would disappear. But we went to the trouble of formatting our markers to look good in the legend.

If we format individual points in a series differently than we format the entire series, the formatting for the entire series stays in the legend. We need to format the individual Actual and Target points differently, by selecting the individual points and choosing No Marker. Here’s how:

Click on the marker for Actual, which selects the whole Actual series. Then click on the marker again or click the right keyboard arrow key, which selects just the single point. Now press Ctrl+1 to format the point. If the title of the format dialog or task pane says “Format Data Point”, you are ready to go. If it says “Format Data Series”, you need to try again to select just the data point. In the Format Data Point dialog, choose No Marker for Marker Style. This hides the Actual marker for the data point in the chart, but leaves the marker in the Actual legend entry.

Repeat the single click plus single click technique to select the marker for Target. In the Format Data Point dialog, again choose No Marker for Marker Style. This hides the marker for the data point in the chart, but leaves the marker in the legend.

The result is the left chart below. Of course, if you don’t need a legend, you could have skipped all the marker formatting we did before, not shown a legend at all, and made the bullet chart even slimmer (below right).

## Vertical Multiple Bullet

The protocol for multiple vertical bullets in a single chart is the same as for a single bullet, with minor adjustments. You need one column per bullet, with High, Mid, and Low, plus Actual and Target for each. The High, Mid, Low, and Target can be the same or different for each bullet. From the data below left, insert a clustered column chart (below middle), and if necessary switch rows and columns so each category (Alpha, Beta, and Gamma below) has its own bullet components.

Right click the Actual series, and choose Change Series Chart Type. Select the Line chart type with markers, format its marker as a square with dark blue border and fill (left chart below). This messes up the legend, but remembering to set the line to No Line fixes it (below right)

Right click the Target series, and choose Change Series Chart Type. Select the XY chart type with markers and no lines, and choose the dash marker using red fill and a red border with thickness of 1.5 instead of the default 0.75, which will eventually look better in the legend (left chart below). Since Excel automatically plots Actual on the secondary axis, format the series to appear on the primary axis (below middle). Format any of the remaining column chart series (High, Medium, or Low) and set the Overlap to 100 and Gap Width of 50 (below right).

Select the Actual series and add error bars. It’s a line chart series, so you only get vertical error bars (left chart below). Format the error bars to show the minus direction only with a percentage of 100, without end caps, using the same dark blue for the line, and make the line thick (such as 7.5 points in the right chart below).

Select the Target series and add error bars. It’s an XY scatter chart series, so you get horizontal and vertical error bars (left chart below). Delete the vertical error bars, and format the horizontal error bars to show both directions, at a value of 0.3, without end caps, with a 1.5-point red line (below right).

Now we repeat our little bit of Excel Chart Magic.

Click on the Actual series, which selects the whole series. Then click on the first marker or click the right keyboard arrow key, which selects just the first point. Now press Ctrl+1 to format the point. If the title of the format dialog or task pane says “Format Data Point”, choose No Marker for Marker Style. Click the right arrow key to select the next point, and again choose the No Marker option. Repeat for the rest of the Actual points. This hides the Actual marker for the points in the chart, but leaves the marker in the Actual legend entry.

Click on the Target series, then click on the first Target marker or click the right arrow key to select the first marker for Target. In the Format Data Point dialog, choose No Marker for Marker Style. Click the right arrow key to select the next Target point, and again choose the No Marker option, and repeat for the rest of the Target data points. This hides the marker for the points in the chart, but leaves the marker in the legend.

The result is the left chart below. Again, if you don’t need a legend, you could have skipped all the marker formatting we did before, omitted the legend, and made the bullet chart narrower (below right).

## Single Horizontal Bullet

The protocol for a horizontal bullet is similar to that for a vertical bullet, with adjustments to account for different axis behavior in a horizontal bar chart. First, one more row of data is needed for the Y value of 0.5 for plotting Actual and Target (below left). Select only the blue shaded range (not the entire data range as before) and insert a clustered horizontal bar chart (below right)

Select the cells containing the Actual label and value, then hold Ctrl and select the cells containing the Y label and value, so both areas are highlighted, and copy (Ctrl+C). Select the chart, and use Paste Special from the Home ribbon tab to paste the data as a new chart series, using values in rows, series names in first column, and categories in first row.

The result is the barely visible bar at the top of the chart, below left. Change the series name from Y to Actual, either by using the Select Data dialog, by selecting the series and dragging the series name highlight from the cell containing Y to the cell containing Actual, or by editing the series formula to put the proper cell reference in the first argument of the formula (below right). Right click the Actual series, choose Change Series Chart Type, and select the XY Scatter option with markers and no line, and format it as a marker with dark blue border and fill (bottom middle).

Select the cells containing the Target label and value and the Y label and value, and copy (Ctrl+C). Select the chart, and use Paste Special to paste the copied data as a new chart series, using values in rows, series names in first column, and categories in first row. This is pasted as another XY Scatter series, again with the name Y. Format the series using the dash marker with a 1.5 point red line and red fill (below left). Change the series name to Target (below right).

Format any of the remaining column chart series (High, Medium, or Low) and set the Overlap to 100 and Gap Width of 50 (below left). Format the secondary vertical axis (right edge of the chart) so the minimum and maximum are manually fixed at 0 and 1, the axis tick label setting is No Labels, and the line option is No Line (below right). Format the horizontal axis so the tick label spacing is more frequent (bottom middle).

Select the Actual series and add error bars. It’s an XY scatter chart series, so you get horizontal and vertical error bars (top left chart below). Delete the vertical error bars. Format the horizontal error bars to show the minus direction only with a percentage of 100, without end caps, using the same dark blue for the line, and make the line thick (such as 6 points in the top right chart below).

Select the Target series and add error bars. As an XY scatter chart series, it gets horizontal and vertical error bars (bottom left chart below). Delete the horizontal error bars, and format the horizontal error bars to show both directions, at a value of 0.3, without end caps, with a 1.5-point red line (bottom right chart below).

Time to work our Excel Chart Magic again. Click on the marker for Actual to select the whole series. Then click on the marker again or click the right keyboard arrow key, which selects just the single point, and press Ctrl+1 to format the point. In the Format Data Point dialog, choose No Marker for Marker Style, which hides the Actual marker for the data point in the chart, but leaves the marker in the Actual legend entry.

Repeat the single click plus single click technique to select the marker for Target. In the Format Data Point dialog, again choose No Marker for Marker Style, to hide the marker for the data point in the chart, but leave the marker in the legend.

The result is the left chart below. If you don’t need a legend, you could have skipped all this finagling with markers, not shown a legend, and made the bullet chart thinner (below right).

## Multiple Horizontal Bullet

The protocol for multiple horizontal bullets in a single chart is the similar to that for a single bullet, with minor adjustments. You need one column of data per bullet, with High, Mid, and Low, plus Actual and Target for each.  Also, a row of data is needed for the Y values for plotting Actual and Target (below left). These values are 0.5 for the first category (Alpha), 1.5 for the next (Beta), and so on, adding 1 to each previous Y value.

Select only the blue shaded range (not the entire data range as before) and insert a clustered horizontal bar chart (top right below). The categories (Alpha, Beta, and Gamma) are plotted bottom-to-top, in a phenomenon described in Excel Plotted My Bar Chart Upside-Down. This is normal behavior, of course, and it’s simple to counteract by formatting the vertical axis to plot categories in reverse order, with the horizontal axis crossing at the maximum category (bottom right below).

Select the cells containing the Actual label and values, then hold Ctrl and select the cells containing the Y label and values, so both areas are highlighted, and copy (Ctrl+C). Select the chart, and use Paste Special from the Home ribbon tab to paste the data as a new chart series, using values in rows, series names in first column, and categories in first row.

The result is the barely visible bar at the top of the chart, below left. Change the series name from Y to Actual (below right). Right click the Actual series, choose Change Series Chart Type, and select the XY Scatter option with markers and no line, and format it as a marker with dark blue border and fill (bottom middle).

Select the cells containing the Target label and values and the Y label and values, and copy (Ctrl+C). Select the chart, and use Paste Special to paste the copied data as a new chart series, using values in rows, series names in first column, and categories in first row. This is pasted as another XY Scatter series, again with the name Y. Format the series using the dash marker with a 1.5 point red line and red fill (below left). Change the series name to Target (below right).

Format any of the remaining column chart series (High, Medium, or Low) and set the Overlap to 100 and Gap Width of 50 (below left). Format the secondary vertical axis (right edge of the chart) so the minimum and maximum are manually fixed at 0 and the number of categories (3 for Alpha, Beta, and Gamma), the axis tick label setting is No Labels, and the line option is No Line (below left). Format the horizontal axis so the tick label spacing is more frequent (bottom middle).

Select the Actual series and add error bars. It’s an XY scatter chart series, so you get horizontal and vertical error bars (top left chart below). Delete the vertical error bars. Format the horizontal error bars to show the minus direction only with a percentage of 100, without end caps, using the same dark blue for the line, and make the line thick (such as 6 points in the top right chart below).

Select the Target series and add error bars. As an XY scatter chart series, it gets horizontal and vertical error bars (bottom left chart below). Delete the horizontal error bars, and format the horizontal error bars to show both directions, at a value of 0.3, without end caps, with a 1.5-point red line (bottom right chart below).

Time to disappear the Actual and Target markers from the chart but keep them in the legend. Click on the marker for Actual to select the whole series. Then click on the marker again or click the right keyboard arrow key, which selects just the single point, and press Ctrl+1 to format the point. In the Format Data Point dialog, choose No Marker for Marker Style, which hides the Actual marker for the data point in the chart, but leaves the marker in the Actual legend entry.

Repeat the single click plus single click technique to select the marker for Target. In the Format Data Point dialog, again choose No Marker for Marker Style, to hide the marker for the data point in the chart, but leave the marker in the legend.

The result is the left chart below. If you don’t need a legend, you could have skipped all this finagling with markers, not shown a legend, and made the bullet chart thinner (below right).

The only problem with the multiple horizontal bullet chart as created here is that the High, Mid, and Low legend entries are in the wrong order. Plotting the vertical axis in reverse order also caused these series to be entered in the legend in reverse order.

There are two ways to avoid this legend reversal. One is to reverse the order of the data in the worksheet, the other is to convert from a clustered bar chart to a stacked bar chart.

## Multiple Horizontal Bullet, Reverse Data Order

If you have control over the data sheet, the easiest way to keep the legend entries in the right order is to change the order of the bullets in the sheet. In the range below left the bullets are listed Gamma, Beta, Alpha. When you select the bar chart data (shaded blue) and insert a clustered bar chart, the categories are ordered top down in the desired Alpha-Beta-Gamma order, and the legend is also shown in the desired Low-Mid-High order.

Following the rest of the protocol above, but omitting the Categories in Reverse Order and Horizontal Axis Crosses at Maximum Category settings, results in the following bullet charts, with and without a legend.

## Multiple Horizontal Bullet, Stacked Bars

If you can’t change the order of the bullet categories, you need to insert a couple rows for calculations. In the range below, the second through fourth rows contain High, Mid, and Low values. The fifth row, labeled “Mid”, has a formula that subtracts the Low value from the Mid value, while the sixth row, labeled “High”, has a formula that subtracts the Mid value from the High value. These calculated values will be stacked together onto the Low value to display the bands in the chart.

Select the first blue shaded row in the data range (top left, below), then hold Ctrl and select the other three blue shaded rows, so that both areas are highlighted, and insert a stacked horizontal bar chart (top right, below). Format the vertical axis to plot categories in reverse order, with the horizontal axis crossing at the maximum category (bottom right below).

Select the cells containing the Actual label and values, then hold Ctrl and select the cells containing the Y label and values, so both areas are highlighted, and copy (Ctrl+C). Select the chart, and use Paste Special from the Home ribbon tab to paste the data as a new chart series, using values in rows, series names in first column, and categories in first row.

The result is the bars stacked on the right edge of the existing bars, below left. Change the series name from Y to Actual (below right). Right click the Actual series, choose Change Series Chart Type, and select the XY Scatter option with markers and no line, and format it as a marker with dark blue border and fill (bottom middle).

Select the cells containing the Target label and values and the Y label and values, and copy (Ctrl+C). Select the chart, and use Paste Special to paste the copied data as a new chart series, using values in rows, series names in first column, and categories in first row. This is pasted as another XY Scatter series, again with the name Y. Format the series using the dash marker with a 1.5 point red line and red fill (below left). Change the series name to Target (below right).

Format the secondary vertical axis (right edge of the chart) so the minimum and maximum are manually fixed at 0 and the number of categories (3 for Alpha, Beta, and Gamma, below right). Format the horizontal axis so the tick label spacing is more frequent (bottom middle).

Select the Actual series and add error bars. It’s an XY scatter chart series, so you get horizontal and vertical error bars (top left chart below). Delete the vertical error bars. Format the horizontal error bars to show the minus direction only with a percentage of 100, without end caps, using the same dark blue for the line, and make the line thick (such as 6 points in the top right chart below).

Select the Target series and add error bars. As an XY scatter chart series, it gets horizontal and vertical error bars (bottom left chart below). Delete the horizontal error bars, and format the horizontal error bars to show both directions, at a value of 0.3, without end caps, with a 1.5-point red line (bottom right chart below).

Hide the Actual and Target markers from the chart but keep them in the legend. Click on the marker for Actual to select the whole series. Then click on the marker again or click the right keyboard arrow key, which selects just the single point, and press Ctrl+1 to format the point. In the Format Data Point dialog, choose No Marker for Marker Style, which hides the Actual marker for the data point in the chart, but leaves the marker in the Actual legend entry.

Repeat the single click plus single click technique to select the marker for Target. In the Format Data Point dialog, again choose No Marker for Marker Style, to hide the marker for the data point in the chart, but leave the marker in the legend.

The result is the left chart below. If you don’t need a legend, you could have skipped all this finagling with markers, not shown a legend, and made the bullet chart thinner (below right).

## Structured Referencing to Identify Parts of Excel Tables

Guest post by Zack Barresse & Kevin Jones
Data Automation Professionals, LLC

Zack and Kevin are VBA ninjas who have been helping people around the internet for several years. They’ve combined resources and started a company, Data Automation Professionals, which helps Excel users automate simple to complex tasks, consults on projects, and teaches the world VBA. Zack has been hanging around forums like Mr Excel and VBA Express for several years, and maintains the blog at exceltables.com. Kevin is an engineer who has been spotted around the net using the online moniker ‘zorvek’. Together they’ve written a book on Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables.

With the introduction of Tables in Excel 2007 (Tables are a re-invention of Lists, introduced in Excel 2003), we were also provided a new syntax for referencing Tables and the parts within those Tables. This new syntax is called structured referencing. The reason a new referencing method is required is because Tables are very dynamic, and the traditional cell referencing syntax would not allow robust referencing without clever use of functions as Tables as data is added and removed. As you will see in this article, structured referencing is a very powerful tool that makes your formulas dynamic while maintaining significant simplicity. If you’re not familiar with Tables, a good starting point is this blog post (with video) by Excel MVP Jon Acampora.

Tables play an integral part of modern Excel. They’re very organized, controlled, and most importantly, they have rules. These allow for a lot of built-in functionality previously unavailable. Functionality such as good data structuring, dynamic chart ranges, dynamic PivotTable sources, etc. Additionally there is some default behavior which can be nice such as banded rows and columns, cell formatting that is added to every new row of a table, and new rows auto-populating with formulas.

All examples in this article are for Excel 2010 and later. There is a slight difference between using structured referencing in Excel 2010 and Excel 2007—Excel 2007 is not covered in this article. For the purposes of discussion, the traditional method of referencing cells (i.e. A1, A2, B1:B100, A2:D100, etc.) is referred to as standard referencing.

## Structured Referencing

Before we get too in-depth here, let’s make sure we have a good understanding of what is meant by structured referencing. Structured referencing makes it easier and more intuitive to work with cell references in Tables. It allows you to reference a Table’s parts such as the columns, header rows, and total rows without using standard referencing (R1C1 or A1 syntax) but rather by using the Table’s name and other constants such as column header values which makes references easier to read because recognizable names are used. This eliminates the need to use complex formulas or rewrite formulas when the Table structure is changed or data is added or removed. Formula audits are also made much easier.

Let’s look at a quick example of referencing the hours, let’s say this is stored in column A, and the rate, stored in column B. The standard referencing formula to calculate the total billable amount in row 2 is:

``=A2*B2``

The structured referencing formula in row 2 (and in all other rows) is:

``=[@Hours]*[@Rate]``

As you can see, with structured referencing it is much clearer what the formula is doing than with standard referencing. With standard referencing, we have to navigate to the source to determine what, exactly, is in cell A2 and B2. With structured referencing, we have greater transparency with makes development, maintenance, and auditing easier. In today’s world of Excel, with groups like EuSpRIG and heightened auditing and maintenance requirements, transparency in spreadsheets is as important as it’s ever been, and structured referencing goes a long ways to assist.

In the examples below, we will look at a simple Table which has three columns and six rows, with both the header and total rows showing. The image below is used for the next examples.

Before we get too in-depth on how the referencing actually takes place, there are a few rules to structured referencing which must be identified. In any single structured reference you may have a

• Table name,
• special identifier, or
• column name.

Generally only the column name is required, but every structured reference will have some combination of these three elements. Below are the basic rules of when to use these elements.

• The Table name used if
• more than one column of a Table is being referenced, or
• the column is being referenced from outside of the Table.
• A special identifier is used if a specific part of the Table is being referenced, i.e. the total row.

A key part of structured referencing is the use of square brackets. Square brackets are used to identify a reference as a structured reference versus a standard reference. Every structured reference (except the Table name itself) is enclosed in a set of square brackets. There are two occasions where you will have an additional set of square brackets.

#### Single-column cell reference

Column name of “Column” (no quotes used as the cell value) has a reference of:

``[@Column]``

Column name of “A Column” (no quotes used as the cell value—note the space) has a reference of:

``[@[A Column]]``

#### Multi-column cell reference

``TableName[@[Column1]:[Column2]]``

In a multiple column reference, Excel will automatically place a separate set of square brackets around each column name regardless of whether or not there is a space or other special character in the name, as well as append the Table name to the reference. This is to identify it as an individual column within a multiple column reference.

Additionally, below are the characters which Excel identifies and automatically puts an additional set of square brackets around.

• Space ( )
• Line feed
• Carriage return
• Comma ( , )
• Colon ( : )
• Period ( . )
• Left bracket ( [ )
• Right bracket ( ] )
• Pound sign ( # )
• Single quotation mark (apostrophe) ( ‘ )
• Quotation mark (  )
• Left curly bracket (brace) ( { )
• Right curly bracket (brace) ( } )
• Dollar sign ( \$ )
• Caret ( ^ )
• Ampersand ( & )
• Asterisk ( * )
• Plus sign ( + )
• Minus sign ( – )
• Equal sign ( = )
• Greater than ( > )
• Less than ( < )
• Division ( / )

### This Row

The ampersand character (@) is used to identify “This Row” in a structured reference. This is also known as the implicit intersection of the row in which the reference resides.

### Special Characters

Excel uses special characters to qualify structured references (discussed later in more detail) and, when these characters are included as part of a column name, they need to be “escaped” so that Excel does not interpret it as a special reference qualifier. The apostrophe ( ‘ ) is used for this escaping.

Below is a list of all special characters used to qualify structured references and must be preceded by an apostrophe when part of a column header.

• Left bracket ( [ )
• Right bracket ( ] )
• Pound sign ( # )
• Single quotation mark (apostrophe) ( ‘ )
• At sign ( @ )

### Special Identifiers

When attempting to reference specific parts of a Table, you will need to use a special identifier. There are only five. Let’s take a look at each of them and a picture for visual reference. In the following examples the referenced area is outlined with a red box.

#### @ (or [#This Row] in Excel 2007)

Note that when referencing Table parts which are not visible or enabled such as when a header or total row isn’t showing, the reference will evaluate to a reference error (`#REF!`). Let’s look at an example formula:

``=TableName[[#Headers],[Column 3]]``

The above formula references the Table named “TableName”, with the special identifier of the header of “Column 3”. This is a single-cell reference. If the header row is showing the result will be the column name or, in this case, “Column 3”. If the header row is not showing the result will be “#REF!”.

This means we can use formulas to tell if a header or total row is visible or not.

``=IF(ISERR(TableName[[#Headers],[Column 3]]),"No","Yes")``

#### Total row visible formula:

``=IF(ISERR(TableName[[#Totals],[Column 3]]),"No","Yes")``

Another Table part which can possibly not exist is the body. It’s important to note this will not affect formula evaluation, but does have a serious impact in VBA which is covered later in this article.

## In Data Validation

Using Tables as a source for an in-cell drop down can simplify your spreadsheets, but Table ranges cannot be referenced directly in the data validation source formula. Instead you must name the range and use that name in the validation formula. To do this, navigate to the FORMULAS tab and click ‘Name Manager’, then click ‘New’.

Since the name of any Table must be unique  for the entire workbook, you cannot name a range the same as a Table name. This is why some people either use Hungarian Notation with naming Tables, or preface all Table names with a “t”. For example, if you have a Table with a list of countries, instead of naming the Table “Countries”, you would name it “tCountries”. This way if you wanted to have those countries as a data validation drop-down list, you could name a range of “Countries” which points to the Table range. Here is an example:

Name: “Countries”

Refers to: “=tCountries[Countries]”

## In Charts

Putting your data into Tables and using structured referencing makes it easier to create dynamic charts that change as the data in the Table changes. As to not reinvent the wheel, we’ll refer you to Jon Peltier’ great post about this topic Easy Dynamic Charts Using Lists or Tables. The takeaway from the blog post is Tables make great data sources for charts which grow and shrink as the source data set changes. It’s a nice alternative from having to manually create dynamic named ranges.

## In Formulas

Putting all of this information into practice can be confusing. Let’s look at some specific structured referencing syntax examples in formulas. Keep in mind that structured references always evaluate to a range of cells and are treated like any other range reference in Excel, so if you’re referencing more than a single cell it must be used as an aggregate, range array, or lookup array, depending on the formula to which they are being passed.

There are five specific locations we can reference:

• Body column
• Entire column
• Column total
• Column cell

The examples below assume the Table’s name is “TableName”, using column “Column Name” where applicable.

### Specific Column Body or Data Excluding Header and Total Rows

``TableName[Column Name]``

### Specific Column Including Header and Total Rows

``TableName[[#All],[Column Name]]``

### All Data Excluding Header and Total Rows (Data Body)

``TableName``

### All Data Including Header and Total Rows

``TableName[#All]``

### Row Across All Columns

``TableName[@]``

### Row for One Column (Single Cell)

``TableName[@[Column Name]]``

### Header Row Across All Columns

``TableName[#Headers]``

### Header Row for One Column

``TableName[[#Headers],[Column Name]]``

### Total Row Across All Columns

``TableName[#Totals]``

### Total Row for One Column

``TableName[[#Totals],[Column Name]]``

Let’s assume we have a column in our Table which is titled “Column 3”. Below are examples of the types of structured references we can use to reference specific parts of that column.

### Specific Column Body or Column Data Excluding Header and Total Rows

``TableName[Column 3]``

### Specific Column Including Header and Total Rows

``TableName[[#All],[Column 3]]``

### Header Row for One Column

``TableName[[#Headers],[Column 3]]``

### Total Row for One Column

``TableName[[#Totals],[Column 3]]``

### Row for One Column (Single Column Cell)

``TableName[@Column 3]``

## In VBA

There are two basic methods for referencing Tables and parts of Tables from VBA. The easiest is to use the same syntax as described above for formulas where you pass the Table information as a text string to the `Range` object. The other method is to use the Excel object models `ListObject` and its child properties and methods. Both are described below.

### Using Range and Evaluate

Just as with standard referencing, the `Range` object evaluates structured references passed as a text string. For example, to reference the body or data portion of “Column 3” in the Table named “TableName” using the `Range` method:

``Range("TableName[Column 3]")``

Note that no qualifying worksheet is required because the table name has to be unique across all worksheets. This is the referencing style you will see when using the Macro Recorder.

### Using the Object Model’s ListObject

To reference any part of a Table using the Excel object model, we have to first identify the Table object itself. The `ListObject` object is how Excel exposes a Table in the Excel object model. It is contained in the collection `ListObjects` which  is a child of the `Worksheet` object. Use this syntax to reference a specific Table on a worksheet:

``ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")``

`ListObjects`, being a collection of list objects or Tables, can also be accessed with an index number:

``ThisWorkbook.Worksheets("Sheet1").ListObjects(1)``

The index number of the Table is the order in which it was created on the worksheet and is a read-only property.

Once we have the Table’s `ListObject` object we can access and manipulate any part of that Table. The more commonly used properties and methods are discussed below. Each example starts with this code:

``````Dim Table1 As ListObject
Set Table1 = ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")``````

### Range Property

The `Range` property returns the entire Table as a range object including the header and total rows.

``````Dim Table1Range As Range
Set Table1Range = Table1.Range``````

The `HeaderRowRange` returns the Table’s header row as a Range object. The range is always a single row – the header row – and extends over all Table columns. When the header row is not showing this property returns Nothing.

``````Dim Table1HeaderRowRange As Range

### DataBodyRange Property

The `DataBodyRange` returns the Table’s body as a Range object. The range is every row between the header and the total row and extends over all Table columns.

``````Dim Table1DataBodyRange As Range
Set Table1DataBodyRange = Table1.DataBodyRange``````

When the Table does not contain any rows the `DataBodyRange` property returns Nothing. This may be confusing when looking at the worksheet as it will appear as if a single row exists. This is the only case when the property `InsertRowRange` returns a range which can be used to insert a new row. Effectively `InsertRowRange` and `DataBodyRange` are equivalent.

### TotalRowRange Property

The `TotalRowRange` returns the Table’s total row as a `Range` object. The range is always a single row – the total row – and extends over all Table columns. When the total row is disabled this property returns Nothing.

``````Dim Table1TotalRowRange As Range
Set Table1TotalRowRange = Table1.TotalRowRange``````

### InsertRowRange Property

The `InsertRowRange` returns the Table’s current insertion row as a Range object only when the Table `DataBodyRange` object is Nothing (the Table has no data rows); it’s Nothing when the `DataBodyRange` is a Range (the Table has one or more data rows). While this range was always the last data row in Excel 2003 (the row with the asterisk), it was partially depreciated in Excel 2007 and remains so in 2013. In Excel 2007 and later versions it only returns the first data row and only when the Table does not contain any data. Otherwise it returns Nothing.

Two additional properties or collections provide access to the rows and columns in the Table. Each collection provides access to all of the `ListRow` and `ListColumn` objects in the Table. Each `ListRow` and each `ListColumn` object has properties and methods.

### ListRows Property

The `ListRows` property returns a collection of the rows in the Table’s `DataBodyRange` as a `ListRows` object type. It behaves very much like a `Collection` object and contains a collection of all the `ListRow` objects. Rows are referenced only by a one-based index number relative to the first row. An empty Table has no rows. The `ListRows.Add` method can be used to insert new rows.

``Debug.Print "The Table has " & Table1.ListRows.Count & " rows."``

### ListColumns Property

The `ListColumns` property returns a collection of the columns in the Table as a `ListColumns` object. It behaves very much like a `Collection` object and contains a collection of all the `ListColumn` objects. Columns are referenced by a one-based index number relative to the first column or the column header. The `ListColumns.Add` method can be used to insert new columns.

``Debug.Print "The Table has " & Table1.ListColumns.Count & " columns."``

## Apply Custom Data Labels to Charted Points

Often you want to add custom data labels to your chart. The chart below uses labels from a column of data next to the plotted values.

When you first add data labels to a chart, Excel decides what to use for labels—usually the Y values for the plotted points, and in what position to place the points—above or right of markers, centered in bars or columns. Of course you can change these settings, but it isn’t obvious how to use custom text for your labels.

This chart is the starting point for our exercise. It plots simple data from columns B and C, and it displays only the default data labels, showing the Y values of each point.

There are a number of ways to apply custom data labels to your chart:

• Manually Type Desired Text for Each Label
• Manually Link Each Label to Cell with Desired Text
• Use the Chart Labeler Program
• Use Values from Cells (Excel 2013 and later)
• Write Your Own VBA Routines

## Manually Type Desired Text for Each Label

The least sophisticated way to get your desired text into each label is to manually type it in.

Click once on a label to select the series of labels.

Click again on a label to select just that specific label.

Double click on the label to highlight the text of the label, or just click once to insert the cursor into the existing text.

Type the text you want to display in the label, and press the Enter key.

Repeat for all of your custom data labels. This could get tedious, and you run the risk of typing the wrong text for the wrong label (I initially typed “alpha” for the label above, and had to redo my screenshot).

One thing that makes this approach unsophisticated is that the typed labels are not dynamic. If th text in one of the cells changes, the corresponding label will not update.

## Manually Link Each Label to Cell with Desired Text

Select an individual label (two single clicks as shown above, so the label is selected but the cursor is not in the label text), type an equals sign in the formula bar, click on the cell containing the label you want, and press Enter. The formula bar shows the link (=Sheet1!\$D\$3).

Repeat for each of the labels. This could get tedious, but at least the labels are dynamic. If the text in one of the cells changes, the corresponding label updates to show the new text.

## Use the Chart Labeler Program

Brilliant Excel jockey and former MVP Rob Bovey has written a Chart Labeler add-in, which allows you to assign labels from a worksheet range to the points in a chart. It is free for anyone to use and can be downloaded from http://appspro.com. Rob colls it the XY Chart Labeler, but it actually works with any chart type that supports data labels.

When installed, the add-in adds a custom ribbon tab with a handful of useful commands. The tab is added at the end of the ribbon, but being pressed for space I moved it digitally to the beginning.

With a chart selected, click the Add Labels ribbon button (if a chart is not selected, a dialog pops up with a list of charts on the active worksheet). A dialog pops up so you can choose which series to label, select a worksheet range with the custom data labels, and pick a position for the labels.

If you select a single label, you can see that the label contains a link to the corresponding worksheet cell. This is like the previous method, but less tedious and much faster.

## Use Values from Cells (Excel 2013 and later)

After years and years of listening to its users begging, Microsoft finally added an improved labeling option to Excel 2013.

First, add labels to your series, then press Ctrl+1 (numeral one) to open the Format Data Labels task pane. I’ve shown the task pane below floating next to the chart, but it’s usually docked off to the right edge of the Excel window.

Click on the new checkbox for Values From Cells, and a small dialog pops up that allows you to select a range containing your custom data labels.

Then uncheck the Y Value option. I also uncheck the Show Leader Lines option, which is another enhancement added in Excel 2013. Leader lines are hardly ever useful for the charts I make, but many users are happy with them.

While these data labels are not explicitly linked to worksheet cells as in the previous approaches, they still reflect any changes to the cells that contain the labels.

## Write Your Own VBA Routines

I’ve put together a couple little routines that help with data point labeling. These are quick and dirty, because sometimes that’s all that you need. Also, writing your own code allows you to streamline your workflow according to your specific requirements.

### Add Data Labels from Range Selected by User

This routine first makes sure a chart is selected, then it determines which series is to be labeled. It asks the user to select a range using an InputBox, and if the user doesn’t cancel it adds a label to the series point by point, linking the label to the appropriate cell.

``````Sub AddLabelsFromUserSelectedRange()
Dim srs As Series, rng As Range, lbl As DataLabel
Dim iLbl As Long, nLbls As Long

If Not ActiveChart Is Nothing Then
If ActiveChart.SeriesCollection.Count = 1 Then
' use only series in chart
Set srs = ActiveChart.SeriesCollection(1)
Else
' use series associated with selected object
Select Case TypeName(Selection)
Case "Series"
Set srs = Selection
Case "Point"
Set srs = Selection.Parent
Case "DataLabels"
Set srs = Selection.Parent
Case "DataLabel"
Set srs = Selection.Parent.Parent
End Select
End If

If Not srs Is Nothing Then
' ask user for range, avoid error if canceled
On Error Resume Next
Set rng = Application.InputBox( _
"Select range containing data labels", _
"Select Range with Labels", , , , , , 8)
On Error GoTo 0

If Not rng Is Nothing Then
' point by point, assign cell's address to label
nLbls = srs.Points.Count
If rng.Cells.Count < nLbls Then nLbls = rng.Cells.Count
For iLbl = 1 To nLbls
srs.Points(iLbl).HasDataLabel = True
Set lbl = srs.Points(iLbl).DataLabel
With lbl
.Position = xlLabelPositionRight
End With
Next
End If
End If
End If
End Sub``````

### Add Data Labels from Row or Column Next to Y Values

This routine first makes sure a chart is selected, then it determines which series is to be labeled. It doesn’t bother the user, instead the routine parses the series formula to find the range containing the Y values, and if this is a valid range, it finds the next column or row, depending on the orientation of the Y values range. The code then adds a label to the series point by point, linking the label to the appropriate cell.

``````Sub AddLabelsFromRangeNextToYValues()
Dim srs As Series, rng As Range, lbl As DataLabel
Dim iLbl As Long, nLbls As Long
Dim sFmla As String, sTemp As String, vFmla As Variant

If Not ActiveChart Is Nothing Then
If ActiveChart.SeriesCollection.Count = 1 Then
' use only series in chart
Set srs = ActiveChart.SeriesCollection(1)
Else
' use series associated with selected object
Select Case TypeName(Selection)
Case "Series"
Set srs = Selection
Case "Point"
Set srs = Selection.Parent
Case "DataLabels"
Set srs = Selection.Parent
Case "DataLabel"
Set srs = Selection.Parent.Parent
End Select
End If

If Not srs Is Nothing Then
' parse series formula to get range containing Y values
sFmla = srs.Formula
sTemp = Mid\$(Left\$(sFmla, Len(sFmla) - 1), InStr(sFmla, "(") + 1)
vFmla = Split(sTemp, ",")
sTemp = vFmla(LBound(vFmla) + 2)
On Error Resume Next
Set rng = Range(sTemp)

If Not rng Is Nothing Then
' use next column or row as appropriate
If rng.Columns.Count = 1 Then
Set rng = rng.Offset(, 1)
Else
Set rng = rng.Offset(1)
End If

' point by point, assign cell's address to label
nLbls = srs.Points.Count
If rng.Cells.Count < nLbls Then nLbls = rng.Cells.Count
For iLbl = 1 To nLbls
srs.Points(iLbl).HasDataLabel = True
Set lbl = srs.Points(iLbl).DataLabel
With lbl
.Position = xlLabelPositionRight
End With
Next
End If
End If
End If
End Sub``````

## Introduction

This article builds upon a previous post, VBA: A Simple Tool to Open a CSV File and Create Chart with Data Specified by User, which showed how to build the barest possible VBA procedure for opening a CSV file and making a chart from its data. This article will show how to make the VBA procedure more robust, easier to use, and resistant to errors. It is assumed that you’ve already built the simple VBA tool from the earlier article.

Rather than the simple workbook we created in the previous solution, the advanced solution will use an add-in. This integrates the add-in’s functionality more smoothly into Excel, making the solution a more professional option.

Add-ins are like regular Excel workbooks, complete with worksheets and other Excel objects, but they have a different file extension, .xlam, and they are not visible in the Excel interface. They can be opened like regular workbooks when their functionality is needed, or they can be installed so that they are always available.

#### Saved Information: Named Range Containing Chart Types and Styles

The worksheet contains a list of chart types that our program will create. These are the simplest and most useful charts built into Excel. The list has three columns, the first being the integer values of Excel’s chart type constants (e.g., `xlArea` = 1, `xlXYScatter` = -4169), the second the default chart styles in Excel 2013 (ignored in earlier versions), and the third the chart type names that will appear in the dialog. To modify the eerlier worksheet, simply insert a column between the Value and Type values, and insert the Style values.

The range A2:C12 has been named “ChartTypes”, so it will be easy for the code to put these values into the dialog. We can save information like this even in the invisible worksheets of an invisible add-in.

Now that we’ve updated the chart type data, we can save our workbook as an add-in.

With the workbook active, go to File tab > Save As, browse to a folder, change the Save As Type to Excel Add-In (*.xlam), and note that the dialog changes to the default user add-in library (C:\Users\[username]\AppData\Roaming\Microsoft\AddIns). This is a convenient place to store add-ins, because they are easier to find when you want to install them. However, if you have an orderly file system hierarchy, you may want to store the add-in with related files. If so, navigate to the desired folder. Then click Save.

The worksheets of an add-in are not visible in the Excel interface. However, you can temporarily change an add-in back to a regular workbook. In the VB Editor’s Project Explorer, select the ThisWorkbook object of the add-in’s VB project, choose the IsAddIn property in the Property Window, and change it to False. The add-in workbook is now visible like any other workbook. Make any adjustments, then change the IsAddIn property back to True.

#### Starting the Program: The Excel Ribbon

Since the sheet is not visible, there is no button to start the code. We’ll use a button on the ribbon instead.

Many web pages and blog posts have been written about writing XML code for custom ribbon controls. As far as I know, only two books have been dedicated to the ribbon.

One is RibbonX: Customizing the Office 2007 Ribbon (affiliate link), written by two Excel MVPs and one Access MVPs. This is a very comprehensive volume that covers more than you could possibly want to know about customizing your ribbon. On a dollars-per-page basis, it also has to be about the cheapest Excel reference I’ve ever purchased.

The other book about the Office ribbon is RibbonX For Dummies, and I must admit I know very little about it.

To make it easy to add custom ribbon elements to your workbook or add-in, you should download the Custom UI Editor from the Open XML Developer web site. Open the add-in or workbook in the Custom UI Editor, insert an Office 2007 Custom UI Part from the Insert menu, and paste the code below into the Custom UI Part.

``````<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab idMso="TabData">
<group id="gpCSV" label="Process CSV">
<button id="btnProcessCSV" label="Process CSV File" screentip="Import CSV
file, save as Excel workbook, insert chart using dialog-selected type
and data" size="large" onAction="RX_OpenCSVFileAndPlotData"
imageMso="GroupChartLocation" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>``````

This code adds a custom group, “Process CSV”, to the Data tab of Excel’s ribbon, and adds a custom button, “Process CSV File”, to this group. The button calls a procedure named RX_OpenCSVFileAndPlotData.

The Custom UI Editor can be used to generate the callback for this procedure:

``````'Callback for btnProcessCSV onAction
Sub RX_OpenCSVFileAndPlotData(control As IRibbonControl)
End Sub``````

This callback is pasted into a regular code module, and the procedure that we’ve been working on, OpenCSVFileAndPlotData, is called from within the callback. You could simply have called OpenCSVFileAndPlotData from the XML code, but you would have had to add the control As IRibbonControl argument to the procedure definition. This makes it harder to simply call the code from the VB Editor’s Immediate Window.

Here is the finished callback, ready to go:

``````'Callback for btnProcessCSV onAction
Sub RX_OpenCSVFileAndPlotData(control As IRibbonControl)
OpenCSVFileAndPlotData
End Sub``````

The button looks like this, at the right end of the Data tab of the ribbon:

### Code Module

Here are the adjustments to the MChartFromCSVFile code module.

#### 1. Get CSV File Name

There are several things we can do to make sure the program is nice to the users.

First of all, we need to exit gracefully if the user cancels the GetOpenFileName dialog. Canceling the dialog returns False, so we need to prevent the error that will occur when the program tries to open a file named “False”.

We should check whether the file is already open in Excel, and offer to work on the open file.

We should check whether the file is even a CSV file. If it is not a CSV file, it will not open in Excel with the expected column structure. It is possible to make the program smart enough to open any text, and split the data into columns using any delimiter (commas, tabs, pipe characters), but that is for another exercise.

If the user decides to abort at any point, the execution proceeds to a label named `ExitProcedure` at the end of the main procedure.

``````  Do
sCSVFullName = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , _
"Select a CSV file", , False)

'' check whether user cancels
If sCSVFullName = "False" Then GoTo ExitProcedure

'' check that file isn't already open
bOpenFile = False
For Each wbTest In Workbooks
If wbTest.FullName = sCSVFullName Then
bOpenFile = True
Exit For
End If
Next
If bOpenFile Then
iMsgBoxResult = MsgBox("Data file is already open. Continue with open file?", _
vbYesNo + vbQuestion, "File Already Open")
If iMsgBoxResult = vbYes Then
Set wb = wbTest
Exit Do
End If
End If

'' ensure that it's a CSV file
If LCase\$(Right\$(sCSVFullName, 4)) <> ".csv" Then
iMsgBoxResult = MsgBox("That doesn't appear to be a CSV file. Continue anyway?", _
vbYesNo + vbQuestion, "Not a CSV File")
If iMsgBoxResult = vbYes Then Exit Do
Else
Exit Do
End If
Loop``````

#### 2. Open CSV file

The only thing to add here is a check whether our workbook `wb` has already been defined (that is, the CSV file was already open when the user started the program).

``````  If wb Is Nothing Then
Workbooks.Open sCSVFullName
Set wb = ActiveWorkbook
End If``````

#### 3. Save as workbook

There are many little changes we can do to make a user’s life easier. We want to save the workbook with the same name as the original CSV file, but we don’t want to bother the user with any duplicate file names. If we encounter a duplicate, we’ll append “_1″ to the file name; if that’s also a duplicate, we’ll append “_2″, and so on.

We need to check for duplicate file names among files already open in Excel, because two open workbooks cannot share the same file name. Then we need to check for duplicate file names in the directory we will save the workbook in.

``````  sFileRoot = Left\$(sCSVFullName, InStrRev(sCSVFullName, ".") - 1)
sWbkFullName = sFileRoot & ".xlsx"
iFile = 0
Do
'' check for duplicate file (already open)
bOpenFile = False
For Each wbTest In Workbooks
If wbTest.FullName = sWbkFullName Then
bOpenFile = True
Exit For
End If
Next

'' check for duplicate file (in folder)
If Not bOpenFile Then
If Len(Dir(sWbkFullName)) = 0 Then Exit Do
End If

'' increment file name
iFile = iFile + 1
sWbkFullName = sFileRoot & "_" & CStr(iFile) & ".xlsx"
Loop

wb.SaveAs sWbkFullName, xlWorkbookDefault``````

All of this checking may seem excessively compulsive, but being a good programmer means preventing user frustration. Remember, the user may know where you live.

#### 4. Parse file

We can improve the dialog display slightly, by not trying to show the first four rows of data if there are less than three rows in the CSV file.

While we’re making life easier for the user, let’s check the first row for text values, so we can precheck the dialog box if it looks like the file starts with column headers.

``````  Set ws = wb.Worksheets(1)
Set rng = ws.UsedRange
vRng = rng.Value2
iRows = rng.Rows.Count
iCols = rng.Columns.Count

'' info to display: column number, first few rows of column
ReDim vChartData(1 To iCols, 1 To 2)
For iCol = 1 To iCols
vChartData(iCol, 1) = iCol
sTemp = ""
For iRow = 1 To 4
If iRow > iRows Then Exit For
sTemp = sTemp & vRng(iRow, iCol) & ", "
Next
sTemp = Left\$(sTemp, Len(sTemp) - 2)
vChartData(iCol, 2) = sTemp
Next

'' first row header labels if all cells in first row are text
For iCol = 1 To iCols
If Not bFirstRowHeaders Then Exit For
Next``````

#### 5. Show dialog

When we load the dialog (UserForm), we pass in the array that shows the first few rows of data using the ChartData property, and also pass in our best guess about whether the first row of the has header labels in the FirstRowHeaders property.

After the user dismisses the dialog, we check whether the user canceled using the Cancel property. If the user did not cancel, we get chart type, first row headers, and X and Y columns using the appropriate properties.

Now we’re done with the UserForm, so we can unload it. If the user has canceled, we jump to the `ExitProcedure` label at the end of our program.

``````  Set frmChartFromCSVFile = New FChartFromCSVFile
With frmChartFromCSVFile
'' pass in information we know
.ChartData = vChartData

.Show

'' check whether user canceled
bCancel = .Cancel
If Not bCancel Then
'' get information selected by user
myChartType = .ChartType
vX = .Xcolumns
vY = .YColumns
End If
End With

If bCancel Then GoTo ExitProcedure``````

#### 6. Draw chart

We define the chart series data as in the earlier program. We select a blank cell so the chart we insert has no data plotted in it.

We insert the chart using the Excel 2007 syntax, so the code runs as expected in Excel 2007 and 2010. After we assign the selected chart type in Excel 2013, we apply the default Excel 2013 chart style for that chart type, to obtain the nicer Excel 2013 styles.

One by one we add each series, and if there are more than one series, we add a legend to our chart.

``````  '' define some series parameters
If IsEmpty(vX) Then
nX = 0
Else
nX = UBound(vX, 1) + 1 - LBound(vX, 1)
End If
nY = UBound(vY, 1) + 1 - LBound(vY, 1)
nSrs = nY
If nX > nY Then nSrs = nX
Set rCht = rng.Offset(1).Resize(iRows - 1)
Else
Set rCht = rng
End If

'' select blank cell before inserting chart
rng.Offset(iRows + 1, iCols + 1).Resize(1, 1).Select

Set cht = ws.Shapes.AddChart.Chart '' Excel 2007+

'' chart type and style
With cht
If myChartType <> 0 Then
.ChartType = myChartType
If Val(Application.Version) >= 15 Then
'' Excel 2013 ChartStyles
If iChartStyle > 0 Then
.ChartStyle = iChartStyle
End If
End If
End If

For iSrs = 1 To nSrs
Set srs = .SeriesCollection.NewSeries
With srs
' X values
If nX = 0 Then
' no X values specified
ElseIf nX = 1 Then
' all series share X values
.XValues = rCht.Columns(CLng(vX(0, 0)))
Else
' each series has unique X values
.XValues = rCht.Columns(CLng(vX(iSrs - 1, 0)))
End If
' Y values
If nY = 1 Then
' all series share Y values
.Values = rCht.Columns(CLng(vY(0, 0)))
Else
' each series has unique Y values
.Values = rCht.Columns(CLng(vY(iSrs - 1, 0)))
End If
' series name
If nSrs = nY Then
.Name = "=" & rng.Cells(1, CLng(vY(iSrs - 1, 0))). _
ElseIf nSrs = nX Then
.Name = "=" & rng.Cells(1, CLng(vX(iSrs - 1, 0))). _
End If
End If
End With
Next

If iSrs > 1 Then
.HasLegend = True
End If
End With``````

#### 7. Save file

Let’s select cell A1 before saving, just to satisfy our obsession with an orderly worksheet.

``````  ws.Range("A1").Select
wb.Save``````

### UserForm

The UserForm looks much like the earlier version, with the addition of a Default “label button” (a label with a raised appearance that functions as a button). This uses the first column of the CSV file as the chart’s X values, and each subsequent column as Y values for another series in the chart.

There also is a Cancel button if the user decides to bail out.

The lstChartType listbox now has three columns, two hidden columns for the chart type codes and the newly added chart style codes, and a visible column for the chart type names. This simply requires changing the ColumnWidths property to 0 pt;0 pt;112 pt. There is a new ChartStyles property used to get the appropriate Excel 2013 chart style codes for the selected chart type.

There are many changes to the code that make things run more smoothly.

Clicking the OK button will invoke some validation of selected columns, to ensure that the selected data is appropriate for the selected chart type. Data entry controls will be shaded light red if the data is not correctly validated and a message to the user will explain the problem.

Clicking the Cancel button sets a variable so that our code knows when the user canceled the process. In addition, code is added so that clicking the red X in the top right corner of the dialog doesn’t lead to error messages.

The X label button will do a little thinking before adding selected columns to the X listbox: if the chart type is a scatter chart, all selected columns will be added as X value columns, but if not, the list of X value columns will be cleared and only the first selected column will be added.

New variables in the UserForm module:

``````Dim mbCancel As Boolean
Const miBAD_COLOR As Long = &HC0C0FF
Const miGOOD_COLOR As Long = &H80000005``````

Validation added to the OK button:

``````Private Sub btnOK_Click()
Dim bValid As Boolean
Dim sInvalid As String

bValid = True
mbCancel = False

'validate
Me.lstX.BackColor = miGOOD_COLOR
Me.lstY.BackColor = miGOOD_COLOR
If Me.lstY.ListCount < 1 Then
bValid = False
sInvalid = "There must be at least one Y range in any chart."
End If
Select Case Me.ChartType
Case -4169, 75, 74
If Me.lstX.ListCount = Me.lstY.ListCount Then
ElseIf Me.lstX.ListCount <= 1 And Me.lstY.ListCount > 0 Then
ElseIf Me.lstY.ListCount = 1 And Me.lstX.ListCount > 0 Then
Else
bValid = False
sInvalid = sInvalid & "For a " & Me.lstChartTypes.List _
(Me.lstChartTypes.ListIndex, 1) & " chart, there must be" & vbNewLine
sInvalid = sInvalid & "- equal numbers of X and Y ranges," & vbNewLine
sInvalid = sInvalid & "- at most 1 X range and multiple Y ranges, or" _
& vbNewLine
sInvalid = sInvalid & "- multiple X ranges and one Y range." & vbNewLine
End If
Case Else
If Me.lstX.ListCount > 1 Then
bValid = False
sInvalid = sInvalid & "There can be at most one X range for a " & _
Me.lstChartTypes.List(Me.lstChartTypes.ListIndex, 1) & " chart." & vbNewLine
End If
End Select

If bValid Then
Me.Hide
Else
'Me.Repaint
MsgBox sInvalid, vbExclamation, "Invalid Data Selections"
End If
End Sub``````

The Cancel button code:

``````Private Sub btnCancel_Click()
mbCancel = True
Me.Hide
End Sub``````

This code ensures that clicking the red X closes the form as if the user had clicked the Cancel button:

``````Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then
Cancel = True
btnCancel_Click
End If
End Sub``````

Here’s the code for the Default label button:

``````Private Sub lblbtnDefault_Click()
Dim iLst As Long

Me.lstX.Clear
Me.lstY.Clear

For iLst = 2 To Me.lstChartData.ListCount
Next
End Sub``````

Here is the updated X label button code:

``````Private Sub lblbtnX_Click()
Dim iLst As Long
Dim myChartType As XlChartType

myChartType = Me.ChartType
For iLst = 1 To Me.lstChartData.ListCount
If Me.lstChartData.Selected(iLst - 1) Then
Select Case myChartType
Case -4169, 75, 74 ' scatter
Case Else ' not scatter
Me.lstX.Clear
End Select
Select Case myChartType
Case -4169, 75, 74 ' scatter
Case Else ' not scatter
Exit For
End Select
End If
Next
End Sub``````

ChartStyle property

``````Public Property Get ChartStyle() As Long
With Me.lstChartTypes
If .ListIndex > -1 Then
ChartStyle = CLng(.List(.ListIndex, 1))
End If
End With
End Property``````

### Code Module MChartFromCSVFile

Here is the complete listing of MChartFromCSVFile:

``````Option Explicit

'Callback for btnProcessCSV onAction
Sub RX_OpenCSVFileAndPlotData(control As IRibbonControl)
OpenCSVFileAndPlotData
End Sub

Sub OpenCSVFileAndPlotData()
Dim sCSVFullName As String, sWbkFullName As String, sWbkFileName As String, _
sFileRoot As String
Dim iFile As Long
Dim bOpenFile As Boolean
Dim wb As Workbook, wbTest As Workbook, ws As Worksheet, rng As Range, vRng As Variant
Dim rCht As Range, cht As Chart, srs As Series
Dim iRows As Long, iCols As Long, iRow As Long, iCol As Long
Dim sTemp As String
Dim vChartData As Variant
Dim myChartType As XlChartType, iChartStyle As Long
Dim vX As Variant, vY As Variant
Dim iX As Long, iY As Long, iSrs As Long
Dim nX As Long, nY As Long, nSrs As Long
Dim bCancel As Boolean
Dim iMsgBoxResult As VbMsgBoxResult
Dim frmChartFromCSVFile As FChartFromCSVFile

' 1. Get CSV file name
Do
sCSVFullName = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , _
"Select a CSV file", , False)

'' check whether user cancels
If sCSVFullName = "False" Then GoTo ExitProcedure

'' check that file isn't already open
bOpenFile = False
For Each wbTest In Workbooks
If wbTest.FullName = sCSVFullName Then
bOpenFile = True
Exit For
End If
Next
If bOpenFile Then
iMsgBoxResult = MsgBox("Data file is already open. Continue with open file?", _
vbYesNo + vbQuestion, "File Already Open")
If iMsgBoxResult = vbYes Then
Set wb = wbTest
Exit Do
End If
End If

'' ensure that it's a CSV file
If LCase\$(Right\$(sCSVFullName, 4)) <> ".csv" Then
iMsgBoxResult = MsgBox("That doesn't appear to be a CSV file. Continue anyway?", _
vbYesNo + vbQuestion, "Not a CSV File")
If iMsgBoxResult = vbYes Then Exit Do
Else
Exit Do
End If
Loop

' 2. Open CSV file
If wb Is Nothing Then
Workbooks.Open sCSVFullName
Set wb = ActiveWorkbook
End If

' 3. Save as workbook
sFileRoot = Left\$(sCSVFullName, InStrRev(sCSVFullName, ".") - 1)
sWbkFullName = sFileRoot & ".xlsx"
iFile = 0
Do
'' check for duplicate file (already open)
bOpenFile = False
For Each wbTest In Workbooks
If wbTest.FullName = sWbkFullName Then
bOpenFile = True
Exit For
End If
Next

'' check for duplicate file (in folder)
If Not bOpenFile Then
If Len(Dir(sWbkFullName)) = 0 Then Exit Do
End If

'' increment file name
iFile = iFile + 1
sWbkFullName = sFileRoot & "_" & CStr(iFile) & ".xlsx"
Loop

wb.SaveAs sWbkFullName, xlWorkbookDefault

' 4. Parse file
Set ws = wb.Worksheets(1)
Set rng = ws.UsedRange
vRng = rng.Value2
iRows = rng.Rows.Count
iCols = rng.Columns.Count

'' info to display: column number, first few rows of column
ReDim vChartData(1 To iCols, 1 To 2)
For iCol = 1 To iCols
vChartData(iCol, 1) = iCol
sTemp = ""
For iRow = 1 To 4
If iRow > iRows Then Exit For
sTemp = sTemp & vRng(iRow, iCol) & ", "
Next
sTemp = Left\$(sTemp, Len(sTemp) - 2)
vChartData(iCol, 2) = sTemp
Next

'' first row header labels if all cells in first row are text
For iCol = 1 To iCols
If Not bFirstRowHeaders Then Exit For
Next

' 5. Show dialog (get chart type, X values, Y values)
Set frmChartFromCSVFile = New FChartFromCSVFile
With frmChartFromCSVFile
'' pass in information we know
.ChartData = vChartData

.Show

'' check whether user canceled
bCancel = .Cancel
If Not bCancel Then
'' get information selected by user
myChartType = .ChartType
iChartStyle = .ChartStyle
vX = .Xcolumns
vY = .YColumns
End If
End With

If bCancel Then GoTo ExitProcedure

' 6. Draw chart
'' define some series parameters
If IsEmpty(vX) Then
nX = 0
Else
nX = UBound(vX, 1) + 1 - LBound(vX, 1)
End If
nY = UBound(vY, 1) + 1 - LBound(vY, 1)
nSrs = nY
If nX > nY Then nSrs = nX
Set rCht = rng.Offset(1).Resize(iRows - 1)
Else
Set rCht = rng
End If

'' select blank cell before inserting chart
rng.Offset(iRows + 1, iCols + 1).Resize(1, 1).Select

Set cht = ws.Shapes.AddChart.Chart '' Excel 2007+
''Set cht = ws.Shapes.AddChart2.Chart '' Excel 2013 only

'' chart type and style
With cht
If myChartType <> 0 Then
.ChartType = myChartType
If Val(Application.Version) >= 15 Then
'' Excel 2013 ChartStyles
If iChartStyle > 0 Then
.ChartStyle = iChartStyle
End If
End If
End If

For iSrs = 1 To nSrs
Set srs = .SeriesCollection.NewSeries
With srs
' X values
If nX = 0 Then
' no X values specified
ElseIf nX = 1 Then
' all series share X values
.XValues = rCht.Columns(CLng(vX(0, 0)))
Else
' each series has unique X values
.XValues = rCht.Columns(CLng(vX(iSrs - 1, 0)))
End If
' Y values
If nY = 1 Then
' all series share Y values
.Values = rCht.Columns(CLng(vY(0, 0)))
Else
' each series has unique Y values
.Values = rCht.Columns(CLng(vY(iSrs - 1, 0)))
End If
' series name
If nSrs = nY Then
.Name = "=" & rng.Cells(1, CLng(vY(iSrs - 1, 0))). _
ElseIf nSrs = nX Then
.Name = "=" & rng.Cells(1, CLng(vX(iSrs - 1, 0))). _
End If
End If
End With
Next

If iSrs > 1 Then
.HasLegend = True
End If
End With

' 7. Save file
ws.Range("A1").Select
wb.Save

ExitProcedure:

End Sub``````

blah

### UserForm Module FChartFromCSVFile

Here is the complete listing of FChartFromCSVFile:

``````Option Explicit

Dim mbCancel As Boolean
Const miBAD_COLOR As Long = &HC0C0FF
Const miGOOD_COLOR As Long = &H80000005

Private Sub btnCancel_Click()
mbCancel = True
Me.Hide
End Sub

Private Sub btnOK_Click()
Dim bValid As Boolean
Dim sInvalid As String

bValid = True
mbCancel = False

'validate
Me.lstX.BackColor = miGOOD_COLOR
Me.lstY.BackColor = miGOOD_COLOR
If Me.lstY.ListCount < 1 Then
bValid = False
sInvalid = "There must be at least one Y range in any chart."
End If
Select Case Me.ChartType
Case -4169, 75, 74
If Me.lstX.ListCount = Me.lstY.ListCount Then
ElseIf Me.lstX.ListCount <= 1 And Me.lstY.ListCount > 0 Then
ElseIf Me.lstY.ListCount = 1 And Me.lstX.ListCount > 0 Then
Else
bValid = False
sInvalid = sInvalid & "For a " & Me.lstChartTypes.List _
(Me.lstChartTypes.ListIndex, 1) & " chart, there must be" & vbNewLine
sInvalid = sInvalid & "- equal numbers of X and Y ranges," & vbNewLine
sInvalid = sInvalid & "- at most 1 X range and multiple Y ranges, or" _
& vbNewLine
sInvalid = sInvalid & "- multiple X ranges and one Y range." & vbNewLine
End If
Case Else
If Me.lstX.ListCount > 1 Then
bValid = False
sInvalid = sInvalid & "There can be at most one X range for a " & _
Me.lstChartTypes.List(Me.lstChartTypes.ListIndex, 1) & " chart." & vbNewLine
End If
End Select

If bValid Then
Me.Hide
Else
'Me.Repaint
MsgBox sInvalid, vbExclamation, "Invalid Data Selections"
End If
End Sub

Private Sub lblbtnDefault_Click()
Dim iLst As Long

Me.lstX.Clear
Me.lstY.Clear

For iLst = 2 To Me.lstChartData.ListCount
Next
End Sub

Private Sub lblbtnReset_Click()
Me.lstX.Clear
Me.lstY.Clear
End Sub

Private Sub lblbtnX_Click()
Dim iLst As Long
Dim myChartType As XlChartType

myChartType = Me.ChartType
For iLst = 1 To Me.lstChartData.ListCount
If Me.lstChartData.Selected(iLst - 1) Then
Select Case myChartType
Case -4169, 75, 74
Case Else
Me.lstX.Clear
End Select
Select Case myChartType
Case -4169, 75, 74
Case Else
Exit For
End Select
End If
Next
End Sub

Private Sub lblbtnY_Click()
Dim iLst As Long

For iLst = 1 To Me.lstChartData.ListCount
If Me.lstChartData.Selected(iLst - 1) Then
End If
Next
End Sub

Public Property Let ChartData(vData As Variant)
Me.lstChartData.List = vData
End Property

End Property

End Property

Public Property Get ChartType() As XlChartType
With Me.lstChartTypes
If .ListIndex > -1 Then
ChartType = CLng(.List(.ListIndex, 0))
End If
End With
End Property

Public Property Get ChartStyle() As Long
With Me.lstChartTypes
If .ListIndex > -1 Then
ChartStyle = CLng(.List(.ListIndex, 1))
End If
End With
End Property

Public Property Get Xcolumns() As Variant
Xcolumns = Me.lstX.List
End Property

Public Property Get YColumns() As Variant
YColumns = Me.lstY.List
End Property

Public Property Get Cancel() As Boolean
Cancel = mbCancel
End Property

Private Sub UserForm_Initialize()
Dim vChartTypes As Variant

vChartTypes = ThisWorkbook.Names("ChartTypes").RefersToRange.Value2
Me.lstChartTypes.List = vChartTypes
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then
Cancel = True
btnCancel_Click
End If
End Sub``````

You could open the add-in like a regular workbook, by double-clicking on it in Windows Explorer, dragging it onto the Excel window from Windows, or by using Excel’s File Open dialog. Alternatively you could install it as an add-in, so it is always available:

• In Excel, click on the File tab or in Excel 2007 the big round Office button in the top left corner
• Select Options (or Excel Options) from the list
• Click Add-Ins in the dialog
• Click the Go button
• Click Browse and navigate to the folder containing .xlam add-in file
• Select the .xlam file and click OK a couple times to return to Excel

However the add-in is used, a new button is added to the end of the Data tab on the ribbon. Click the button to launch the code.

## VBA: A Simple Tool to Open a CSV File and Create Chart with Data Specified by User

In a recent Mr Excel post, a member asked how to convert a CSV File to Excel File with Dynamic Graphing Capability. This is a great topic for a tutorial, but too long for a forum answer.

## Problem Statement

• Convert a CSV file to an Excel workbook
• Create a chart based on
• User-specified chart type
• User-specified columns for X and Y

I expanded on this a little bit:

• Open a user-selected CSV file
• Save as an Excel workbook
• Display dialog for user to select
• Chart type
• Columns containing X and Y values for chart
• Create the desired chart

This is a pretty easy project, which would generally take me half a day or less, depending on other distractions.

## Approach

Essentially, I started with this simple VBA procedure, and a similarly simple UserForm (dialog).

``````Sub OpenCSVFileAndPlotData()
' 1. Get CSV file name
' 2. Open CSV file
' 3. Save as workbook
' 4. Parse file
' 5. Show dialog (select chart type, X values, Y values)
' 6. Draw chart
' 7. Save file
End Sub``````

My first time through development, I did the least I had to do to make it work. The second time through, I added a bunch of nice embellishments that should make things easier for users to just pick it up and run with it.

For both levels, the following sections have pieces of code describing what is happening, the complete code, and an example workbook.

## Building a Simple Solution

### Code Workbook

I’m using a macro-enabled Excel workbook named My_CSV_Data_Processor.xlsm. It has a single worksheet names Parameters.

#### Saved Information: Named Range Containing Chart Types

The worksheet contains a list of chart types that our program will create. These are the simplest and most useful charts built into Excel. The list has two columns, the first being the integer values of Excel’s chart type constants (e.g., `xlArea` = 1, `xlXYScatter` = -4169), the second being the chart type names that will appear in the dialog.

The range A2:B12 has been named ChartTypes, so it will be easy for the code to put these values into the dialog.

#### Starting the Program: Form Control Button

To add a button, the Developer tab must be visible on the ribbon. If you don’t see it, right-click on a blank place in the ribbon, choose Customize the Ribbon. In the list on the right (Main Tabs), click the box in front of Developer.

On the Developer tab, in the Controls group, click the Insert dropdown, then under Form Controls, click on the first icon, Button. Draw a button on the worksheet.

A dialog will pop up asking which VBA procedure to assign to the button; you can revisit this dialog by right-clicking on the button. Select OpenCSVFileAndPlotData and press OK. Then type a caption on the button and resize it as needed.

### Code Module

We need to put the code into a regular code module. In the VB Editor, find the workbook’s project in the Project Explorer pane, right click anywhere on it, and choose Insert > Module. When a new code module opens up in the editor, rename it to something useful like MChartFromCSVFile using the Properties pane. The exact name you use isn’t critical, but it should be somewhat descriptive.

On the View menu, click Project Explorer (Ctrl+R) or Properties (F4) if these panes are not visible.

The first line of the module should be

``Option Explicit``

If this statement doesn’t appear, type it in, then go to Tools menu > Options, and check the Require Variable Declaration box. While you’re in the Options dialog, uncheck Auto Syntax Check.

After a blank line or two, type Sub and the name of the procedure. The VB Editor automatically skips another line and types End Sub for you:

``````Sub OpenCSVFileAndPlotData()

End Sub``````

The main program will be typed between Sub and End Sub. All the variables will be declared at the top using Dim statements. Variables don’t need to be declared at the top of a routine, but VBA offers no just-in-time benefit to declaring them right where you start using them, and I like having them in one place so I can find their declarations easily. In the sections below I will insert a simple Dim statement for the variables used.

#### 1. Get CSV File Name

We can use Excel’s GetOpenFileName to allow the user to select a CSV file from his computer.

``````  Dim sCSVFullName As String
sCSVFullName = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , _
"Select a CSV file", , False)``````

We’ve specified the CSV file filter, indicated a title for the Open File dialog, and said False to selecting multiple files.

#### 2. Open CSV file

This part is easy, we just need to open the file named in the previous step. Then we’ll set a workbook object variable to this file, so it’s easy to reference later in the code.

``````  Dim wb As Workbook
Workbooks.Open sCSVFullName
Set wb = ActiveWorkbook``````

#### 3. Save as workbook

We need to remove the file extension from the CSV file name (which includes the file path), which we do by retaining only text up to the last period in the file name. Then we add the Excel workbook file extension, and save the file, specifying the default workbook format.

``````  Dim sWbkFullName As String, sFileRoot As String
sFileRoot = Left\$(sCSVFullName, InStrRev(sCSVFullName, ".") - 1)
sWbkFullName = sFileRoot & ".xlsx"
wb.SaveAs sWbkFullName, xlWorkbookDefault``````

#### 4. Parse file

Now the file is open, so we’ll extract some information to place in the dialog. We’ll use a two-column display, where the first column has the column number, and the second a simple comma-delimited list of the first few values in that column.

``````  Dim ws As Worksheet, rng As Range, vRng As Variant
Dim iRows As Long, iCols As Long, iRow As Long, iCol As Long
Dim sTemp As String
Dim vChartData As Variant

Set ws = wb.Worksheets(1)
Set rng = ws.UsedRange
vRng = rng.Value2
iRows = rng.Rows.Count
iCols = rng.Columns.Count

'' info to display: column number, first few rows of column
ReDim vChartData(1 To iCols, 1 To 2)
For iCol = 1 To iCols
vChartData(iCol, 1) = iCol ' column number
sTemp = ""
For iRow = 1 To 4 ' first 4 values
sTemp = sTemp & vRng(iRow, iCol) & ", "
Next
sTemp = Left\$(sTemp, Len(sTemp) - 2) ' remove last comma
vChartData(iCol, 2) = sTemp
Next``````

#### 5. Show dialog

We need to instantiate the UserForm (i.e., load it into memory), pass in the array of column numbers and initial column values, and display the form. At this point, code stops and waits for the user to make his selections and dismiss the form.

When the form is dismissed, we need to get the user’s selections: chart type, whether the CSV file has header labels in the first row, and the columns to be used for X and Y values in the chart. Then we remove the UserForm from memory.

``````  Dim bFirstRowHeaders As Boolean
Dim myChartType As XlChartType
Dim vX As Variant, vY As Variant
Dim frmChartFromCSVFile As FChartFromCSVFile

Set frmChartFromCSVFile = New FChartFromCSVFile
With frmChartFromCSVFile
'' pass in information we know
.ChartData = vChartData

.Show

'' get information selected by user
myChartType = .ChartType
vX = .Xcolumns
vY = .YColumns
End With

#### 6. Draw chart

We need to figure out how to separate the data into separate series, then we need to get the data into the chart.

In XY charts, we let the user create one or more series, where (a) all series share the same set of X values (or no X values if the user didn’t specify them, and Excel will use 1, 2, 3, etc. as X values) and each series has a unique set of Y values, (b) all series share the same set of Y values and each has a unique set of X values, or (c) each series has its own unique sets of X and Y values.

For other chart types, the only relevant combination of X and Y is (a), since Excel uses the same X values for all series regardless of how we specify them. We will deal with this in the Advanced version of this program.

Excel 2013 introduced AddChart2 as an improvement over Excel 2007’s AddChart method. AddChart is hidden in Excel 2013, but AddChart2 will crash Excel 2007 and 2010, so we will use AddChart. In the Advanced program, we will enhance the code to use Excel 2013’s improved default styles while still using the error-free AddChart method.

After adding the chart, we add one series at a time, adding its Y values, X values, and name separately.

``````  Dim iX As Long, iY As Long, iSrs As Long
Dim nX As Long, nY As Long, nSrs As Long
Dim rCht As Range, cht As Chart, srs As Series

'' define some series parameters
If IsEmpty(vX) Then
nX = 0
Else
nX = UBound(vX, 1) + 1 - LBound(vX, 1)
End If
nY = UBound(vY, 1) + 1 - LBound(vY, 1)
nSrs = nY
If nX > nY Then nSrs = nX
Set rCht = rng.Offset(1).Resize(iRows - 1)
Else
Set rCht = rng
End If

'' select blank cell before inserting chart
rng.Offset(iRows + 1, iCols + 1).Resize(1, 1).Select

Set cht = ws.Shapes.AddChart.Chart '' Excel 2007+

'' chart type
With cht
If myChartType <> 0 Then
.ChartType = myChartType
End If

For iSrs = 1 To nSrs
Set srs = .SeriesCollection.NewSeries
With srs
' X values
If nX = 0 Then
' no X values specified
ElseIf nX = 1 Then
' all series share X values
.XValues = rCht.Columns(CLng(vX(0, 0)))
Else
' each series has unique X values
.XValues = rCht.Columns(CLng(vX(iSrs - 1, 0)))
End If
' Y values
If nY = 1 Then
' all series share Y values
.Values = rCht.Columns(CLng(vY(0, 0)))
Else
' each series has unique Y values
.Values = rCht.Columns(CLng(vY(iSrs - 1, 0)))
End If
' series name
If nSrs = nY Then
.Name = "=" & rng.Cells(1, CLng(vY(iSrs - 1, 0))). _
ElseIf nSrs = nX Then
.Name = "=" & rng.Cells(1, CLng(vX(iSrs - 1, 0))). _
End If
End If
End With
Next
End With``````

#### 7. Save file

Simple: save changes.

``  wb.Save``

### UserForm

Right click on the workbook’s project in the Project Explorer pane, click Insert > UserForm. When the UserForm appears, give it the name FChartFromCSVFile in the properties pane. This name is descriptive, and is how your code references the UserForm.

#### UserForm Controls

The Userform contains the following important controls, with the important properties shown below:

btnOK – OK button
` Default: True`

lstChartType – Listbox with two columns, one hidden.
``` ColumnCount: 2 ColumnWidths: 0 pt;112 pt Width: 120 Height: 150```

lstChartData – Listbox with two columns, extended multiselect
``` ColumnCount: 2 ColumnWidths: 42 pt;145 pt Width: 195 Height: 150 MultiSelect: 2 - fmMultiSelectExtended```

lstX, lstY – simple listboxes

lblbtnX, lblbtnY, lblbtnReset – “Label Buttons” or Labels with raised appearance (like regular buttons)
` SpecialEffect: 1 - fmSpecialEffectRaised`

Plus a few labels that help the user understand the dialog.

#### UserForm Code

Right click on the UserForm in the Project Explorer, and click on View Code. The Dialog’s code module will appear. Much of this code responds to events on the UserForm, events like clicking buttons. This code also includes properties, which allow the calling procedure to pass information into the UserForm and get information back from it.

As with the regular code module, the UserForm module should begin with

``Option Explicit``

If a control on the UserForm is to have code associated with it, double-click on the control and the VB Editor will insert a short stub of code into the module. For example, if you double-click on the OK button (named btnOK), the Editor will insert this:

``````Private Sub btnOK_Click()

End Sub``````

To make the code useful, we only need to insert our statements within this stub.

When the user clicks the OK button, we want the form to be hidden but remain in memory:

``````Private Sub btnOK_Click()
Me.Hide
End Sub``````

When the UserForm is first loaded, we want the information from the named range ChartTypes to appear in the listbox lstChartTypes. The UserForm_Initialize code runs when the UserForm is loaded, and the code shown here does the magic:

``````Private Sub UserForm_Initialize()
Dim vChartTypes As Variant

vChartTypes = ThisWorkbook.Names("ChartTypes").RefersToRange.Value2
Me.lstChartTypes.List = vChartTypes
End Sub``````

The button-formatted labels need some simple code attached, so that clicking them will populate the X and Y column lists. Click the lblbtnX label button to populate the lstX listbox:

``````Private Sub lblbtnX_Click()
Dim iLst As Long

For iLst = 1 To Me.lstChartData.ListCount
If Me.lstChartData.Selected(iLst - 1) Then
End If
Next
End Sub``````

Click the lblbtnY label button to populate the lstY listbox:

``````Private Sub lblbtnY_Click()
Dim iLst As Long

For iLst = 1 To Me.lstChartData.ListCount
If Me.lstChartData.Selected(iLst - 1) Then
End If
Next
End Sub``````

Click the lblbtnReset lable button to clear the X and Y listboxes and start over:

``````Private Sub lblbtnReset_Click()
Me.lstX.Clear
Me.lstY.Clear
End Sub``````

We need the ChartData property to pass the information to display in the lstChartData listbox of the UserForm:

``````Public Property Let ChartData(vData As Variant)
Me.lstChartData.List = vData
End Property``````

We also needproperties to let us extract information from the UserForm: whether the first row of the CSV file has header labels, the selected chart type, and the X and Y columns to be plotted:

``````Property Get FirstRowHeaders() As Boolean
End Property

Public Property Get ChartType() As XlChartType
With Me.lstChartTypes
If .ListIndex > -1 Then
ChartType = CLng(.List(.ListIndex, 0))
End If
End With
End Property

Public Property Get Xcolumns() As Variant
Xcolumns = Me.lstX.List
End Property

Public Property Get YColumns() As Variant
YColumns = Me.lstY.List
End Property``````

## The Simple Tool

### Code Module MChartFromCSVFile

Here is the complete listing of MChartFromCSVFile:

``````Option Explicit

Sub OpenCSVFileAndPlotData()
Dim sCSVFullName As String, sWbkFullName As String, sFileRoot As String
Dim wb As Workbook, ws As Worksheet, rng As Range, vRng As Variant
Dim rCht As Range, cht As Chart, srs As Series
Dim iRows As Long, iCols As Long, iRow As Long, iCol As Long
Dim sTemp As String
Dim vChartData As Variant
Dim myChartType As XlChartType
Dim vX As Variant, vY As Variant
Dim iX As Long, iY As Long, iSrs As Long
Dim nX As Long, nY As Long, nSrs As Long
Dim frmChartFromCSVFile As FChartFromCSVFile

' 1. Get CSV file name
sCSVFullName = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , "Select a CSV file", , False)

' 2. Open CSV file
Workbooks.Open sCSVFullName
Set wb = ActiveWorkbook

' 3. Save as workbook
sFileRoot = Left\$(sCSVFullName, InStrRev(sCSVFullName, ".") - 1)
sWbkFullName = sFileRoot & ".xlsx"
wb.SaveAs sWbkFullName, xlWorkbookDefault

' 4. Parse file
Set ws = wb.Worksheets(1)
Set rng = ws.UsedRange
vRng = rng.Value2
iRows = rng.Rows.Count
iCols = rng.Columns.Count

'' info to display: column number, first few rows of column
ReDim vChartData(1 To iCols, 1 To 2)
For iCol = 1 To iCols
vChartData(iCol, 1) = iCol
sTemp = ""
For iRow = 1 To 4
If iRow > iRows Then Exit For
sTemp = sTemp & vRng(iRow, iCol) & ", "
Next
sTemp = Left\$(sTemp, Len(sTemp) - 2)
vChartData(iCol, 2) = sTemp
Next

' 5. Show dialog (get chart type, X values, Y values)
Set frmChartFromCSVFile = New FChartFromCSVFile
With frmChartFromCSVFile
'' pass in information we know
.ChartData = vChartData

.Show

myChartType = .ChartType
vX = .Xcolumns
vY = .YColumns
End With

' 6. Draw chart
'' define some series parameters
If IsEmpty(vX) Then
nX = 0
Else
nX = UBound(vX, 1) + 1 - LBound(vX, 1)
End If
nY = UBound(vY, 1) + 1 - LBound(vY, 1)
nSrs = nY
If nX > nY Then nSrs = nX
Set rCht = rng.Offset(1).Resize(iRows - 1)
Else
Set rCht = rng
End If

'' select blank cell before inserting chart
rng.Offset(iRows + 1, iCols + 1).Resize(1, 1).Select

Set cht = ws.Shapes.AddChart.Chart '' Excel 2007+
''Set cht = ws.Shapes.AddChart2.Chart '' Excel 2013 only

'' chart type
With cht
.ChartType = myChartType

For iSrs = 1 To nSrs
Set srs = .SeriesCollection.NewSeries
With srs
' X values
If nX = 0 Then
' no X values specified
ElseIf nX = 1 Then
' all series share X values
.XValues = rCht.Columns(CLng(vX(0, 0)))
Else
' each series has unique X values
.XValues = rCht.Columns(CLng(vX(iSrs - 1, 0)))
End If
' Y values
If nY = 1 Then
' all series share Y values
.Values = rCht.Columns(CLng(vY(0, 0)))
Else
' each series has unique Y values
.Values = rCht.Columns(CLng(vY(iSrs - 1, 0)))
End If
' series name
If nSrs = nY Then
.Name = "=" & rng.Cells(1, CLng(vY(iSrs - 1, 0))).Address(True, True, xlA1, True)
ElseIf nSrs = nX Then
.Name = "=" & rng.Cells(1, CLng(vX(iSrs - 1, 0))).Address(True, True, xlA1, True)
End If
End If
End With
Next
End With

' 7. Save file
wb.Save

ExitProcedure:

End Sub``````

### UserForm Module FChartFromCSVFile

Here is the complete listing of FChartFromCSVFile:

``````Option Explicit

Private Sub btnOK_Click()
Me.Hide
End Sub

Private Sub lblbtnReset_Click()
Me.lstX.Clear
Me.lstY.Clear
End Sub

Private Sub lblbtnX_Click()
Dim iLst As Long

For iLst = 1 To Me.lstChartData.ListCount
If Me.lstChartData.Selected(iLst - 1) Then
End If
Next
End Sub

Private Sub lblbtnY_Click()
Dim iLst As Long

For iLst = 1 To Me.lstChartData.ListCount
If Me.lstChartData.Selected(iLst - 1) Then
End If
Next
End Sub

Public Property Let ChartData(vData As Variant)
Me.lstChartData.List = vData
End Property

End Property

Public Property Get ChartType() As XlChartType
With Me.lstChartTypes
If .ListIndex > -1 Then
ChartType = CLng(.List(.ListIndex, 0))
End If
End With
End Property

Public Property Get Xcolumns() As Variant
Xcolumns = Me.lstX.List
End Property

Public Property Get YColumns() As Variant
YColumns = Me.lstY.List
End Property

Private Sub UserForm_Initialize()
Dim vChartTypes As Variant

vChartTypes = ThisWorkbook.Names("ChartTypes").RefersToRange.Value2
Me.lstChartTypes.List = vChartTypes
End Sub``````

### Workbook with Working Code

You can download the simple workbook My_CSV_Data_Processor.xlsm to see all of this code in one place, and to see how it works.