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.