Create Business Intelligence Excel Charts Using VBA And Upload To SharePoint

We can derive business intelligence out of the Excel data by making use of its charting capability. In order to work with charting options, Excel provides the chart object. The chart can either be an embedded chart (contained in a ChartObject object) or a separate chart sheet.

We have a lot of options to create business intelligence charts within SharePoint like SSRS, PerformancePoint, PowerPivot, Power BI, Client Side Rendering and so on. In this article, we will see how to get started with the charting capability of Excel using VBA, upload it and view it from SharePoint.

Prerequisites

We have to make sure that the developer tab is available in the Excel sheet in order to get started with development using VBA. In order to do tha,t right click anywhere on the Tool bar and select "Customize the Ribbon" option.


From the list box, select Developer option and click on OK.


This will activate the Developer tab in the Ribbon menu.


Click on "View Code" to open up the Visual Basic Code window.


Make charts with sample data

Before making use of the Excel data, we will see how to create a simple chart in Excel by making use of hard coded data to create a business intelligence chart out of it. We will be using the "charts.Add" method to add a new chart to the Excel sheet. We will then use ‘ActiveChart.SetSourceData Sheets("Sheet1").Range’ to set the data range and will populate the series data using ‘ ActiveChart.SeriesCollection(1).Formula’ .

  1. Sub ConvertDataToChart()  
  2. 'Add a new chart  
  3. Add  
  4. 'Set a data range for the chart which will be populated later  
  5. SetSourceData Sheets("Sheet1").Range("a1:d4")  
  6. 'Set the chart type  
  7. ChartType = xlBarClustered  
  8. 'Set arbitrary values for the data series  
  9. SeriesCollection(1).Formula = _  
  10. "=SERIES(""Maruthi Baleno"",{""Q1"",""Q2"",""Q3"",""Q4""},{2000,3000,4000,5000},1)"  
  11. SeriesCollection(2).Formula = _  
  12. "=SERIES(""Ford Figo"",{""Q1"",""Q2"",""Q3"",""Q4""},{1500,2200,3400,4500},2)"  
  13. SeriesCollection(3).Formula = _  
  14. "=SERIES(""Renault Duster"",{""Q1"",""Q2"",""Q3"",""Q4""},{100,1100,3120,7300},3)"  
  15. End Sub  

On running the macro, we will get the below chart output in the sheet.



We can change the type of the chart we want to display by modifying the ChartType property, as shown below.

  1. ChartType = xlCylinderColStacked  

 

Create Chart from Excel Data

Now, we will see how to create charts from the data present in the Excel sheet. Say for instance, we have the below set of data that indicates the mark list for a set of students and we want to create chart from this data.


We can use fairly simple VBA macro to get this done. We can add a chart to the sheet using ‘Chart. Add’ and set the properties for the ‘ActiveChart’ object. We will specify the range of Excel cells upon which the chart should be created by setting the value for ‘ActiveChart.SetSourceData Source’ . ‘ ActiveChart.ChartType’ will set the type of chart we want to use.

  1. Sub MakeColumnChart()  
  2. 'Add a new chart object  
  3. Add  
  4. 'Set the Chart Properties  
  5. ChartType = xl3DColumn  
  6. SetSourceData Source:=Sheets("Sheet1").Range("A1:E7")  
  7. Location Where:=xlLocationAsObject, Name:="Sheet1"  
  8. End Sub  

We can now run the script and see how the chart looks like by clicking on the "Run" button.


Select the macro name and Click on Run.


This will generate the chart for us in the Excel sheet next to the data.


Create Pie Charts

Using the same technique, we can create similar Pie Charts to derive business intelligence. We will make use of the below Excel data to demo the pie chart experience.


The code is similar to the Column chart that we created above. The only change is the ‘ChartType’ property. We will be setting it to ‘xlPie’ this time.

  1. Sub MakePieChart()  
  2. 'Add a new chart object  
  3. Add  
  4. 'Set the Chart Properties  
  5. ChartType = xlPie  
  6. SetSourceData Source:=Sheets("Sheet2").Range("A4:B7")  
  7. Location Where:=xlLocationAsObject, Name:="Sheet2"  
  8. End Sub  

Let’s go ahead and select the macro and run it to see the output chart.


Thus, we can see the pie chart representation for the Excel data.


Build Line Chart and Cylinder Charts

By changing the ChartType to ‘xlLine’, we can convert the previous Pie Chart to a Line Chart as shown below.

  1. ChartType = xlLine  

 


Similarly, changing the ‘ChartType’ to ‘xlCylinderCol’ will fetch us the below Cylinder chart.

  1. ChartType = xlCylinderCol  

From the design tab, we can modify these charts and give a better look and feel to it.


Upload to SharePoint

So far, we saw how to create the charts from the Excel data. Now, we will upload the Excel to SharePoint so that it can be used for better collaboration among business users. Before uploading, let's save it. Do ensure that you save it in the xlsm format as specified by the warning message below so as to preserve the macros.


Now, head over to the SharePoint document library and upload the file.


As we can see the Excel file has been uploaded successfully. On clicking the Excel document, it will be rendered using Excel Online.


Thus, the pie chart has come up next to the Excel data just like in the desktop Excel application.


Available Chart Types

We can make use of the entire set of Charts provided by the Microsoft Excel team to derive and showcase business intelligence reports with the slightest customization. Refer the below table to see the complete list of available charts. We just have to change the ‘ChartType’ property to the required Name.

  1. ChartType = <Specify ChartName Here>  
NameValueDescription
xl3DArea-40983D Area.
xl3DAreaStacked783D Stacked Area.
xl3DAreaStacked10079100% Stacked Area.
xl3DBarClustered603D Clustered Bar.
xl3DBarStacked613D Stacked Bar.
xl3DBarStacked100623D 100% Stacked Bar.
xl3DColumn-41003D Column.
xl3DColumnClustered543D Clustered Column.
xl3DColumnStacked553D Stacked Column.
xl3DColumnStacked100563D 100% Stacked Column.
xl3DLine-41013D Line.
xl3DPie-41023D Pie.
xl3DPieExploded70Exploded 3D Pie.
xlArea1Area
xlAreaStacked76Stacked Area.
xlAreaStacked10077100% Stacked Area.
xlBarClustered57Clustered Bar.
xlBarOfPie71Bar of Pie.
xlBarStacked58Stacked Bar.
xlBarStacked10059100% Stacked Bar.
xlBubble15Bubble.
xlBubble3DEffect87Bubble with 3D effects.
xlColumnClustered51Clustered Column.
xlColumnStacked52Stacked Column.
xlColumnStacked10053100% Stacked Column.
xlConeBarClustered102Clustered Cone Bar.
xlConeBarStacked103Stacked Cone Bar.
xlConeBarStacked100104100% Stacked Cone Bar.
xlConeCol1053D Cone Column.
xlConeColClustered99Clustered Cone Column.
xlConeColStacked100Stacked Cone Column.
xlConeColStacked100101100% Stacked Cone Column.
xlCylinderBarClustered95Clustered Cylinder Bar.
xlCylinderBarStacked96Stacked Cylinder Bar.
xlCylinderBarStacked10097100% Stacked Cylinder Bar.
xlCylinderCol983D Cylinder Column.
xlCylinderColClustered92Clustered Cone Column.
xlCylinderColStacked93Stacked Cone Column.
xlCylinderColStacked10094100% Stacked Cylinder Column.
xlDoughnut-4120Doughnut.
xlDoughnutExploded80Exploded Doughnut.
xlLine4Line.
xlLineMarkers65Line with Markers.
xlLineMarkersStacked66Stacked Line with Markers.
xlLineMarkersStacked10067100% Stacked Line with Markers.
xlLineStacked63Stacked Line.
xlLineStacked10064100% Stacked Line.
xlPie5Pie.
xlPieExploded69Exploded Pie.
xlPieOfPie68Pie of Pie.
xlPyramidBarClustered109Clustered Pyramid Bar.
xlPyramidBarStacked110Stacked Pyramid Bar.
xlPyramidBarStacked100111100% Stacked Pyramid Bar.
xlPyramidCol1123D Pyramid Column.
xlPyramidColClustered106Clustered Pyramid Column.
xlPyramidColStacked107Stacked Pyramid Column.
xlPyramidColStacked100108100% Stacked Pyramid Column.
xlRadar-4151Radar.
xlRadarFilled82Filled Radar.
xlRadarMarkers81Radar with Data Markers.
xlStockHLC88High-Low-Close.
xlStockOHLC89Open-High-Low-Close.
xlStockVHLC90Volume-High-Low-Close.
xlStockVOHLC91Volume-Open-High-Low-Close.
xlSurface833D Surface.
xlSurfaceTopView85Surface (Top View).
xlSurfaceTopViewWireframe86Surface (Top View wireframe).
xlSurfaceWireframe843D Surface (wireframe).
xlXYScatter-4169Scatter.
xlXYScatterLines74Scatter with Lines.
xlXYScatterLinesNoMarkers75Scatter with Lines and No Data Markers.
xlXYScatterSmooth72Scatter with Smoothed Lines.
xlXYScatterSmoothNoMarkers73Scatter with Smoothed Lines and No Data Markers.

Source: TechNet

Summary

Thus, we saw how we can create Business Intelligence Charts from Excel Data and upload it to SharePoint.