Charts and VBA in Excel
We have here the data of four good IT companies, namely, Wipro, TCS, Infosys and HCL. It's actually their balance sheet of the last 5 years. Now let's say we wish to analyze their sales and net profit over the years. We also wish to have charts so that we don't have to browse through many pages of data.
The first thing we did was to extract the relevant data and sort it. Next the data was transferred to another sheet called data. Now from this consolidated data we created charts and placed them in a new sheet called charts. We felt that the whole process could be automated using VBA. But how to get a solution that even a beginner could implement relatively easily. Instead of writing the code we recorded a macro for each step. Then the macros were tweaked a little bit and the complete process is now automated. The charts look like dashboards!
The first macro is recorded as follows:
- Select the years, sales and net profit/loss rows (A3:F4, press CTRL and select A21 to F21)
- Now click on the copy icon on the toolbar
- Next click in Cell J3, click on the arrow next to paste and finally click on transpose
- Now select the years data 2009 to 2005 and click on sort ascending icon
- Now select the transposed and sorted data, click on the cut icon and paste the data on the 'data' worksheet.
Now select the data of 'Wipro' from the data worksheet and create a chart. Do this for all the four data of the four companies one by one.
All the time you are recording your actions in different macros.
Code of Macro1
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/27/2010 by Family Computer Club
' Selecting, copying, paste special, sorting
'
Sheets(Array("Wipro", "TCS", "Infosys", "HclTecnologies")).Select
Sheets("Wipro").Activate
Range("A3:F4,A21:F21").Select
Range("A21").Activate
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("J4:J8").Select
Sheets("Wipro").Select
Application.CutCopyMode = False
Range("J3:L8").Sort Key1:=Range("J4"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("TCS").Select
Range("J3:L8").Sort Key1:=Range("J4"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Infosys").Select
Range("J3:L8").Sort Key1:=Range("J4"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("HclTecnologies").Select
Range("J3:L8").Sort Key1:=Range("J4"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
Code of Macro2
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 4/27/2010 by Family Computer Club
' cutting and pasting
'
Sheets("Wipro").Activate
Range("J3:L8").Select
Selection.Cut
Sheets("Data").Select
Range("B2").Select
ActiveSheet.Paste
Range("B10").Select
Sheets("TCS").Select
Range("J3:L8").Select
Selection.Cut
Sheets("Data").Select
ActiveSheet.Paste
Range("B18").Select
Sheets("Infosys").Select
Range("J3:L8").Select
Selection.Cut
Sheets("Data").Select
ActiveSheet.Paste
Range("B26").Select
Sheets("HclTecnologies").Select
Range("J3:L8").Select
Selection.Cut
Sheets("Data").Select
ActiveSheet.Paste
Sheets("Data").Select
End Sub
VBA code of Macro3
Sub Macro3()
'
' Macro3 Macro
' Macro recorded 4/27/2010 by Family Computer Club
' charts and tweaking of chart size and position
'
Sheets("Data").Activate
Range("B2:D7").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Data").Range("B2:D7"), PlotBy:= _
xlColumns
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).XValues = "=Data!R3C2:R7C2"
ActiveChart.SeriesCollection(2).XValues = "=Data!R3C2:R7C2"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Wipro"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "year"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Rs."
End With
With ActiveChart.Parent
.Left = 50
.Width = 300
.Top = 15
.Height = 180
End With
'ActiveSheet.Shapes("Wipro2").ScaleWidth 0.61, msoFalse, msoScaleFromTopLeft
'ActiveSheet.Shapes("Wipro2").ScaleHeight 0.5, msoFalse, msoScaleFromTopLeft
'ActiveSheet.Shapes("Wipro2").IncrementLeft -185.25
'ActiveSheet.Shapes("Wipro2").IncrementTop -86.25
'ActiveSheet.Shapes("Wipro2").ScaleHeight 1.46, msoFalse, msoScaleFromTopLeft
'ActiveWindow.Visible = False
Windows("charts-and-vba-in-excel.xls").Activate
Range("I2").Select
End Sub
The VBA codes for Macro4, 5 and 6 are almost the same. Now these chart macros don't work. So need to need to deactivate the relevant lines by placing an apostrophe before them. The extra code added does the job of resizing and placing the charts in their proper positions. You can play around with the program to see how it works when you change the title or width of the chart.
Some screen shots are also given here for your ready reference:
Macro2
Macro3