Listing 1: The XLCharts Subroutine in ExcelerateYourVBScripts.hta

Sub XLCharts

Const xlColumnClustered = 51
Const xlColumns = 2
Const xlLocationAsObject = 2
Const xlCategory = 1
Const xlPrimary = 1
Const xlValue = 2
Const xlRows = 1
Const xlLocationAsNewSheet = 1
Const xlRight = -4152
Const xlBuiltIn =21

' ******* BEGIN CALLOUT A *******

Set XL = CreateObject("Excel.Application")
XL.Workbooks.Add
XL.Sheets.Add.name = "Bar Chart"
XL.Visible = TRUE
XL.ActiveWorkbook.Sheets("Bar Chart").Tab.ColorIndex = 3

XL.Cells(1, 2).Value = "Jan"
XL.Cells(1, 3).Value = "Feb"
XL.Cells(1, 4).Value = "Mar"
XL.Cells(2, 1).Value = "John"
XL.Cells(3, 1).Value = "Mae"
XL.Cells(4, 1).Value = "Al"
XL.Cells(2, 2).Value = 100
XL.Cells(2, 3).Value = 200
XL.Cells(2, 4).Value = 300
XL.Cells(3, 2).Value = 400
XL.Cells(3, 3).Value = 500
XL.Cells(3, 4).Value = 600
XL.Cells(4, 2).Value = 900
XL.Cells(4, 3).Value = 800
XL.Cells(4, 4).Value = 700

' ******* END CALLOUT A *******

XL.Charts.Add
XL.ActiveChart.ChartType = xlColumnClustered
XL.ActiveChart.SetSourceData xl.Sheets("Bar Chart").Range("A1:D4"), xlColumns
XL.ActiveChart.Location xlLocationAsObject, "Bar Chart"

With xl.ActiveChart
 .HasTitle = False
 .Axes(xlCategory, xlPrimary).HasTitle = False
 .Axes(xlValue, xlPrimary).HasTitle = False
End With


' ******* BEGIN CALLOUT B *******

XL.Charts.Add
XL.ActiveChart.ApplyCustomType xlBuiltIn, "Columns with Depth"
XL.ActiveChart.SetSourceData XL.Sheets("Bar Chart").Range("A1:D4"), xlRows
XL.ActiveChart.Location xlLocationAsNewSheet, "Columns with Depth"

XL.ActiveChart.HasLegend = True
XL.ActiveChart.Legend.Select
XL.Selection.Position = xlRight
XL.Application.CommandBars("Chart").Visible = False

With XL.ActiveChart
 .HasTitle = True
 .ChartTitle.Characters.Text = "Sales"
 .Axes(xlCategory).HasTitle = True
 .Axes(xlCategory).AxisTitle.Characters.Text = "1st Quarter"
 .Axes(xlValue).HasTitle = True
 .Axes(xlValue).AxisTitle.Characters.Text = "Dollars"
End With
XL.ActiveChart.Axes(xlValue).Select
XL.ActiveWorkbook.Sheets("Columns with Depth").Tab.ColorIndex = 5

' ******* END CALLOUT B *******

End Sub