Thursday, August 7, 2014

Chart and VBA

Excel’s Charts
Excel’s charting feature lets you create a wide variety of charts using data that’s stored in a worksheet. You have a great deal of control over nearly every aspect of each chart. An Excel chart is simply packed with objects, each of which has its own properties and methods. Because of this, manipulating charts with Visual Basic for Applications (VBA) can be a bit of a challenge. We discuss the key concepts that you need to understand in order to write VBA code that generates or manipulates charts.
The Chart object model
When you first start exploring the object model for a Chart object, you’ll probably be very confused — which isn’t surprising; the object model is very confusing. It’s also very deep. For example, assume that you want to change the title displayed in an embedded chart. The toplevel object, of course, is the Application object (Excel). The Application object contains a Workbook object, and the Workbook object contains a Worksheet object. The Worksheet object contains a ChartObject object, which contains a Chart object. The Chart object has a ChartTitle object, and the ChartTitle object has a Text property that stores the text that’s displayed as the chart’s title. Here’s another way to look at this hierarchy for an embedded chart:
Application
Workbook
Worksheet
ChartObject
Chart
ChartTitle
Your VBA code must, of course, follow this object model precisely. For example, to set a chart’s title to Annual Sales, you can write a VBA instruction like this:
WorkSheets(“Sheet1”).ChartObjects(1).Chart.ChartTitle. Text = “YTD Sales”
Note that the preceding statement will fail if the chart doesn’t have a title. To add a default title to the chart (which displays the text Chart Title), use this statement:
Worksheets(“Sheet1”).ChartObjects(1).Chart.HasTitle = True
Creating an Embedded Chart
A ChartObject is a special type of Shape object. Therefore, it’s a member of the Shapes collection. To create a new chart, use the AddChart method of the Shapes collection. The following statement creates an empty embedded chart:
ActiveSheet.Shapes.AddChart
The AddChart method can use five arguments (all are optional):
Type: The type of chart. If omitted, the default chart type is used. Constants for all the chart types are provided (for example, xlArea, xlColumnClustered, and so on).
 Left: The left position of the chart, in points. If omitted, Excel centers the chart horizontally.
Top: The top position of the chart, in points. If omitted, Excel centers the chart vertically.
Width: The width of the chart, in points. If omitted, Excel uses 354.
Height: The height of the chart, in points. If omitted, Excel uses 210.
A chart without data isn’t very useful, so you’ll want to use the SetSourceData method to add data to a newly created chart. The procedure that follows demonstrates the SetSourceData method. This procedure creates the chart shown in below Figure.
Data for Chart

Macro Code for Chart
Sub CreateChart()
Dim MyChart As Chart
Dim DataRange As Range
Set DataRange = ActiveSheet.Range(“A1:C13”)
Set MyChart = ActiveSheet.Shapes.AddChart.Chart
MyChart.SetSourceData Source:=DataRange
End Sub
OutPut

Creating a Chart on a Chart Sheet
To create a chart on a chart sheet, use the Add method of the Charts collection. The Add method of the Charts collection uses several optional arguments, but these arguments specify the position of the chart sheet — not chart-related information.
The example that follows creates a chart on a chart sheet and specifies the data range and chart type:
Sub CreateChartSheet()
Dim MyChart As Chart
Dim DataRange As Range
Set DataRange = ActiveSheet.Range(“A1:C13”)
Set MyChart = Charts.Add
MyChart.SetSourceData Source:=DataRange
ActiveChart.ChartType = xlColumnClustered
End Sub
Exporting a Chart or Graphics
In some cases, you may need an Excel chart in the form of a graphics file. For example, you may want to post the chart on a Web site. One option is to use a screen-capture program and copy the pixels directly from the screen. Another choice is to write a simple VBA macro as given below.
Sub SaveChartAsGIF ()
Dim Fname as String
If ActiveChart Is Nothing Then Exit Sub
Fname = ThisWorkbook.Path & “\” & ActiveChart.Name & “.gif”
ActiveChart.Export FileName:=Fname, FilterName:=”GIF”
End Sub
Other choices for the FilterName argument are “JPEG” and “PNG”.
Another way to export all graphic images from a workbook is to save the file in HTML format. Doing so creates a directory that contains GIF and PNG images of the charts, shapes, clipart, and even copied range images (created with HomeàClipboardàPasteàPicture (U)).
Here’s a VBA procedure that automates the process. It works with the active workbook:
Sub SaveAllGraphics()
Dim FileName As String
Dim TempName As String
Dim DirName As String
Dim gFile As String
FileName = ActiveWorkbook.FullName
TempName = ActiveWorkbook.Path & "\" & _
ActiveWorkbook.Name & "graphics.htm"
DirName = Left(TempName, Len(TempName) - 4) & "_files"
' Save active workbookbook as HTML, then reopen original
ActiveWorkbook.Save
ActiveWorkbook.SaveAs FileName:=TempName, FileFormat:=xlHtml
Application.DisplayAlerts = False
ActiveWorkbook.Close
Workbooks.Open FileName
' Delete the HTML file
Kill TempName
' Delete all but *.PNG files in the HTML folder
gFile = Dir(DirName & "\*.*")
Do While gFile <> ""
If Right(gFile, 3) <> "png" Then Kill DirName & "\" & gFile
gFile = Dir
Loop
' Show the exported graphics
Shell "explorer.exe" & DirName, vbNormalFocus
End Sub
Changing chart data based on the active cell
Figure below shows a chart that’s based on the data in the row of the active cell. When the user moves the cell pointer, the chart is updated automatically.


This example uses an event handler for the Sheet1 object. The SelectionChange event occurs whenever the user changes the selection by moving the cell pointer. The event-handler procedure for this event (which is located in the code module for the Sheet1 object) is as follows:
Private Sub Worksheet_SelectionChange(ByVal Target  As Excel.Range)
If CheckBox1 Then Call UpdateChart
End Sub
In other words, every time the user moves the cell cursor, the Worksheet_SelectionChange procedure is executed. If the Auto Update Chart check box (an ActiveX control on the sheet) is checked, this procedure calls the UpdateChart procedure, which follows:
Sub UpdateChart()
Dim ChtObj As ChartObject
Dim UserRow As Long
Set ChtObj = ActiveSheet.ChartObjects(1)
UserRow = ActiveCell.Row
If UserRow < 4 Or IsEmpty(Cells(UserRow, 1)) Then
ChtObj.Visible = False
Else
ChtObj.Chart.SeriesCollection(1).Values = _
Range(Cells(UserRow, 2), Cells(UserRow, 5))
ChtObj.Chart.ChartTitle.Text = Cells(UserRow, 1).Text
ChtObj.Visible = True
End If
End Sub
The UserRow variable contains the row number of the active cell. The If statement checks that the active cell is in a row that contains data. (The data starts in row 4.) If the cell cursor is in a row that doesn’t have data, the ChartObject object is hidden. Otherwise, the code sets the Values property for the Series object to the range in columns 2–5 of the active row. It also sets the ChartTitle object to correspond to the text in column A.

Animating Charts: How to Animate Chart we will show you in next post

Excel ShortCuts

Heading Short Cut Key Discription The Fundamentals Ctrl + O Open File The Fundamentals Ctrl + N ...