## The xkcd Velociraptor Problem #1

In Substitute, we learn of a substitute teacher’s “real-life” math problems, which include the following:

Ha, ha, funny commentary on a frustrated old substitute math teacher. (Disclosure: my wife is an 8th grade math teacher, and frustrated is often descriptive of her job.)

So how far could you get before you become Velociraptor Chow? Rhett Allain, a science writer at Wired Magazine, shows us how to figure this out in Here’s How to Solve the xkcd Velociraptor Problem With Code. Prof. Allain describes a numerical approach to the problem using some physics and a bit of python code. Essentially you have some initial conditions, such as speed and position of predator and prey, and some boundary conditions, such as acceleration and maximum speed of each. You select a suitably short time increment, compute the speed due to acceleration and distance traveled based on speed, and update the conditions. If the dinosaur hasn’t yet caught the primate, you again increment the time, update the conditions, and so on, until the two participants coincide in space and time.

You could use your best 8th grade algebra (or my wife’s) and compute the solution analytically, but that’s more suited to paper and pencil, not to a computer. And the numerical approach can be applied to many phenomena, physical and other (including financial).

An algebraic solution?

We’ll use Excel to carry out these same calculations, both as a set of worksheet formulas and as a VBA routine.

In his Calvin and Hobbes comic strip, Bill Watterson has touched on velociraptor-human conflict, not as a physics problem but as a solution to an ecological problem (human overpopulation).

## Approach 1. Using Excel Table with Simple Formulas

Using an Excel table allows you to make iterative calculations easily. Once you get the formulas right, you can then extend the calculations by adding rows to the table.

Below is the initial setup of the velociraptor problem. Calculations are compiled in a table in the top left of the worksheet. Parameters for the problem are listed in a range nearby and are used in definitions for Names that make setting up the problem easier (see definitions below). The chart plots positions of the velociraptor and of the human on the Y axis vs. time on the X axis.

This screen shot of Excel’s Name Manager dialog shows the Names which were defined to facilitate calculations. `Delta_t` is the time increment between calculated time points in the table. `XstartV` and `XstartH` are the initial positions of the velociraptor and of the human. `AccelV` and `AccelH` are the acceleration for the velociraptor and for the human. `VmaxV` and `VmaxH` are the maximum running speeds for the velociraptor and for the human.

The table contains these column formulas:

``````Time
0,
OFFSET([@Time],-1,0)+Delta_t)``````
``````Vvel (velocity of the velociraptor)
=IF([@Time]=0,
0,
IF(OFFSET([@Vvel],-1,0)>=VmaxV,
VmaxV,
MIN(OFFSET([@Vvel],-1,0)+AccelV*Delta_t,VmaxV)))``````
``````Xvel (position of the velociraptor)
=IF([@Time]=0,
XstartV,
OFFSET([@Xvel],-1,0)+[@Vvel]*Delta_t)``````
``````Vhum (velocity of the human)
=IF([@Time]=0,
0,
IF(OFFSET([@Vhum],-1,0)>=VmaxH,
VmaxH,
MIN(OFFSET([@Vhum],-1,0)+AccelH*Delta_t,VmaxH)))``````
``````Xhum (position of the human)
=IF([@Time]=0,
XstartH,
OFFSET([@Xhum],-1,0)+[@Vhum]*Delta_t)``````

This screenshot shows the table populated to an elapsed time of 2 seconds. The human has just reached his maximum velocity, but the velociraptor is still accelerating. The dinosaur has gotten slightly closer to the human.

You can extend the chase by extending the table, simply by clicking on the small angle-iron at the bottom right corner of the tab and dragging it down as far as needed.

Below, the table has been filled down to 6.5 seconds (the rows between 1 sec and 5 sec have been hidden). We see from the chart that the velociraptor has overtaken the human, and I’ve indicated with red text the row in the table where the position of the velociraptor has first passed the position of the human (row 62, 5.9 sec).

This simplistic model doesn’t stop when the velociraptor reaches his dinner. We need to insert some intelligence into the formulas in our table.

## Approach 2. Using Excel Table with More Detailed Formulas

Using a more detailed table allows you to calculate the point of intersection of the paths of the velociraptor and the human, and stop the chase at that time.

Below is the initial setup of the velociraptor problem.

There are a few additional columns:

``````TTime (adjusted time)
0,
IF(OFFSET([@TTime],-1,0)<>OFFSET([@Time],-1,0),
NA(),
IF([@Xvel]<=[@Xhum],
[@Time],
(OFFSET([@Xhum],-1,0)-OFFSET([@Xvel],-1,0))/
(OFFSET([@Xhum],-1,0)-OFFSET([@Xvel],-1,0)
-[@Xhum]+[@Xvel])*Delta_t+OFFSET([@Time],-1,0))))``````
``````XXvel (adjusted position of the velociraptor)
=IF([@Time]=[@TTime],
[@Xvel],
OFFSET([@Xvel],-1,0)+[@Vvel]*([@TTime]-OFFSET([@TTime],-1,0)))``````
``````XXhum (adjusted position of the human)
=IF([@Time]=[@TTime],
[@Xhum],
OFFSET([@Xhum],-1,0)+[@Vhum]*([@TTime]-OFFSET([@TTime],-1,0)))``````

The formula for `TTime` does a lot of work. It’s equal to zero in the first data row, it fills with #N/A after the velociraptor reaches the human, and if the velociraptor catches the human during the current time increment, it interpolates to find the precise time that this happens (see the red entries in row 61).

The chart shows the paths of the predator and prey only up to the point of capture, at 5.8375 sec, or 29.325 m from the human’s initial position.

## Approach 3. Using Excel VBA

You can use Excel VBA to solve this velociraptor problem, and animate the chart which illustrates the chase. Initial and boundary conditions are found in the small table in columns G and H.

Columns A through E are the calculations, as in the first approach, except the results are not calculated by worksheet formulas, but instead are calculated by VBA and output to the table as values. These values are plotted in the larger chart.

The small table in columns J through L show the initial and current (i.e., in the most recently calculated iteration) positions of human and velociraptor. These values are plotted in the smaller chart.

Clicking the `Reset Chase` button runs the `ResetChase` procedure (below), which sets conditions back to the start of the chase, by deleting all table rows after the first data row.

``````Sub ResetChase()
Application.ScreenUpdating = False
With ActiveSheet.ListObjects(1)
Do Until .ListRows.Count = 1
.ListRows(.ListRows.Count).Delete
Loop
End With
Application.ScreenUpdating = True
End Sub``````

Clicking the `Start Chase` button runs the `StartChase` procedure (below), which starts the chase and runs it until the end.

``````Sub StartChase()
Dim tTime0 As Double, tTime1 As Double
Dim xXhum0 As Double, xXhum1 As Double
Dim xXvel0 As Double, xXvel1 As Double
Dim vVhum0 As Double, vVhum1 As Double
Dim vVvel0 As Double, vVvel1 As Double
Dim vVmaxH As Double, AccelH As Double
Dim vVmaxV As Double, AccelV As Double
Dim DeltaT As Double, DDelTT As Double
Dim iDelay As Long, iLooper As Long
Dim ws As Worksheet

ResetChase

' initialize
Set ws = ActiveSheet
tTime0 = 0
iLooper = ws.Range("Looper").Value2
xXhum0 = ws.Range("XstartH").Value2
xXvel0 = ws.Range("XstartV").Value2
vVhum0 = 0
vVvel0 = 0
vVmaxH = ws.Range("VmaxH").Value2
vVmaxV = ws.Range("VmaxV").Value2
AccelH = ws.Range("AccelH").Value2
AccelV = ws.Range("AccelV").Value2
DeltaT = ws.Range("Delta_t").Value2

' loop
Do
tTime1 = tTime0 + DeltaT

' calculate human velocity and position
If vVhum0 >= vVmaxH Then
vVhum1 = vVmaxH
Else
vVhum1 = vVhum0 + AccelH * DeltaT
If vVhum1 > vVmaxH Then
vVhum1 = vVmaxH
End If
End If
xXhum1 = xXhum0 + vVhum1 * DeltaT

' calculate velociraptor velocity and position
If vVvel0 >= vVmaxV Then
vVvel1 = vVmaxV
Else
vVvel1 = vVvel0 + AccelV * DeltaT
If vVvel1 > vVmaxV Then
vVvel1 = vVmaxV
End If
End If
xXvel1 = xXvel0 + vVvel1 * DeltaT

' has velociraptor caught human?
If xXvel1 > xXhum1 Then
DDelTT = DeltaT * (xXhum0 - xXvel0) / _
((xXhum0 - xXvel0) + (xXvel1 - xXhum1))
tTime1 = tTime0 + DDelTT
xXhum1 = xXhum0 + vVhum1 * DDelTT
xXvel1 = xXvel0 + vVvel1 * DDelTT
End If

' add new time point data to row below table
With ws.ListObjects(1)
.ListRows(.ListRows.Count).Range.Offset(1).Value = _
Array(tTime1, vVvel1, xXvel1, vVhum1, xXhum1)
End With

' exit if we're done
If xXvel1 >= xXhum1 Then
Exit Do
End If

' build in delay if animation runs too quickly on screen
For iDelay = 1 To iLooper
DoEvents
Next
DoEvents

' persist previous loop's data
tTime0 = tTime1
vVhum0 = vVhum1
xXhum0 = xXhum1
vVvel0 = vVvel1
xXvel0 = xXvel1
Loop

End Sub``````

As the VBA code runs and data is added to the table, you can watch the chase progress in the two charts. Here is the chase after two seconds.

Here is the chase after four seconds.

And here is the chase at its conclusion.

Same result as the table based approaches, except for the added benefit of watching the animation as the VBA calculations proceed.

## VBA: Accuracy vs. Calculation Load

Obviously the accuracy of our numerical solution depends on the size of the time increment we use in our calculations. If we take smaller time increments, we can reduce the error resulting from treating nonlinear behavior (acceleration) as linear. On the other hand, taking smaller time increments means our program has to make more calculations, and therefore it will run more slowly.

I ran a modified `StartChase` procedure to determine how time increment size affected elapsed time, distance, number of iteractions, total calculation time, and calculation time per iteration. I turned off screen updating during this procedure and did not output the results of each increment to the worksheet, to ignore the time VBA spends communicating with the Excel worksheet.

Here are the effects of increment time on solution accuracy and calculation time. The blue shaded row shows the solution used in the examples above, an increment of 0.1 sec.

We learn some interesting things if we plot this data.

If we plot computed elapsed time to capture of the human vs time increment (Delta_T), we see a straight line with an almost perfect correlation (below left). The computed elapsed time gets closer and closer to the Y intercept as the time increment decreases. We could say that this Y-intercept is the actual time of capture, and our solutions get closer and closer to predicting it as the error in the incremental calculations is minimized. In fact, the difference between the time increment used above (0.1 sec) and the Y-intercept is only 0.05 sec. Maybe we can decide that our analytical solution using an increment of 0.1 sec is “accurate enough”.

If we plot distance to capture vs time increment (below right), we see a trend, but not a nice linear trend as with the elapsed time chart. However, we see that for time increments of 0.1 sec or shorter, there is barely any deviation from the 0.1 sec computation of 29.325 m.

Naturally, a smaller time increment will result in more iterations, in an inverse relationship. The chart below left shows a linear scatter chart, which isn’t very interesting; the data for a large increment (0.5 s) is found out on the X axis. Shortening the increment brings the points back along the X axis until they curve around and head up the Y axis. What is happening is shown better in the log-log plot below right. This is a straight line, and the exponent on X is -0.9995, extremely close to the inverse 1/X relationship we expect.

We would expect a similar inverse relationship between total calculation time and time increment. The linear chart below left shows the same axis-hugging behavior as above, and the log-log chart below right gives us another straight line. This one isn’t as perfectly straight, but a regression on the points for smaller increments (filled points) has a strong correlation and a nearly 1/X relationship.

For the time increment of 0.1 sec from the examples above, we have a total calculation time of 0.05 sec, which is pretty fast for an “accurate enough” solution. If we really want more accuracy, we could even go to a 1 sec solution based on a o.oo2 time increment.

Keep in mind that these calculation times are for a modified procedure, not the procedure that updates the table and chart after each calculation increment. Our 0.1 sec time increment may give the solution in 0.05 sec, but the time we spend watching each increment update the table and chart is more like 4 or 5 seconds.

Finally, we see that as the number of iterations increases, the calculation time increases linearly.

This article will show how easy it is to create a scatter chart with its plot area divided horizontally and vertically into four regions. The regions are separated by the chart axes, and these axes can be positioned where needed to demarcate the quadrants.

I have written a tutorial showing how to create an Excel Chart With Colored Quadrant Background, which was more complicated, as it used stacked areas and secondary axes to get the colored background. This is much simpler to create and maintain, and serves much the same purpose.

Here is the sample X and Y data, with calculated averages, and the initial XY scatter chart. We will position the axes at the respective averages of the X and Y data, though you can position them wherever it makes sense in your analysis.

We need to reposition the axes of this chart. Double click the horizontal axis, or select the horizontal axis and press Ctrl+1 (numeral one), to open the Format Axis task pane (shown here, Excel 2013) or Format Axis dialog (works much the same in earlier Excel versions). Under Axis Options >Vertical Axis Crosses, select the Axis Value option, and enter the X average into the box, as shown.

The result is shown below left. Repeat for the vertical axis, below right.

Those axis labels are totally in the way, but it’s easy to move them. Format each axis (open the task pane or dialog as above) and under Labels > Label Position, select Low from the dropdown.

Now those labels are along the edges of the chart, where they do more good than harm (below left). You can do a small amount of formatting to make the quadrants stick out a bit more clearly. In the chart below right, I’ve used a lighter shade of gray for the gridlines, and I’ve used a darker color, in fact, the same color as the markers, for the axis line color.

It is easy to use VBA to position the axes and axis labels, using a simple routine like that shown below. This routine positions the labels, then uses the averages calculated in the worksheet to position the axis lines.

``````Sub AxesAsQuadBoundaries1()
With ActiveChart
With .Axes(xlCategory)
.TickLabelPosition = xlTickLabelPositionLow
.CrossesAt = ActiveSheet.Range("A17").Value2
End With
With .Axes(xlValue)
.TickLabelPosition = xlTickLabelPositionLow
.CrossesAt = ActiveSheet.Range("B17").Value2
End With
End With
End Sub``````

The next routine skips the worksheet calculations, instead taking the averages of the X and Y values plotted in the chart to position the axis lines.

``````Sub AxesAsQuadBoundaries2()
Dim vData As Variant
With ActiveChart
vData = .SeriesCollection(1).XValues
With .Axes(xlCategory)
.TickLabelPosition = xlTickLabelPositionLow
.CrossesAt = WorksheetFunction.Average(vData)
End With
vData = .SeriesCollection(1).Values
With .Axes(xlValue)
.TickLabelPosition = xlTickLabelPositionLow
.CrossesAt = WorksheetFunction.Average(vData)
End With
End With
End Sub``````

You can even use worksheet events to reposition the chart axes. My X and Y values were calculated using `=RANDBETWEEN(2,14)`, so whenever the worksheet calculates (e.g., by pressing the F9 function key), the values change. I can tap into this calculation event as follows.

• Right click on the worksheet tab, and select View Code from the popup menu. The VB Editor opens with a code module corresponding to the worksheet.
• Select Worksheet from the left hand dropdown at the top of the new code module.
• Select Calculate from the right hand dropdown.
• Enter the code as shown.

Here is the code so you don’t need to type it all yourself. Simply copy and paste into the worksheet’s code module.

``````Private Sub Worksheet_Calculate()
Dim vData As Variant
With Me.ChartObjects(1).Chart
vData = .SeriesCollection(1).XValues
With .Axes(xlCategory)
.TickLabelPosition = xlTickLabelPositionLow
.CrossesAt = WorksheetFunction.Average(vData)
End With
vData = .SeriesCollection(1).Values
With .Axes(xlValue)
.TickLabelPosition = xlTickLabelPositionLow
.CrossesAt = WorksheetFunction.Average(vData)
End With
End With
End Sub``````

Depending on the details of your worksheet model, you could use the `Worksheet_Calculate`, `Worksheet_Change`, `Worksheet_PivotTableUpdate`, or other event procedures to update the chart.

## Structured Referencing to Identify Parts of Excel Tables

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

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

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

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

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

## Structured Referencing

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

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

``=A2*B2``

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

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

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

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

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

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

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

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

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

#### Single-column cell reference

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

``[@Column]``

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

``[@[A Column]]``

#### Multi-column cell reference

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

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

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

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

### This Row

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

### Special Characters

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

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

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

### Special Identifiers

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

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

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

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

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

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

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

#### Total row visible formula:

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

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

## In Data Validation

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

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

Name: “Countries”

Refers to: “=tCountries[Countries]”

## In Charts

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

## In Formulas

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

There are five specific locations we can reference:

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

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

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

``TableName[Column Name]``

### Specific Column Including Header and Total Rows

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

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

``TableName``

### All Data Including Header and Total Rows

``TableName[#All]``

### Row Across All Columns

``TableName[@]``

### Row for One Column (Single Cell)

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

### Header Row Across All Columns

``TableName[#Headers]``

### Header Row for One Column

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

### Total Row Across All Columns

``TableName[#Totals]``

### Total Row for One Column

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

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

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

``TableName[Column 3]``

### Specific Column Including Header and Total Rows

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

### Header Row for One Column

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

### Total Row for One Column

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

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

``TableName[@Column 3]``

## In VBA

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

### Using Range and Evaluate

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

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

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

### Using the Object Model’s ListObject

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

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

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

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

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

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

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

### Range Property

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

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

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

``````Dim Table1HeaderRowRange As Range

### DataBodyRange Property

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

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

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

### TotalRowRange Property

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

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

### InsertRowRange Property

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

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

### ListRows Property

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

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

### ListColumns Property

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

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

## Apply Custom Data Labels to Charted Points

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

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

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

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

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

## Manually Type Desired Text for Each Label

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

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

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

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

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

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

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

## Manually Link Each Label to Cell with Desired Text

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

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

## Use the Chart Labeler Program

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

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

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

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

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

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

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

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

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

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

## Write Your Own VBA Routines

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

### Add Data Labels from Range Selected by User

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

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

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

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

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

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

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

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

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

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

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

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