Microsoft Office Tutorials and References
In Depth Information
Executing the recorded macro
Executing the recorded macro
You might expect that executing this recorded macro would create an exact copy of
the original chart. In fact, it will create the chart, but you’ll get an error message
when the code attempts to move and resize the chart. The recorded macro accesses
the ChartObject as a shape — and it uses its actual name (Chart 1). When you
execute the macro, the chart object that’s created will not be named Chart 1, and the
macro will end with an error.
Macros that create charts can be useful, but (usually) executing a recorded
macro isn’t good enough. For example, you might prefer the chart to use the data
that’s currently selected. The recorded macro “hard codes” the original data range
address.
Writing a macro to create a chart
The goal of this exercise is to write a general-purpose macro that creates a column
chart from the selected data. Furthermore, this macro will delete the legend, add a
title, size the chart to be 300 x 200, and position the chart at the lower-right corner
of the selected range.
The macro that meets these requirements (named CreateChart ) follows.
Sub CreateChart()
Dim DataRange As Range
Dim ChtObj As ChartObject
Dim ChtTop As Long, ChtLeft As Long
If TypeName(Selection) <> “Range” Then Exit Sub
Set DataRange = Selection
ChtTop = DataRange.Top + DataRange.Height
ChtLeft = DataRange.Left + DataRange.Width
Set ChtObj = ActiveSheet.ChartObjects.Add(ChtLeft, ChtTop, 300, 200)
With ChtObj.Chart
.ChartType = xlColumnClustered
.SetSourceData Source:=DataRange, PlotBy:=xlColumns
.HasLegend = False
.HasTitle = True
.ChartTitle.Text = “Chart Title”
.Parent.Select
End With
End Sub
The macro starts by declaring five variables that will be used. It then checks to
ensure that a range is selected. If not, the macro ends. The DataRange variable is set
to represent the selected range. Two other variables ( ChtTop and ChtLeft ) store the
top and left coordinates of the new chart. These variables are calculated using the
metrics of the selected range.
Search JabSto ::




Custom Search