Home
Search
 
What's New
Index
Books
Links
Q & A
Newsletter
Banners
 
Feedback
Tip Jar
 
C# Helper...
 
XML RSS Feed
Follow VBHelper on Twitter
 
 
 
MSDN Visual Basic Community
 
 
 
 
 
TitleUse code to make a chart in Excel from the current selection in VBA
DescriptionThis example shows how to use code to make a chart in Excel from the current selection in VBA.
KeywordsExcel, Office, chart, VBA, graph, Visual Basic for Applications
CategoriesGraphics, Office
 
The MakeGraph subroutine takes as a parameter a Range to use to build the chart. You can pass it the Selection object to use the currently selected values.

The subroutine creates a new worksheet and adds a chart to it. It adds any labels that is was passed as parameters and then sizes the chart.

 
Public Sub MakeGraph(value_range As Range, Optional ByVal _
    title As String = "", Optional ByVal x_label As String _
    = "", Optional ByVal y_label As String = "")
Dim work_book As Workbook
Dim last_sheet As Worksheet
Dim new_sheet As Worksheet
Dim r As Integer
Dim new_chart As Chart
Dim chart_shape As Shape

    ' Make a new worksheet.
    Set work_book = Application.ActiveWorkbook
    Set last_sheet = _
        work_book.Sheets(work_book.Sheets.Count)
    Set new_sheet = work_book.Sheets.Add(after:=last_sheet)
    new_sheet.Name = "New Chart"

    ' Make the chart.
    Set new_chart = Charts.Add()
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData _
        Source:=value_range, _
        PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, _
        Name:="New Chart"

    ' Set the chart's title abd axis labels.
    With ActiveChart
        If Len(title) = 0 Then
            .HasTitle = False
        Else
            .HasTitle = True
            .ChartTitle.Characters.Text = title
        End If

        If Len(x_label) = 0 Then
            .Axes(xlCategory, xlPrimary).HasTitle = False
        Else
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, _
                xlPrimary).AxisTitle.Characters.Text = _
                x_label
        End If

        If Len(y_label) = 0 Then
            .Axes(xlValue, xlPrimary).HasTitle = False
        Else
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, _
                xlPrimary).AxisTitle.Characters.Text = _
                y_label
        End If
    End With

    ' Make it bigger.
    Set chart_shape = _
        new_sheet.Shapes(new_sheet.Shapes.Count)
    With chart_shape
        .ScaleWidth 1.9, msoFalse, msoScaleFromBottomRight
        .ScaleHeight 1.9, msoFalse, msoScaleFromBottomRight
        .Left = 20
        .Top = 20
    End With
End Sub
 
 
Copyright © 1997-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
  Updated