Creating a chart using a macro

This is quite an advanced topic and not something that you will need to do very often. But it is a great thing to have in your arsenal – being able to generate a chart based on user entry is a useful thing to be able to do.

Getting ready

Load the following script:

LOAD * INLINE [
  Country, Sales
  UK, 100000
  USA, 101899
  Japan, 87088
  Germany, 67896
];

How to do it…

These steps show you how to create a chart using a macro:

1. From the Tools menu, select Edit Module….

2. Enter the following code:

   Option Explicit
   
   Sub GenerateBarChart()
   
     Dim myChart
   
     ' Create a new Bar Chart
     Set myChart = _
        ActiveDocument.ActiveSheet().CreateBarChart()
   
     ' Add a dimension of Country to the new chart
     myChart.AddDimension "Country"
     ' Add an expression
     myChart.AddExpression "Sum(Sales) "
   
     ' Get the properties object
     Dim cp
     Set cp = myChart.GetProperties()
   
     ' Set the title of the dimension
     cp.Dimensions(0).Title.v = "Country Name"
   
     ' Set the Title-in-chart text...