# Wind Direction Chart

My workmate has recently got me into going for lunchtime runs around the Refinery.
Running on sand is hard. As a person who has only ever run on the road, I found it very difficult to adjust.
I haven’t quite picked when yet, but sometimes the sand is really soft. It’s like running in quicksand.

After work, I decided to knock up a few Excel Charts to give me more information before my run.
It’s not really going to change my habits, but I feel easier knowing what I’m in for. OK, maybe I like a tailwind.

I’ve got realtime data for Tide Height, Wind Speed and Wind Direction.

Tide Height and Wind Speed I’ve done as standard Line Charts, but Wind Direction didn’t look quite right as a Line Chart so I decided to do something different.

15 minute averages extend for about 24 columns – 6 hours worth.

The formulas – a bit of Trig for the end point coordinates.
B6: 0
B7: =COS(B4/180)
B8: 0
B9: =SIN(B4/180)

The charts you see in the snapshot are Scatter Charts.

Steps to create a chart:

Select B8:B9. Hit the AutoChart button
Select the “Scatter with data points connected by lines without markers”. Click Next
Select the Series tab. For X Values select B6:B7. Click Finish
Select the Legend box and hit the Delete key to destroy it
Doubleclick the Y Axis to bring up the “Format Axis” window. Select the Scale tab
Set Minimum to -1 and Maximum to 1. Click OK. Hit the Delete key to destroy the Y axis
Repeat for the X Axis
Select the Gridlines and hit the Delete key to destroy it
Doubleclick the Plot Area to bring up the “Format Plot Area” window
Set the border to None and the Area to None. Click OK
Doubleclick the line. Set the Line Color to Black. Click OK
The line should still be selected. Note that two blobs are at the ends of the line.
Click the blob near the center of the chart. Then, doubleclick the blob
In the Marker section, set the Style to Circle, the Foreground to Black and the Size to 5. Click OK

Now you need to resize the chart so it fits in the width of one column. The top-left of the chart should start in cell B11.
This takes some fiddling since the Chart Area doesn’t want to play nice with the Chart Object which contains it.
Set the Zoom to 200% if it helps (but make sure you set it back to 100% afterward!)

I didn’t sit there adding all of the charts, I just created the left-most one and copied them across.
But, it’s not quite as simple as a copy/paste. A normal copy/paste would have all the charts still looking at column B.

Charts can not be relative.
That is, you can’t move the chart over a column and expect it to now look at column C.
Ranges do it fine, you just remove the \$ and that’s relative.
It appears that charts can’t use Relative References. It’s Absolute References only.

Here is why VBA is my friend.
While copying the original chart, it finds the column part of the Chart formula and adjust it to look at the destination column.

Make sure the Chart is selected, then run this macro:

Sub CopySelectedChartOver()
Const cColumns = 23

Dim i As Long, cob As ChartObject
Dim str As String, strCol As String, lngCol As Long

Select Case TypeName(Selection)
Case “PlotArea”, “ChartArea” ‘ensure the chart has been selected first
Set cob = Selection.Parent.Parent
strCol = cob.Chart.SeriesCollection(1).Formula

i = InStr(1, strCol, “\$”) ‘look for the column part of the =SERIES() formula
If i > 0 Then
strCol = Mid(strCol, i + 1, 2) ‘ take the AB out of “\$AB123:…”
If Not (Right(strCol, 1) >= “A” And Right(strCol, 1) <= “Z”) Then strCol = Left(strCol, 1)
lngCol = Range(strCol & “1”).Column

For i = 1 To cColumns
cob.Copy
cob.TopLeftCell.Offset(, i).Select
str = Split(Columns(lngCol).Offset(, i).Address, “:”)(0) ‘get the “AC” bit from a column number
ActiveSheet.Paste
With Selection.Parent.SeriesCollection(1)
.Formula = Replace(.Formula, “\$” & strCol, str) ‘replace the old \$AB with \$AC
End With
Next
End If

Case Else
MsgBox “The source chart must first be selected”, vbExclamation, “Selection Error”
End Select
End Sub

Finally, you can pretty it up by hiding the Coordinates. Set the Number Format for Rows 6:9 as ;;; (3 semi-colons)

Posted in Uncategorized

## 13 thoughts on “Wind Direction Chart”

1. Jon,

It was your speedo which enabled me to think about using trig with scatter line charts ;)

One thing I like about the “chart per column” idea is that you can use the window horz scrollbar and the chart looks animated.

Rob

2. Murray Shactman says:

What units of measurement are in cell B4 through E4?

If they are in degrees, then the formula in cell B7 should be COS(B4 * 3.1416[pi]/180).

I wrote some VBA code that would graphically indicate direction using the drawing objects (arrowhead line) on it. If you want it I’ll post it.

3. Murray,

You are correct. I must have been looking at a previous version.

What I settled on was =COS(B4 * PI() / 180)

When posting code in this comment box, replace:
” with &quot;
& with &amp;
< with &lt;
> with &gt;

4. Murray Shactman says:

Sub Direction()

Dim ang As Double
Dim length As Double
Dim centerX As Double ‘the starting point x points from upper left
Dim centerY As Double ‘the starting point y points from upper left
Dim y As Double
Dim x As Double
Dim i As Integer
Dim numLines As Integer

numLines = ActiveSheet.Shapes.Count ‘count number of shapes
For i = numLines To 1 Step -1
If ActiveSheet.Shapes(i).Type = 9 Then
ActiveSheet.Shapes(i).Delete ‘get rid of them
End If
Next i

centerX = Range(&quotB1&quot).Value ‘I just used empty cells
centerY = Range(&quotB3&quot).Value
length = Range(&quotB5&quot).Value ‘the length of arrow line in points
ang = Range(&quotB4&quot).Value – 180 ‘so that the arrow will point toward the
‘wind direction
rad = ang * Application.WorksheetFunction.Pi / 180
With ActiveSheet.Shapes.AddLine(centerX, centerY, centerX + x, centerY – y).Line
End With
End Sub

My first time posting code. I hope I did it right.

This is only for one wind direction of course.

5. Ajith says:

Hi Rob,
I am creating a chart using ChartSpaceClass.
oChartSpace.Charts[0].SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimCategories,Convert.ToInt32(OWC11.ChartSpecialDataSourcesEnum.chDataLiteral),names );
oChartSpace.Charts[0].SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimValues,Convert.ToInt32(OWC11.ChartSpecialDataSourcesEnum.chDataLiteral),totals );
‘names’ and ‘totals’ are twa tab limited strings.
I want this chart to be showed in an excel and save in the server.
I use SpreadsheetClass to generate excel sheet. How can I link this chart to my Spreadsheet, if I am not using the data from the spread sheet to create Chart.

If I use,
oChartSpace.ExportPicture( TargetGIF + strName, “gif”, 800, 600);

Ajith

6. I’ve built an app which is a vector map, essentially a scatter chart with a regular array of points, where each point used a custom line segment as its marker, with an arrowhead at one end, and the length and direction of the arrow were determined by values in a table.

7. Ajith –

Use Insert menu > Picture > From File to insert the GIF. If you need to automate it, turn on the macro recorder prior to inserting the picture, then edit the code as necessary.

– Jon

8. Ajith says:

Jon,
Thanks for the reply. But I am using OWC copmponent to create the spread sheet and the chart. I want to combine both. Or I can insert the GIF file created from the chart into the spread sheet at runtime. Do you have any idea to do this.
Thanks
Ajith

9. Hi Rob,

I was pleasantly surprised by all ideas on in-cell charts. This is fun.

You’ll be far beyond this topic (2005) but still …
In that way 0 degrees point top, 90 degrees to right etc. I wonder if I have a point here.

If you are interested: see my site, page Example Files, Excel, DiversenGrafieken.xls. On the last sheet, you’ll find ‘changed’ versions of your great examples.
I also had a fight with the macro code of in-cell line charts. It assigns names to the lines, based on a cell address. And I tried to add more comments (in Dutch). Also funny: I found the WorksheetFunction.Max before reading all comments.

Kind regards, Frans

10. Rob van Gelder says:

Hi Frans.

Indeed you’ve found a mistake. You’ll note this was a comment earlier on.
Nice site. You’ve got a lot of material there!

Cheers

11. I am creating a chart using ChartSpaceClass.
oChartSpace.Charts[0].SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimCategories,Convert.ToInt32(OWC11.ChartSpecialDataSourcesEnum.chDataLiteral),names );
oChartSpace.Charts[0].SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimValues,Convert.ToInt32(OWC11.ChartSpecialDataSourcesEnum.chDataLiteral),totals );
‘names’ and ‘totals’ are twa tab limited strings.
I want this chart to be showed in an excel and save in the server.
I use SpreadsheetClass to generate excel sheet. How can I link this chart to my Spreadsheet, if I am not using the data from the spread sheet to create Chart.

Posting code? Use <pre> tags for VBA and <code> tags for inline.