capt webb
Capt. Horatio T.P. Webb
Creating Excel Charts with VBScript (aka VBA)
A Time Series Forecasting Example

Parks -- Fall 2013
Version 1 -- Last Updated 9:10 PM 10/26/2013

Microsoft Excel can use "macros" written in vbscript rather than using the built-in Excel functions and graphical user interfaces. This page specifically discusses how to a "macro in vbscript" to automate the sometimes tedious process of creating Excel "Charts".

Data and code for the sample analysis is stored in an Excel spreadsheet here: http://www.bauer.uh.edu/parks/chart.xlsm. This spreadsheet has macros (VBA means "Visual Basic for Applications" i.e., vbscript code) and is stored NOT a a *.xlsx file BUT as a *.xlsm file as it has macros). Save the file in a place you can remember.

To View the VBA code necessary to perform the tasks below, you will need to set up Excel to show the "Developer" tab. To do this follow these instructions: Click here for Excel 2013 OR Click here for Excel 2010.

  1. Start Excel
  2. Click "File", then "Open", then find the "chart.xlsm" file you downloaded above.

    You will see two parts on the spreadsheet.

    1. the data for the charts is shown on the left side of the spreadsheet. This data is NOT the primary focus of our discussion. It is used to make the data for the example charts.

      1. The data represents randomly generated observations for five monthly time series for nine years. These observations are used to create forecast for the five randomly generated series. There are 108 rows (9 years x twelve months per year) and 5 columns of observational data (one for each time series labelled: Y1, Y2, Y3, Y4, Y5 -- in rows 5 thru 112 and columns E thru I). These rows and columns are shaded light gray.
      2. the calculations of "trend" (the best fitting straight line) for each of the five series is shown in rows 5 thru 112 and columns J thru N. These cells are shaded dark gray.
      3. the slopes and interecepts for the regression line calculations are shown in ten lavender-colored boxes below the data
      4. seasonality calculations are shown for each month for each series (blue)
      5. the Trend forcastes for the future 12 months for each of the five series (i.e., the year 2014 in green)
      6. the Trend forcastes for the future 12 month for each of the five series "seasonalized" with the indices from step (4)

    2. The charts (or graphs if your prefer) are shown on the right side of the spreadsheet

      For each of the five series (Y1 thru Y5), an Excel Chart is created that shows:

      1. The original time series data (Y1 thru Y5 for the nine years 2003 thru 2013) in red
      2. The calculated trend values for each series in blue
      3. The forecasted monthly forecated trend values for 2014 in green
      4. The seasonalized monthly forecasted trend values for 2014 in magenta

The VBA code (i.e, vbscript) can be seen in two macros. To see the code, Click the "Developer" tab on the ribbon. Then click "Macros". You will see two code modules.

  1. main

    This main routine is executed by clicking the "Recalculate and Draw the Charts" button on the spreadsheet. The result is a recalculation of all spreadsheet values and the creation of five charts.

    1. The first statement in "main" calls the "make_data" sub in order to create the data on the left-side of the spreadsheet (see the code discussion for "make_data" below)

    "main" then creates 5 Excel charts:

    1. Creates several Arrays to store various titles and data range strings: 

      '
'*** below sets the chart title
'
titles = Array("Y1 Observed, Trend and Forecasts", "Y2 Observed, Trend and Forecasts", "Y3 Observed, Trend and Forecasts", "Y4 Observed, Trend and Forecasts", "Y5 Observed, Trend and Forecasts")
'
'*** x data ranges
'
xvals = "d5:d112"      '*** historical X (Jan 2003 thru Dec 2013)
xvals2 = "d118:d129"   '*** future x  (Jan 2014 thru Dec 2014)
xvals3 = "i133:i144"   '*** future x (Jan 2014 thru Dec 2014)
'
'*** y1 thru y4  set data location ranges
'*** y1names thru y4names sets the name of the series
'
y1 = Array("e5:e112", "f5:f112", "g5:g112", "h5:h112", "i5:i112")
y1name = Array("Observed Y1", "Observed Y2", "Observed Y3", "Observed Y4", "Observed Y5")

y2 = Array("j5:j112", "k5:k112", "l5:l112", "m5:m112", "n5:n112")
y2Name = Array("Historical Trend Y1", "Historical Trend Y2", "Historical Trend Y3", "Historical Trend Y4", "Historical Trend Y5")

y3 = Array("j118:j129", "k118:k129", "l118:l129", "m118:m129", "n118:n129")
y3Name = Array("Trend Forecast Y1", "Trend Forecast Y2", "Trend Forecast Y3", "Trend Forecast Y4", "Trend Forecast Y5")

y4 = Array("j133:j144", "k133:k144", "l133:l144", "m133:m144", "n133:n144")
y4Name = Array("Seasonalized Forecast Y1", "Seasonalized Forecast Y2", "Seasonalized Forecast Y3", "Seasonalized Forecast Y4", "Seasonalized Forecast Y5")

    2. deletes all the previous charts 

      Dim NumCharts As Long
NumCharts = ActiveSheet.ChartObjects.Count
If NumCharts > 0 Then
   For i = NumCharts To 1 Step -1
       ActiveSheet.ChartObjects(i).Delete
    Next i
End If

    3. sets the vertical location of the first chart: 

      curr_Top = 75 '*** location of the top of the first chart

    4. then for each chart (uses the loop: For chart_num = 0 To 4)

      1. set the chart title 

        Set cht = ActiveSheet.ChartObjects.Add(Left:=800, Width:=900, Top:=75, Height:=400)
   cht.Chart.HasTitle = True
   With cht.Chart.ChartTitle
          .Text = titles(chart_num)
End With

      2. for each of the five charts draw 4 components: (1) Original Observations; (2)Trend; (3) Forecasted Trend; (4) Seasonalized Forecasted Trend 

         With cht

      .Chart.ChartType = xlXYScatterLines                                                '*** original data in red
      .Chart.SeriesCollection.NewSeries
      .Chart.SeriesCollection(1).XValues = Worksheets("Sheet1").Range(xvals)        '*** gets the time values (i.e., the month numbers 1 thru 108)
      .Chart.SeriesCollection(1).Values = Worksheets("Sheet1").Range(y1(chart_num)) '*** get the y values
      .Chart.SeriesCollection(1).Name = y1name(chart_num)                           '*** gets the series name from the array
      .Chart.SeriesCollection(1).Format.Line.Weight = 1                             '*** sets line width in pixels
      .Chart.SeriesCollection(1).MarkerBackgroundColor = RGB(255, 255, 255)         '*** set background to white  (i.e., what is inside the circle)
      .Chart.SeriesCollection(1).MarkerForegroundColor = RGB(255, 0, 0)             '*** sets the circle to red
      .Chart.SeriesCollection(1).MarkerSize = 2                                     '*** makes the marker size 2 pixels
      .Chart.SeriesCollection(1).MarkerStyle = xlMarkerStyleCircle                  '*** sets the marker to a circle
      .Chart.SeriesCollection(1).Border.Color = RGB(255, 0, 0)                      '*** sets the line color
	  
      .Chart.SeriesCollection.NewSeries                                                  '*** trend in blue
      .Chart.SeriesCollection(2).XValues = Worksheets("Sheet1").Range(xvals)
      .Chart.SeriesCollection(2).Values = Worksheets("Sheet1").Range(y2(chart_num))
      .Chart.SeriesCollection(2).Name = y2Name(chart_num)
      .Chart.SeriesCollection(2).Format.Line.Weight = 1
      .Chart.SeriesCollection(2).MarkerBackgroundColor = RGB(255, 255, 255)
      .Chart.SeriesCollection(2).MarkerForegroundColor = RGB(0, 0, 255)
      .Chart.SeriesCollection(2).MarkerSize = 2
      .Chart.SeriesCollection(2).MarkerStyle = xlMarkerStyleCircle
      .Chart.SeriesCollection(2).Border.Color = RGB(0, 0, 255)
      
      .Chart.SeriesCollection.NewSeries                                                  '*** forecated trend in green
      .Chart.SeriesCollection(3).XValues = Worksheets("Sheet1").Range(xvals2)
      .Chart.SeriesCollection(3).Values = Worksheets("Sheet1").Range(y3(chart_num))
      .Chart.SeriesCollection(3).Name = y3Name(chart_num)
      .Chart.SeriesCollection(3).Format.Line.Weight = 1
      .Chart.SeriesCollection(3).MarkerBackgroundColor = RGB(255, 255, 255)
      .Chart.SeriesCollection(3).MarkerForegroundColor = RGB(0, 203, 0)
      .Chart.SeriesCollection(3).MarkerSize = 2
      .Chart.SeriesCollection(3).MarkerStyle = xlMarkerStyleCircle
      .Chart.SeriesCollection(3).Border.Color = RGB(0, 203, 0)
      
      .Chart.SeriesCollection.NewSeries                                                  '*** seasonalized forcast in magenta
      .Chart.SeriesCollection(4).XValues = Worksheets("Sheet1").Range(xvals3)
      .Chart.SeriesCollection(4).Values = Worksheets("Sheet1").Range(y4(chart_num))
      .Chart.SeriesCollection(4).Name = y4Name(chart_num)
      .Chart.SeriesCollection(4).Format.Line.Weight = 1
      .Chart.SeriesCollection(4).MarkerBackgroundColor = RGB(255, 255, 255)
      .Chart.SeriesCollection(4).MarkerForegroundColor = RGB(255, 0, 255)
      .Chart.SeriesCollection(4).MarkerSize = 2
      .Chart.SeriesCollection(4).MarkerStyle = xlMarkerStyleCircle
      .Chart.SeriesCollection(4).Border.Color = RGB(255, 0, 255)

      3. Moves the top down for the next chart 

          curr_Top = curr_Top + 430

    5. make_data

      This sub:

      1. defines several Arrays:

        • a 5 element array named m for the slope of each series
        • a 5 element array named b for the intercepts of each series
        • a 12 element arrray for the seasonal indices named si

      2. makes the data for the charts

        • Step 1 -- generates the random Y series data by: 
          lsquare = b(j) + m(j) * Cells(r, 4)          '*** uses the month number in the fourth column of the spreadsheet
		                                          and the slope and intercept to make a random trend line observations
errv = 95# + (100# * Rnd) / 10#              '*** Generate a random error values between .95 and 1.05
yobs = (lsquare * si(mon - 1)) * errv / 100# '*** calculate the observation by adjusting with the error
                   		                          and the seasonal index
Cells(r, 5 + j) = yobs                       '*** stores the observation in the light gray portion of the table

        • Step 2 -- creates a linear times series estimate for the random observations using the built-in Excel SLOPE and INTERCEPT functions and the observation data: 
          Cells(114, 5 + j) = Application.WorksheetFunction.Slope    (Range(Cells(5, 5 + j), Cells(112, 5 + j)), Range(Cells(5, 4), Cells(112, 4)))
Cells(115, 5 + j) = Application.WorksheetFunction.Intercept(Range(Cells(5, 5 + j), Cells(112, 5 + j)), Range(Cells(5, 4), Cells(112, 4)))

        • Step 3 -- Makes the trend calculations for each observation for the dark gray areas: 
          Cells(r, 10 + j) = Cells(115, 5 + j) + Cells(r, 4) * Cells(114, 5 + j)
        • Step 4 -- calculates the seasonal indices for each series summing the observed/trend values for each month and then finding the average.
        • Step 5 -- using the month values for 2014, calculate the forecasted trend
        • Step 6 -- seasonalize the forecated trend using the seasonal indices in step 4

      3. performs a recalculation of ALL the worksheets in the spreadsheet like this: 

        Dim Sht As Worksheet
Application.Calculation = xlCalculationManual
For Each Sht In Worksheets
   Sht.EnableCalculation = False
   Sht.EnableCalculation = True
Next Sht
Application.Calculate

      4. removes ALL the existing charts and buttons on the spreadsheet 

        Dim Shp As Shape
For Each Shp In ActiveSheet.Shapes
   Shp.Delete
Next

      5. puts the button back on the spreadsheet 

         Set btn = ActiveSheet.Buttons.Add(700, 30, 160, 20)
 btn.OnAction = "main"
 btn.Caption = "Recalculate and Draw the Charts"

      NOTE: this step is not really necessary but was done to show how to add a button and connect it to a macro

      Ther are other Chart properties:

      For a Chart on a worksheet:

      Hierarchy:
        Application
          Workbook
            Worksheet
              Chart Object
                Chart
                  property (e.g.,Chart Tile)

      Then you use either:

      Worksheets("Sheet1").ChartObjects(1).Chart.property '***chart number

      OR

      Worksheets("Sheet1").ChartObjects(chart name1).Chart.property '*** chart name

      For A Chart on a separate chart sheet:
      Hieracrchy:
        Application
          Workbook
            Chart
              property (e.g., ChartTile.Text)

      A Chart Sheet is a Chart Object, so...

      Sheets("chart name).property

      To build a Chart:

      Set name of the worksheet in this code= ActiveWorkbook.Worksheets("Sheet1")
      name of the worksheet in this code.ChartArea.Left = pixels
      name of the worksheet in this code.ChartArea.Top = pixels
      name of the worksheet in this code.ChartArea.Height = pixels
      name of the worksheet in this code.ChartArea.Width = pixels

      then With Worksheets("sheet1").ChartObjects(1).Chart
          .HasTitle = True
          .ChartTitle.Text = "February Sales"
          .ChartTitle.Height =
          .ChartTile.HorizontalAlignment=
          .Font.Size = 12
          .ChartType = see values belowbelow
          .HasMajorGridlines = True | False
      End With

      Chart types

      Usage:

      Worksheets(1).ChartObjects(1).Activate
      ActiveChart.ChartType = Chart Name OR Value from below
      Chart Type NameValueDecsription
      xl3DArea -4098 3D Area.
      xl3DAreaStacked 78 3D Stacked Area.
      xl3DAreaStacked100 79 100% Stacked Area.
      xl3DBarClustered 60 3D Clustered Bar.
      xl3DBarStacked 61 3D Stacked Bar.
      xl3DBarStacked100 62 3D 100% Stacked Bar.
      xl3DColumn -4100 3D Column.
      xl3DColumnClustered 54 3D Clustered Column.
      xl3DColumnStacked 55 3D Stacked Column.
      xl3DColumnStacked100 56 3D 100% Stacked Column.
      xl3DLine -4101 3D Line.
      xl3DPie -41023D Pie.
      xl3DPieExploded 70 Exploded 3D Pie.
      xlArea 1Area
      xlAreaStacked 76 Stacked Area.
      xlAreaStacked100 77100% Stacked Area.
      xlBarClustered 57 Clustered Bar.
      xlBarOfPie 71 Bar of Pie.
      xlBarStacked 58 Stacked Bar.
      xlBarStacked100 59 100% Stacked Bar.
      xlBubble 15 Bubble.
      xlBubble3DEffect 87 Bubble with 3D effects.
      xlColumnClustered 51 Clustered Column.
      xlColumnStacked 52 Stacked Column.
      xlColumnStacked 100 53100% Stacked Column.
      xlConeBarClustered 102 Clustered Cone Bar.
      xlConeBarStacked 103 Stacked Cone Bar.
      xlConeBarStacked100 104 100% Stacked Cone Bar.
      xlConeCol 105 3D Cone Column.
      xlConeColClustered 99 Clustered Cone Column.
      xlConeColStacked 100 Stacked Cone Column.
      xlConeColStacked100 101 100% Stacked Cone Column.
      xlCylinderBarClustered 95 Clustered Cylinder Bar.
      xlCylinderBarStacked 96 Stacked Cylinder Bar.
      xlCylinderBarStacked100 97 100% Stacked Cylinder Bar.
      xlCylinderCol 98 3D Cylinder Column.
      xlCylinderColClustered 92 Clustered Cone Column.
      xlCylinderColStacked 93 Stacked Cone Column.
      xlCylinderColStacked100 94 100% Stacked Cylinder Column.
      xlDoughnut -4120 Doughnut.
      xlDoughnutExploded 80 Exploded Doughnut.
      xlLine 4 Line.
      xlXYScatterLines 65 Line with Markers.
      xlLineMarkersStacked 66 Stacked Line with Markers.
      xlLineMarkersStacked 100 67 100% Stacked Line with Markers.
      xlLineStacked 63 Stacked Line.
      xlLineStacked 100 64 100% Stacked Line.
      xlPie 5 Pie.
      xlPieExploded 69 Exploded Pie.
      xlPieOfPie 68 Pie of Pie.
      xlPyramidBarClustered 109 Clustered Pyramid Bar.
      xlPyramidBarStacked 110 Stacked Pyramid Bar.
      xlPyramidBarStacked100 111 100% Stacked Pyramid Bar.
      xlPyramidCol 112 3D Pyramid Column.
      xlPyramidColClustered 106 Clustered Pyramid Column.
      xlPyramidColStacked 107 Stacked Pyramid Column.
      xlPyramidColStacked100 108 100% Stacked Pyramid Column.
      xlRadar -4151 Radar.
      xlRadarFilled 82 Filled Radar.
      xlRadarMarkers 81 Radar with Data Markers.
      xlStockHLC 88 High-Low-Close.
      xlStockOHLC 89 Open-High-Low-Close.
      xlStockVHLC 90 Volume-High-Low-Close.
      xlStockVOHLC 91 Volume-Open-High-Low-Close.
      xlSurface 83 3D Surface.
      xlSurfaceTopView 85 Surface (Top View).
      xlSurfaceTopViewWireframe 86 Surface (Top View wireframe).
      xlSurfaceWireframe 84 3D Surface (wireframe).
      xlXYScatter -4169 Scatter.
      xlXYScatterLines 74 Scatter with Lines.
      xlXYScatterLinesNoMarkers 75 Scatter with Lines and No Data Markers.
      xlXYScatterSmooth 72 Scatter with Smoothed Lines.
      xlXYScatterSmoothNoMarkers 73 Scatter with Smoothed Lines and No Data Markers.