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

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

Bullet Graph

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).

Peltier Tech Chart Utility

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.

Starting Point for Table Formula 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.

[#All]

Table Special Identifier [#All]

[#Headers]

Table Special Identifier [#Headers]

[#Data]

Table Special Identifier [#Data]

[#Totals]

Table Special Identifier [#Totals]

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

Table Special Identifier @ or [#This Row]

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.

Header row visible formula:

=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 header
  • 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]

Referencing Table Column Excluding Header and Total Rows

Specific Column Including Header and Total Rows

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

Referencing Table Column Including Header and Total Rows

Header Row for One Column

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

Referencing Header Row for One Column

Total Row for One Column

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

Referencing Total Row for One Column

Row for One Column (Single Column Cell)

TableName[@Column 3]

Referencing One Cell

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

HeaderRowRange Property

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
Set Table1HeaderRowRange = Table1.HeaderRowRange

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."

Peltier Tech Chart Utility

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.

Simple Chart with Custom Data Labels

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.

Simple Chart with Default Data Labels

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.

Simple Chart with Data Labels Selected

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

Simple Chart with Specific Data Label Selected

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

Simple Chart with Data Label Text Selected

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

Simple Chart with Data Label Text Being Typed

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).

Simple Chart with Data Label Link Being Entered

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.

XY Chart Labeler Ribbon Tab

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.

XY Chart Labeler Dialog

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.

XY Chart Labeler Dialog

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.

Format Data Labels Task Pane

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.

Format Data Labels Task Pane

Select your data label range.

Format Data Labels Task Pane

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.

Format Data Labels Task Pane

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
            .Text = "=" & rng.Cells(iLbl).Address(External:=True)
            .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
            .Text = "=" & rng.Cells(iLbl).Address(External:=True)
            .Position = xlLabelPositionRight
          End With
        Next
      End If
    End If
  End If
End Sub

 

Peltier Tech Chart Utility

VBA: An Advanced Add-in to Open a CSV File and Create Chart with Data Specified by User

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.

Advanced Solution

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.

Code Workbook (Add-In)

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.

Create an 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
    bFirstRowHeaders = Not IsNumeric(vRng(1, iCol))
    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
    .FirstRowHeaders = bFirstRowHeaders
    
    .Show
    
    '' check whether user canceled
    bCancel = .Cancel
    If Not bCancel Then
      '' get information selected by user
      myChartType = .ChartType
      bFirstRowHeaders = .FirstRowHeaders
      vX = .Xcolumns
      vY = .YColumns
    End If
  End With
  Unload frmChartFromCSVFile

  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
  If bFirstRowHeaders Then
    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
    
    '' add series
    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 bFirstRowHeaders Then
          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
    
    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."
    Me.lstY.BackColor = miBAD_COLOR
  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
        Me.lstX.BackColor = miBAD_COLOR
        Me.lstY.BackColor = miBAD_COLOR
        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
        Me.lstX.BackColor = miBAD_COLOR
      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

  Me.lstX.AddItem 1
  For iLst = 2 To Me.lstChartData.ListCount
    Me.lstY.AddItem iLst
  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
      Me.lstX.AddItem iLst
      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 bFirstRowHeaders As Boolean
  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
    bFirstRowHeaders = Not IsNumeric(vRng(1, iCol))
    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
    .FirstRowHeaders = bFirstRowHeaders
    
    .Show
    
    '' check whether user canceled
    bCancel = .Cancel
    If Not bCancel Then
      '' get information selected by user
      myChartType = .ChartType
      iChartStyle = .ChartStyle
      bFirstRowHeaders = .FirstRowHeaders
      vX = .Xcolumns
      vY = .YColumns
    End If
  End With
  Unload frmChartFromCSVFile
  
  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
  If bFirstRowHeaders Then
    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
    
    '' add series
    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 bFirstRowHeaders Then
          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
    
    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."
    Me.lstY.BackColor = miBAD_COLOR
  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
        Me.lstX.BackColor = miBAD_COLOR
        Me.lstY.BackColor = miBAD_COLOR
        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
        Me.lstX.BackColor = miBAD_COLOR
      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

  Me.lstX.AddItem 1
  For iLst = 2 To Me.lstChartData.ListCount
    Me.lstY.AddItem iLst
  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
      Me.lstX.AddItem iLst
      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
      Me.lstY.AddItem iLst
    End If
  Next
End Sub

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

Property Let FirstRowHeaders(bFirstRowHeaders As Boolean)
  Me.chkFirstRowHeaders.Value = bFirstRowHeaders
End Property

Property Get FirstRowHeaders() As Boolean
  FirstRowHeaders = Me.chkFirstRowHeaders.Value
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

Add-In with Working Code

You can download the advanced add-in My_CSV_Data_Processor.xlam to see all of this code in one place, and to see how it works.

Using the Add-In

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.

Peltier Tech Chart Utility

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

The simple statement was this:

  • 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.

Getting Ready

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
    bFirstRowHeaders = .FirstRowHeaders
    vX = .Xcolumns
    vY = .YColumns
  End With
  Unload frmChartFromCSVFile

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
  If bFirstRowHeaders Then
    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
    
    '' add series
    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 bFirstRowHeaders Then
          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

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

chkFirstRowHeaders – Checkbox

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
      Me.lstX.AddItem iLst
    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
      Me.lstY.AddItem iLst
    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
  FirstRowHeaders = Me.chkFirstRowHeaders.Value
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 bFirstRowHeaders As Boolean
  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
    bFirstRowHeaders = .FirstRowHeaders
    vX = .Xcolumns
    vY = .YColumns
  End With
  Unload frmChartFromCSVFile
  
  ' 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
  If bFirstRowHeaders Then
    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
    
    '' add series
    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 bFirstRowHeaders Then
          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
      Me.lstX.AddItem iLst
    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
      Me.lstY.AddItem iLst
    End If
  Next
End Sub

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

Property Get FirstRowHeaders() As Boolean
  FirstRowHeaders = Me.chkFirstRowHeaders.Value
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.

Advanced Version

The next blog post has a number of enhancements that make things easier for the user and prevent various warning messages. Stay tuned…

 

Peltier Tech Chart Utility

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.