blogs

blogs

02.132018
0 0
Step by step guide to create charts in excel VBA part – 1

Step by step guide to create charts in excel VBA part – 1

Excel is one of the most powerful tools when it comes to data analysis and representing the analyzed data in the form of charts and graphs. If you are an analyst, you know well how much time is spent in creating a graphical visualization of data and it is time-consuming, right? In this era of automation, there is no scope for manual processing. So here we are with a new blog on automating the process of creating charts and graphs in excel. We will see how we can use VBA to automate this process.

We will also look into the different aspects of the charting engine provided by Microsoft Excel.

Basically, we have two types of the charts which can be manipulated for VBA:

  • Chart Sheet
  • Embedded Chart

Chart Sheet

Chart Sheets are separate sheet tabs in your workbook.

Excel VBA Chart Sheet

Excel VBA Chart

Sub ChartSheetDemo()
Dim chtChart As Chart
Set ChartDemo = Application.Workbooks(“Chart Demo.xlsm”)
Set DataSheet = ChartDemo.Sheets(“Data”)
‘Check if the chart sheet already exist with the same name
IfChartExist chartname:=”Sales”
Set chtChart = Charts.AddWith chtChart
.name = “Sales”
.ChartType = xlColumnClustered
‘Specifying the data source
.SetSourceData Source:=DataSheet.Range(“A4:G9″), _
PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = “Sales Report For the Year 2017″
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = “Month”
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = “Sale Unit”
End WithEnd Sub

Let’s dive into the code and see what’s exactly going on.

The first line states how to start the procedure in VBA. We are creating a procedure as ChartSheetDemo, which will contain our chart creation logic.

Sub ChartSheetDemo()

The below lines are declaration and initialization of variables we will be using.

Dim chtChart As Chart

We have declared a Chart type variable.

Set ChartDemo = Application.Workbooks(“Chart Demo.xlsm”)

The ChatDemo is an object type variable which corresponds to our workbook. ChartDemo variable now contains all the attributes and property of the current workbook and we can use this variable to manipulate workbook properties and attributes. We will also need this variable to access different sheets in this workbook.

Set DataSheet = ChartDemo.Sheets(“Data”)

DataSheet contains the data from which we have generated our chart. It is also of type object. DataSheet has a public access specifier.

Before we add the chart sheet to the workbook, we should check if we already have a chart with the same name otherwise it will throw an error. So, below the line is a validation check. IfChartExist is a parameterized procedure which takes chart sheet name as an argument. The mechanism which is used here to pass the argument is known as a named argument.

IfChartExist chartname:=”Sales”

This line will add the chart to the workbook.

Set chtChart = Charts.Add

Now we will be settings different properties of our chart. You can play around with these properties to check out their functioning.

With chtChart
.name = “Sales”    Sets the tab name of the chart sheet.
.ChartType = xlColumnClustered  Tell the type of chart sheet.
‘Link to the source data range
.SetSourceData Source:=DataSheet.Range(“A4:G9″), _
PlotBy:=xlRows

The above line specifies the data source which will be referred by the chart to generate the bar chart.

.HasTitle = True
.ChartTitle.Text = “Sales Report For the Year 2017″
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = “Month”
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = “Sale Unit”
End With

In the next blog, we will see how to create an embedded chart using VBA. Also, there are lots of new topics coming on Excel VBA Automation so stay tuned.

Thank you, all the readers will see you in the next blog. Till then keep reading.

Ignatiuz is having a strong expertise to provide better solution for Learning Management System (LMS) Solution as per the client requirements. To know more about the providing please visit http://ignatiuz.com/solutions/lms-solutions

If you have any query or want more information on Excel Automation or Excel VBA Coding, contact us today at +1-484-876-1867 or send us a message.


Leave a Reply

Your email address will not be published. Required fields are marked *